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..

You’re entering a world of pain…

The answer to your question can be 00″:”00

If you need to do anything with these figures i.e. arithmetic, then you’re in for a frustrating time.

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)))

Do you need the colon? You could use a full stop which is pretty standard eg 09.30 or no punctuation eg 0930. That’s the norm in the military and if its a time column there can’t be any confusion.

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, Paul

Ctrl+1 for the format dialogue box, then custom format at bottom of list.

Type 00″:”00 in the box on the right

Rob Hilton – EXACTLY what I needed! Thank you so much 8)

Its a little thing that just makes life easier!

May you have many good things!!

May you have many good things!!

Pay it forward 😀

It just occurred to me: using this format means you’d only have to type in 130 to display 01:30 – the fewer keystrokes, the better, eh?

I found out about the (eg) 130 thing by accident and was chuffed!

I’ll endeavour to pay it forward indeed 😀

Cheers fella

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?
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.

A great tip for making it display properly, but that doesn’t actually format it as a time does it?
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.

Really?? Do enlighten us, please.

Because, e.g. the number of hours between 01:30 and 06:00 is not 4:70 !

No idea if this helps you but if you press ctrl+shift+semi-colon then the current time is inserted into a cell and it formats it as e.g. “10:40”.

^^^
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.

Because, e.g. the number of hours between 01:30 and 06:00 is not 4:70 !

I was being sar car stick. This was referred to in the 2nd post.

Oh. It came across as a massively smug I’m-right-you’re-wrong.