is it possible to just enter time and have the format in HH:MM?

[RockandGrohl]

I would like to just enter "1400" and have it format to "14:00" if possible.

I also have a VBA code that automatically re-formats cells to the correct formatting, would this be able to also successfully convert 1400 to 14:00 for instance?

 

[Special-K99]

Ok, lets say you enter 1400 into a cell.
The cell contains 1400, that's one thousand four hundred.

And let's say the FORMAT of the cell displays the value as 14:00

This does NOT change the value of the cell, the value of the cell is still 1400 (one thousand four hundred.).

Is that the value you want the cell to contain or do you want 1400 converted into 14:00 (perhaps to do other calculations with later)?

If the latter use a helper column with this formula

in B1
=TIME(--LEFT(A1,2),--RIGHT(A1,2),0)

 

[RockandGrohl]

Err, I think either or would work. The reason being is that it gets transferred onto a timesheet which then gets printed, so as long as the end user sees "14:00" or whichever variable of this.

 

[Special-K99]

Something to bear in mind, if your timesheet is gonna print totals, add times up like in-times and out-times, like how many hours worked then you'll need to convert to proper times.

e.g.
1430 +0130 = 1560 but that's really 16:00, ie 1600


This page looks like it has several solutions

https://www.mrexcel.com/forum/excel-questions/1014107-accept-military-time-without-colon.html

 

[RockandGrohl]

Something to bear in mind, if your timesheet is gonna print totals, add times up like in-times and out-times, like how many hours worked then you'll need to convert to proper times.

e.g.
1430 +0130 = 1560 but that's really 16:00, ie 1600


This page looks like it has several solutions

https://www.mrexcel.com/forum/excel-questions/1014107-accept-military-time-without-colon.html

Hey,

I said Timesheet but it's nothing as rigorous as that, it's just so an operator knows what time to pick up a client. There will be no addition or multiplication of time etc.

I just need it so that when they type in "1356" it plops a colon in the middle.

 

[Special-K99]

That link I sent, one of the pages says use this Custom format

00\:00

Entering 1234 produces 12:34

 

[JMcCarthy0929]

If I type in 1300 how can it automatically change to 1:00PM. This will be ten times easier then having people type the time on their own, or is their a way that the time can self populate once date is inserted into a cell?

 

[Special-K99]

You either need a formula or VBA.
Some solutions here (I dont have time to read all this)

Accept Military Time without a colon ":"

Hi there, Here is the my situation. I want to enter time into the spreadsheet as military time. Typing 1445 is much faster than 14:45, and it's insanely faster than 2:45 PM. The problem is, that Excel forces me to use the colon on military time (even though military time doesn't actually use...

www.mrexcel.com