Date/Time

[Steve H]

Hello.
I am working with a file myfile 2010-09.xlsx. I am deriving the date
using
=DATE(MID(CELL("filename",A1),FIND("-",CELL("filename",A1))-4,4),MID(CELL("filename",A1),FIND("-",CELL("filename",A1))
+1,2),1)

which returns 09/01/2010 00:00.

In cell A3 I have =A1+1/24 which returns 09/01/2010 01:00 and this
formula is copied down the column. The formula returns the right date/
time when formated as date/time but when I enter the same date/time
and reformat as number they are not the same.
Example
A6 returns 9/1/2010 6:00 formatted as number 40,422.250000000000
A7 (A6+1/24) returns 9/1/2010 7:00 formatted as number
40,422.29166666660000
then 9/1/2010 7:00 manually entered and formated as number =
40,422.291666666700

I am trying to compare date/time from formula to one that is entered
and although they look the same and are the same formatted as dae/
time, when compared by formula they aren't. I suppose that it is a
rounding issue but how do I fix it?
Thanks




A7=A6+1/24

 

[joeu2004]

 I am deriving the date using
=DATE(MID(CELL("filename",A1),FIND("-",CELL("filename",A1))-4,4),
MID(CELL("filename",A1),FIND("-",CELL("filename",A1))+1,2),1)
which returns 09/01/2010 00:00.

In cell A3 I have =A1+1/24 which returns 09/01/2010 01:00 and this
formula is copied down the column. [....]
I am trying to compare date/time from formula to one that is entered
[manually] and although they look the same and are the same formatted
as dae/time, when compared by formula they aren't.  I suppose that it is
a rounding issue but how do I fix it?

Correct. Precisely what I had explained in the thread "Date Time
increment with additional condition of skipping weekends" at
http://groups.google.com/group/micr...functions/browse_frm/thread/c5ab0b93c0dfe752#.

The fix:

=--TEXT(A1+1/24,"m/d/yyyy h:m")

which you format as you like, e.g. Custom "mm/dd/yyyy hh:mm" without
quotes.