Data Validation for Time (h:mm)

[lucy61176]

I am trying to limit my users to entering hours and minutes as h:mm only. The total hours and minutes can be greater than a 24-hour day, however, as in total vacation for a year. Any ideas? I'm drawing a blank. Thanks

 

[Asala42]

Use a custom [h]:mm format rather than h:mm, that will let you display hours more than 24 hours.

 

[lucy61176]

The cells are already formatted as [h]:mm. What I need is data validation so that the user is required to enter the time in that format with the colon. I tried setting the data validation for Time between 12:00:00 AM and 11:59:00 PM, but the time could be greater than 24:00.

 

[lucy61176]

If anyone can help, I really need it! Thanks

 

[lucy61176]

I'm stuck at using Data Validation for time between 12:00:00 AM and 11:59:00 PM. If I do that, the user cannot enter the total time an employee has of vacation for the year, for example, 180:00. Does anyone know how to validate the data to allow for hours and minutes greater than 24?

 

[lucy61176]

Maybe this detailed explanation with a link to my form will help.

I have an annual time/absence record. The timekeeper fills it out annually and inputs the employee's total hours of sick leave, vacation, sabbatical, personal holiday, and legal holiday. Throughout the year, the timekeeper inputs hours:minutes the employee uses, and the spreadsheet calculates. I would like to use data validation so that the user is notified that they need to enter the time as hh:mm and not as a decimal or whole number. I tried data validation for Time Between 12:00:00 AM and 11:59:00 PM; however, then I cannot enter anything over 24:00. Is there any way I can do a custom validation for this? Any help is GREATLY appreciated! Thanks

Here is the link to my excel spreadsheet. Maybe this will help.

https://onedrive.live.com/<wbr>redir?resid=BBE57EBA0DFD80DF!<wbr>128&authkey=!AJjAWS5jeOKec4M&<wbr>ithint=file%2cxlsm

 

[Asala42]

From another post, try changing the allow criteria to Date with criteria less than 10/02/07 15:00. Keep the cell format as [h]:mm