Comparing now()

[Derek]

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?

 

[Dave Peterson]

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]

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
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:

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?

 

[Dave Peterson]

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,

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
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:

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?

 

[Derek]

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.

Did you try it?

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
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:

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?

 

[joeu2004]

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.

 

[Derek]

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

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.

 

[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,

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.

Did you try it?

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
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:

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?

 

[joeu2004]

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.

 

[Derek]

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.

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,

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.

Did you try it?

Derek wrote:

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
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:

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?