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

[TC]

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]

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

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

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

Da