Excel time format query. Should be easy!
I have an Excel sheet (Excel 2010) that is used as an online log (working offshore on a survey vessel) & I am a bit lazy (erm, trying to be more ‘efficient’!) so looking to save time:
Is there a way that I can enter the time as (eg) 0130 and have it appear in the cell as 01:30 ie not have to type the : ??
I think I need to format the cell as text but how do I get the : to appear ‘automatically’?
I know there are some Excel wizards on here so this, surely, should be easy! It would save me a load of time over a 12 hour shift. Other people then copy the log and need the time displayed as (eg) 01:30 so I can’t just use 0130.
As ever, ta in advance..Posted 1 year agoirelanstMember
You could use a formula in the next column, if you type 1130 in cell A1 then in B1 you would need;
=Time(Left(A1,2),Right(A1,2)) And you need column A:A formatted as text.
You would need to be careful about using 130 instead of 0130 though. You could put an IF loop in to check for that;
=IF(LEN(A1)=3,Time(Left(A1,1),Right(A1,2)),Time(Left(A1,2),Right(A1,2)))Posted 1 year ago
Rob – thanks; how would I enter this in Excel so I can just tupe in (eg) 0130. The time is not used in any arithmetic at all.
Irelanst – ideally I don’t want to enter another column if needs be
Nick – If it was down to me it would be entered 0130 but its a long running project and the client wants continuity. Booo!
Thanks for the help so far. I’m offline for 12 hours now as its the end of my shift and WiFi hasn’t reached this vessel yet!
Cheers, PaulPosted 1 year agorugbydickSubscriber
Ctrl+1 for the format dialogue box, then custom format at bottom of list.
Type 00″:”00 in the box on the right
A great tip for making it display properly, but that doesn’t actually format it as a time does it?Posted 1 year ago
If you need to do any calculations with the entry (e.g. calculating how many hours it is since 01:30), then it won’t work.jam boSubscriber
this wins, and you can do date/time arithmetic on it as well as underneath is stored as a decimal day number.
if you do ctrl + : following by a space and then ctrl + shift + :
does a proper date time.
however, if your oppo comes along and just types a number in it’ll all go to shit.Posted 1 year ago
The topic ‘Excel time format query. Should be easy!’ is closed to new replies.