Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time format
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Time format
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to format time in decimal format & calculate time | Excel Discussion (Misc queries) | |||
How to format time in decimal format & calculate time | Excel Discussion (Misc queries) | |||
How to format time in decimal format & calculate time | Excel Discussion (Misc queries) | |||
How to type format time in decimal format & calculate time | Excel Discussion (Misc queries) | |||
Remove time from a date and time field? Format removes the displa. | Excel Worksheet Functions |