Hi there I have another problem.
I want time formats in cells to be 1:00 and I want 13:00 to be displayed as 1:00 as well. so 13:00 or 1:00 PM should be 1:00
I have seen a few macros that kinda do this but I cant figure them out.
Thanks
Steven
Hi
I do not trhink that you can change this in formatting however you could use a second cell to hold the actual time and an IF function to display the tme you wish. bearing in mind that time in excel is a decimal value with a whole day being 1, then if your source cell was A1 then the followingwould display the way you wish.Code:=IF(A1>0.5:A1-0.5:A1)
Regards
Jeff
Last edited by solnajeff; 01-15-2009 at 10:48 AM.
I used this formula, Jeff. Thanks.
In my ss, I used data validation to ensure shift start and end time are valid entries.
To create a version that is 12-hour and without the AM/PM, I used this:
=IF(B7<>"",IF(B7>0.5,TEXT(B7-0.5,"h:mm"),TEXT(B7,"h:mm")),"") & "-" & IF(D7<>"",IF(D7>0.5,TEXT(D7-0.5,"h:mm"),TEXT(D7,"h:mm")),"")
Via data validation, B7 contains my start time and D7 the end time.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks