Date Time

T

TBFJ

Can Excel recognize 2400 hours as midnight today? Every formula I try forces
the date/time to show it as 1st thing tomorrow morning. ie, it adds one day
to the date, when it should not do so until 0001 hours.

Thanks in advance.

TBF
 
G

Gary''s Student

That's because 2400 is the roll-over point.

In A1 thru C1 put:
2359 =TIME(A1/100,A1-100*INT(A1/100),0) =B1
and you will see:
2359 11:59 PM 0.9993055556 with the correct formatting

However if you put 2400 in A1 you will see:
2400 12:00 AM 0.0000000000 with the same formatting
 
T

TBFJ

That is what I suspected was the "Feature" of Excel. Do you know of a way to
correct this? I am working with weather data and the data that is summerized
at midnight is for the preceeding 24 hours, so should have that date on it,
not the next day's. The only way I have found is to do "if time=2400 then
date=date-1" but that is not really correct since that actually puts that
data at the beginning of the day instead of the end of the day.

Thanks for your thoughts ===> TBFJ
 
G

Gary''s Student

Move it back by one minute or even 1 second (if possible)

=IF(A1=2400,2359,A1)
 
R

Roger Govier

Hi

To move it back by 1 second
=IF(MOD(A1,1)=0,(A1-TIME(0,0,1)),A1)

Regards

Roger Govier
 
Top