Entering Time as HHMM; having it converted to HH:MM

TC · 08-21-2006, 11:55 AM

I need to enter elapsed start and stop times (military time, as hours
and minutes) into a spreadsheet, for calculations of elapsed time in
minutes (calculation used: stop time - start time * 24 * 60).

I hate having to reach over to enter the colon between the hours and
minutes, as it really slows me down (there are 50-60 entries on each
spreadsheet I need to do). Does anyone know a way that I can enter
the start and stop times without any colons, commas or periods, and
still have a calculation performed to provide me with elapsed minutes?

Thanks for your help.

TC

Niek Otten · 08-21-2006, 12:25 PM

http://www.cpearson.com/excel/DateTimeEntry.htm

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"TC" <tecNOSPAM1@earthNOSPAMlink.net> wrote in message news:81eje2dq9nkk0j7hpgtvgg46haar6iom6o@4ax.com...
|I need to enter elapsed start and stop times (military time, as hours
| and minutes) into a spreadsheet, for calculations of elapsed time in
| minutes (calculation used: stop time - start time * 24 * 60).
|
| I hate having to reach over to enter the colon between the hours and
| minutes, as it really slows me down (there are 50-60 entries on each
| spreadsheet I need to do). Does anyone know a way that I can enter
| the start and stop times without any colons, commas or periods, and
| still have a calculation performed to provide me with elapsed minutes?
|
| Thanks for your help.
|
| TC

**Dav** · 08-21-2006, 12:25 PM

Simply
TIME(LEFT(B2,LEN(B2)-2),RIGHT(B2,2),0)-TIME(LEFT(A2,LEN(A2)-2),RIGHT(A2,2),0)

To make sure the time does not go over midnight and you get a negative time

=IF(ISERROR(TIME(LEFT(B2,LEN(B2)-2),RIGHT(B2,2),0)-TIME(LEFT(A2,LEN(A2)-2),RIGHT(A2,2),0)),TIME(LEFT(B2,LEN(B2)-2)+1,RIGHT(B2,2),0)-TIME(LEFT(A2,LEN(A2)-2),RIGHT(A2,2),0),TIME(LEFT(B2,LEN(B2)-2),RIGHT(B2,2),0)-TIME(LEFT(A2,LEN(A2)-2),RIGHT(A2,2),0))

format as hh:mm

Regards

Dav

**Chrisb1985** · 07-30-2013, 08:55 AM

Hi,

Can anyone tell me where you would enter either of these codes? I am trying to achieve the same thing as TC was.

I have tried entering the code given in Neik Otten's post directly into the VBA in the "ThisWorkbook" object of the spreadsheet I am working on, and this doesn't appear to have done anything.

I have also tried entering Dav's code in the same place, and these also doesn't appear to have changed anything.

If anyone could help, I would really appreciate it.

Apologies if I what I am asking is something very basic!

Kind Regards

Chris

**Chrisb1985** · 07-31-2013, 09:50 AM

Can anyone help with this?

Thanks

Chris

**arlu1201** · 08-01-2013, 08:29 AM

Chrisb,

Unfortunately you need to post your question in a new thread, it's against the forum rules to post a question in the thread of another user. If you create your own thread, any advice will be tailored to your situation so you should include a description of what you've done and are trying to do. Also, if you feel that this thread is particularly relevant to what you are trying to do, you can surely include a link to it in your new thread.

**Chrisb1985** · 08-01-2013, 09:12 AM

Thanks arlu, I have done that here: http://www.excelforum.com/excel-gene...-to-hh-mm.html

**kmahesh** · 08-02-2013, 10:49 AM

Try this,
suppose you are entering time as 1130 in cell A1
now in A2(or wherever you want it as 11:30) enter this formula
=LEFT(A1,2)&":"&RIGHT(A1,2)
then format this cell as Custom and type [h]:mm