Entering Military Time/Adding & Subtracting time

[Gadgetman]

Please help with a few issues...

Is there a way for me to be able to enter military time in a cell
without using the colon )? When I format the cell using a custom
format of hhmm, and then enter a time, it just converts it to all zeros.

What I am trying to accomplish is adding total time on a certain
project. For instance in cell A1 for example, I'd like to be able to
enter a START TIME (in military time without using a colon such as 1450).

In the cell next to it, B1, I'd like to enter a FINISHED TIME. And in
C1, a TOTAL TIME where I'd like it to total the time on the project, but
if possible, to display the TOTAL TIME as 2 hr 1 0m for example.


Right now, the only thing I can figure out how to do is use the standard
time format of hh:mm in all 3 cells, but then it totals the time by
displaying it 02:10 (for 2 hr 10 min for example) using a formula in C1
of =B1-A1.

Is there a way to display the Start and Finished times in military times
WITHOUT the colon and to have the TOTAL TIME display as 2 hr 10 min
instead of 02:10? Am I using the correct formula in C1?

Thanks for the help.

 

[Bernard Rey]

One way could be:

In cell C1, type:

=TIME(LEFT(B1,LEN(B1)-2),RIGHT(B1,2),)-TIME(LEFT(A1,LEN(A1)-2),RIGHT(A1,2),)

Then change the cell format in C1 to a custom:
[h]" hr "m" min"

 

[JE McGimpsey]

Is there a way for me to be able to enter military time in a cell
without using the colon )? When I format the cell using a custom
format of hhmm, and then enter a time, it just converts it to all zeros.

The input parser doesn't take display format into account (except for
Text - then it doesn't parse the entry).

To do what you want to do requires VBA. Take a look here:

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

 

[JE McGimpsey]

Is there a way to display the Start and Finished times in military times
WITHOUT the colon and to have the TOTAL TIME display as 2 hr 10 min
instead of 02:10? Am I using the correct formula in C1?

To display the format you want:

Format/Cells/Number/Custom [h] \hr m \min

You're using the correct formula as long as the times all fall within
the same day. If the times may span midnight, then the "later" time
might be less than the "earlier" time (e.g., 2100 = 0.875, 0300 = 0.125).

In that case either use XL's coercion of boolean (TRUE/FALSE) values to
1/0, respectively:

=B1 - A1 + (B1<A1)

Or use the somewhat more obscure MOD() alternative:

=MOD(B1-A1,1)

 

[Gadgetman]

Is there a way to display the Start and Finished times in military times
WITHOUT the colon and to have the TOTAL TIME display as 2 hr 10 min
instead of 02:10? Am I using the correct formula in C1?

To display the format you want:

Format/Cells/Number/Custom [h] \hr m \min

You're using the correct formula as long as the times all fall within
the same day.

Thanks for the help. All my times entered are going to be w/in the same
day so I guess I'll be okay with the formula FINISHED TIME - START TIME.

Is there a way to discard the hours portion of the resulting TOTAL time
if it does not go at least an hour. For instance, if I enter 1430 in the
Start time and 1500 in the Finished time, can i just have it display
30min and not 0 hr 30 min

 

[JE McGimpsey]

Thanks for the help. All my times entered are going to be w/in the same
day so I guess I'll be okay with the formula FINISHED TIME - START TIME.

Is there a way to discard the hours portion of the resulting TOTAL time
if it does not go at least an hour. For instance, if I enter 1430 in the
Start time and 1500 in the Finished time, can i just have it display
30min and not 0 hr 30 min

One way:

Format/Cells/Number/Custom: [<0.041655092592593][m] \min;[h] \hr m \min

 

[Gadgetman]

You're using the correct formula as long as the times all fall within
the same day. If the times may span midnight, then the "later" time
might be less than the "earlier" time (e.g., 2100 = 0.875, 0300 = 0.125).

In that case either use XL's coercion of boolean (TRUE/FALSE) values to
1/0, respectively:

=B1 - A1 + (B1<A1)

Or use the somewhat more obscure MOD() alternative:

=MOD(B1-A1,1)

Mr McGimpsey
I was mistaken when I replied saying that all the times added will be in
the same date. When I thought more about it, there are going to be times
when over a midnight shift, one could potentially start a job prior to
midnight and end it into the next date. Can you elaborate on your way of
adding times in that case? Im not real clear on how to do it. I'd
appreciate your help
Thanks

 

[JE McGimpsey]

In that case either use XL's coercion of boolean (TRUE/FALSE) values to
1/0, respectively:

=B1 - A1 + (B1<A1)

Or use the somewhat more obscure MOD() alternative:

=MOD(B1-A1,1)

Mr McGimpsey
I was mistaken when I replied saying that all the times added will be in
the same date. When I thought more about it, there are going to be times
when over a midnight shift, one could potentially start a job prior to
midnight and end it into the next date. Can you elaborate on your way of
adding times in that case? Im not real clear on how to do it. I'd
appreciate your help[/QUOTE]

Use either of the two methods above. I'm not sure what you're confused
about:

A1: 21:00
B1: 03:00
C1: =MOD(B1-A1,1) ==> 6:00 (when formatted as time)

 

[Gadgetman]

Is there a way to discard the hours portion of the resulting TOTAL time
if it does not go at least an hour. For instance, if I enter 1430 in the
Start time and 1500 in the Finished time, can i just have it display
30min and not 0 hr 30 min

One way:

Format/Cells/Number/Custom: [<0.041655092592593][m] \min;[h] \hr m \min[/QUOTE]


2 other things: Using the above custom format as a guide...

Is there a way to just display only "1 hr" in the cell instead of "1 hr
0 min" if it's exactly an hour?

Also, Is there a way to hide the 0 min that defaults if nothing is
entered in the Starting Time and/or Ending Time cells and there is
nothing to add?

Again, I'm very appreciative of the help. I'm not too great w/ these
formats etc.

 

[JE McGimpsey]

2 other things: Using the above custom format as a guide...

Is there a way to just display only "1 hr" in the cell instead of "1 hr
0 min" if it's exactly an hour?

Not really... You could do it for 1 hour:

[<0.041655092592593][m] \min;[<0.0416782407407407][h] \hr;[h] \hr m \min

but 2 hours exactly would display as 2 hr 0 min

Also, Is there a way to hide the 0 min that defaults if nothing is
entered in the Starting Time and/or Ending Time cells and there is
nothing to add?

Couple of ways:

Preferences/View uncheck the "Zero values" checkbox.

change the format to

[<=0]"";[<0.041655092592593][m] \min;[h] \hr m \min

 

[Gadgetman]

Thanks a bunch!

2 other things: Using the above custom format as a guide...

Is there a way to just display only "1 hr" in the cell instead of "1 hr
0 min" if it's exactly an hour?

Not really... You could do it for 1 hour:

[<0.041655092592593][m] \min;[<0.0416782407407407][h] \hr;[h] \hr m \min

but 2 hours exactly would display as 2 hr 0 min

Also, Is there a way to hide the 0 min that defaults if nothing is
entered in the Starting Time and/or Ending Time cells and there is
nothing to add?

Couple of ways:

Preferences/View uncheck the "Zero values" checkbox.

change the format to

[<=0]"";[<0.041655092592593][m] \min;[h] \hr m \min