Time format

[PD]

11:59:00 PMOne of the files we've to use requires the staff to update the time.

The timings have to be between 21:00 to 08:00 hrs(am). On attempting to use
data validation, it did not work. Probably because the format after 0 hrs
changes and excel does not differentiate between them, ie, 01:01 hrs is 01:01
am on the worksheet but does excel read this differently

I typed 21:00 and dragged it down on the worksheet. It showed that the
format remained as h:mm(custom. However, the formula bar showed the time
differently for time before and after 11:59. All the entries before 11:59 had
the format as 9:00:00 PM for 21:00, 11:59:00 PM for 11:59, but changed to
1/1/1900 12:00:00 AM fro 0 hrs.

I did data validation time and put the time between 21:00 hrs and 08:00,
However, whenever we put any time after 11:59, it throws an error.

The file has column B marked from row 3 to 633 with the time and has to be
manully inout by the staff. The validation should prevent them from entering
time in any other format than 24 hrs, ie 0:00 instead of 12:00.

Also when I used value() function for 02:00 in the 1/1/1900 12:00:00 AM, it
showed 1.083333333 and if manually entered in 2:00:00 AM format, it showed
0.083333333. Why is this difference?

Also why is the format different when dragged and entered manually?

Could you please advise how this can be corrected. Apoloies for the length
of the message.

Thanks.

 

[NoodNutt]

G'day PD

Assume:

A1 = Start Time 'Format h:mm
B1 = Finish Time ' Same format

C1=IF(A1="","",MOD(B1-A1,1))

Change cell references to suit
Copy down as required

HTH
Mark