 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / January 2008Comparing now()
Hi, I have a price quote with a timestamp which is returned from the server. The timestamp is in text with format 01/23/2008 16:01:00. Can anybody help me to compare the timestamp with now() so that the closing price can be identified? If your windows shortdate is in mdy order, then subtract the smaller from the larger (excel will coerce the text time/date to a number) and format the cell the way you like: Maybe [hh]:mm:ss or d hh:mm:ss > Derek wrote: > [quoted text clipped - 5 lines] > Can anybody help me to compare the timestamp with now() so that the closing > price can be identified?  SignatureDave Peterson Hi Dave, Thank you for your input. However, the timestamp "01/23/2008 16:01:00" is a text which cannot be converted to a number for subtraction. I tried Datevalue() but it does not work. Any Excel function can do that? Derek > If your windows shortdate is in mdy order, then subtract the smaller from > the [quoted text clipped - 18 lines] >> closing >> price can be identified? I didn't say you should convert it to text, I said that if your short date format was mdy, then excel would do it when it did the subtraction. Did you try it? > Hi Dave, > [quoted text clipped - 32 lines] > > > > Dave Peterson SignatureDave Peterson Hi Dave, I formated the cell as mdy format, then did the subtraction and end up #VALUE!. When I looked into the step in calculation, Excel showed that it was the text string "01/23/2008 16:01:00" which caused #VALUE!. i.e. now()- "01/23/2008 16:01:00" Any function to convert the string? >I didn't say you should convert it to text, I said that if your short date > format was mdy, then excel would do it when it did the subtraction. [quoted text clipped - 41 lines] >> > >> > Dave Peterson It's not the cell's format that makes a difference. It's a windows regional setting. If your windows short date--nothing inside excel--is in mdy format, it'll work. If you select an empty cell and hit ctrl-; (control semicolon), what do you see? What order is the date in the formulabar? You can parse that value into its pieces and convert it to a date: =DATE(MID(A1,7,4),LEFT(A1,2),MID(A1,4,2))--RIGHT(A1,8) You could put that in a helper cell or just use it as part of your formula. =DATE(MID(A1,7,4),LEFT(A1,2),MID(A1,4,2))--RIGHT(A1,8) - now() and give it a nice format (where a1 contains your text string) > Hi Dave, > [quoted text clipped - 55 lines] > > > > Dave Peterson SignatureDave Peterson Hi Dave, The format shows "2008-1-24" when I hit ctrl-; . Your function works well except that now() should be put in front of Date() for subtraction. Many thanks ! Derek > It's not the cell's format that makes a difference. > [quoted text clipped - 77 lines] >> > >> > Dave Peterson > However, the timestamp "01/23/2008 16:01:00" is a text which > cannot be converted to a number for subtraction. I tried Datevalue() > but it does not work. Any Excel function can do that? Well you could do the following: =datevalue(left(A1,find(" ",A1))) + timevalue(right(A1,len(A1)-find(" ",A1))) Not sure that is the best way to do the conversion. Also not sure that converting the text timestamp to a serial number and subtracting from NOW() is the best way accomplish whatever you want to do. Be aware that NOW() seems to have an accuracy of about 0.01 seconds, plus or minus binary representation error. Hi Joe, THank you for you advice. The Datevalue function does not convert the text "1/23/2008" yet it does convert "2008/1/23", this makes me nuts as the timestamp string cannot be changed. Should I write some codes but I don't know VB On Jan 23, 9:43 am, "Derek" <derekch...@sina.com> wrote: > However, the timestamp "01/23/2008 16:01:00" is a text which > cannot be converted to a number for subtraction. I tried Datevalue() > but it does not work. Any Excel function can do that? Well you could do the following: =datevalue(left(A1,find(" ",A1))) + timevalue(right(A1,len(A1)-find(" ",A1))) Not sure that is the best way to do the conversion. Also not sure that converting the text timestamp to a serial number and subtracting from NOW() is the best way accomplish whatever you want to do. Be aware that NOW() seems to have an accuracy of about 0.01 seconds, plus or minus binary representation error. > "joeu2004" <joeu2...@hotmail.com> wrote in message > > Well you could do the following: > > =datevalue(left(A1,find(" ",A1))) + timevalue(right(A1,len(A1)-find(" ",A1))) > > The Datevalue function does not convert the text "1/23/2008" yet it does > convert "2008/1/23" [...]. Should I write some codes but I don't know VB No need for UDF. What version of Excel are you using? In Office Excel 2003, DATEVALUE() works with both forms of dates. Anyway, looks like Dave finally offered a workable solution -- exactly the one I would offer if the datevalue/timevalue did not work. Dave's is simpler, relying on the exact format of the timestamp. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.