[SOLVED] military time

[Bbud]

My troops report their time in and out in military time (1030 - 1400 hrs). I want to show how much time was spent in hours/minutes. How can I do that? Thx

 

[USAFRet]

My troops report their time in and out in military time (1030 - 1400 hrs). I want to show how much time was spent in hours/minutes. How can I do that? Thx

1030 = 10:30 AM
1400 = 2:00 PM.

3.5 hours.

If you have 'troops', then surely you know what military time is, and how it is calculated.

In what context do you need this number?
What software/application?

 

[Bbud]

I'm using Google Sheets. Troops can refer to any group, in this case, first responders. We track hours worked for insurance purposes.

Yes, we submit military time in and out, and I need to know the duration in hours:minutes for reporting purposes.

thanks for any help you can offer.

 

[Bob.B]

I'm using Google Sheets. Troops can refer to any group, in this case, first responders. We track hours worked for insurance purposes.

Yes, we submit military time in and out, and I need to know the duration in hours:minutes for reporting purposes.

thanks for any help you can offer.

Military time is just a 24hr clock.
The clock starts a mid night.......0000.
5am....0500.
1pm...1300.
8pm...2000.
There is still 60mins in a hour.
None of this am/pm nonsense.

 

[ex_bubblehead]

I'm using Google Sheets. Troops can refer to any group, in this case, first responders. We track hours worked for insurance purposes.

Yes, we submit military time in and out, and I need to know the duration in hours:minutes for reporting purposes.

thanks for any help you can offer.

Google Sheets should provide a function that converts Date/Time to a date serial upon which you can do math by just subtracting the smaller from the larger to get the difference, which is your answer when converted back.

 

[hotaru.hino]

If you have people fill out a Google Sheets template, you can use what they input to do some calculations somewhere else in the spreadsheet.

For example if you have them fill out a couple of rows for time with standard military time formatting (i.e., just a number, no colons), then you can simply treat that as a regular number and perform some math on it, like so:

• Note that START_TIME and END_TIME are the cells where the person fills out a start time and end time respectively
• Have two cells equal the following to get the hours down to the nearest whole number
  • =INT(START_TIME/100)
  • =INT(END_TIME/100)
• Have two more cells equal the following to get the minutes in terms of partial hours:
  • =MOD(START_TIME, 100)/60
  • =MOD(END_TIME, 100)/60
• Find the difference between the hours and the partial hours (minutes), then combine them

If you need to report in the format of HH:MM, then this formula should work: =INT(DIFF_TIME)&":"&(60*(DIFF_TIME - TRUNC(DIFF_TIME))), where DIFF_TIME is the cell that holds the difference in time between START_TIME and END_TIME. Note, the coloring is just to make it easier to see the different formulas used.

You may need to mess with the formatting rules if you want the time to show up with a leading zero (https://spreadsheetpoint.com/keep-leading-zeros-in-google-sheets/)

This also breaks down if they enter something greater than 59 for minutes. So if you want to get extra fancy, you can do something to catch this. And there's the issue of whether 12:00 AM is 2400 or 0000 (I'm pretty sure military time reports 12:00 AM as 2400)

 

[Bbud]

Military time is just a 24hr clock.
The clock starts a mid night.......0000.
5am....0500.
1pm...1300.
8pm...2000.
There is still 60mins in a hour.
None of this am/pm nonsense.

If you have people fill out a Google Sheets template, you can use what they input to do some calculations somewhere else in the spreadsheet.

For example if you have them fill out a couple of rows for time with standard military time formatting (i.e., just a number, no colons), then you can simply treat that as a regular number and perform some math on it, like so:

• Note that START_TIME and END_TIME are the cells where the person fills out a start time and end time respectively
• Have two cells equal the following to get the hours down to the nearest whole number
  • =INT(START_TIME/100)
  • =INT(END_TIME/100)
• Have two more cells equal the following to get the minutes in terms of partial hours:
  • =MOD(START_TIME, 100)/60
  • =MOD(END_TIME, 100)/60
• Find the difference between the hours and the partial hours (minutes), then combine them

If you need to report in the format of HH:MM, then this formula should work: =INT(DIFF_TIME)&":"&(60*(DIFF_TIME - TRUNC(DIFF_TIME))), where DIFF_TIME is the cell that holds the difference in time between START_TIME and END_TIME. Note, the coloring is just to make it easier to see the different formulas used.

You may need to mess with the formatting rules if you want the time to show up with a leading zero (https://spreadsheetpoint.com/keep-leading-zeros-in-google-sheets/)

This also breaks down if they enter something greater than 59 for minutes. So if you want to get extra fancy, you can do something to catch this. And there's the issue of whether 12:00 AM is 2400 or 0000 (I'm pretty sure military time reports 12:00 AM as 2400)

Thanks. I'll see what I can do.

 

[Bbud]

If you have people fill out a Google Sheets template, you can use what they input to do some calculations somewhere else in the spreadsheet.

For example if you have them fill out a couple of rows for time with standard military time formatting (i.e., just a number, no colons), then you can simply treat that as a regular number and perform some math on it, like so:

• Note that START_TIME and END_TIME are the cells where the person fills out a start time and end time respectively
• Have two cells equal the following to get the hours down to the nearest whole number
  • =INT(START_TIME/100)
  • =INT(END_TIME/100)
• Have two more cells equal the following to get the minutes in terms of partial hours:
  • =MOD(START_TIME, 100)/60
  • =MOD(END_TIME, 100)/60
• Find the difference between the hours and the partial hours (minutes), then combine them

If you need to report in the format of HH:MM, then this formula should work: =INT(DIFF_TIME)&":"&(60*(DIFF_TIME - TRUNC(DIFF_TIME))), where DIFF_TIME is the cell that holds the difference in time between START_TIME and END_TIME. Note, the coloring is just to make it easier to see the different formulas used.

You may need to mess with the formatting rules if you want the time to show up with a leading zero (https://spreadsheetpoint.com/keep-leading-zeros-in-google-sheets/)

This also breaks down if they enter something greater than 59 for minutes. So if you want to get extra fancy, you can do something to catch this. And there's the issue of whether 12:00 AM is 2400 or 0000 (I'm pretty sure military time reports 12:00 AM as 2400)

Thanks so much for this! I think it will work well. My only question has to do with cell format. Should all these cells be simple number, or automatic, or something else? Some of the outputs show negative numbers, or 15 digits following a period, etc. when clearly they should all be positive. Is there a way I can send you the file I'm working on?

 

[USAFRet]

Thanks so much for this! I think it will work well. My only question has to do with cell format. Should all these cells be simple number, or automatic, or something else? Some of the outputs show negative numbers, or 15 digits following a period, etc. when clearly they should all be positive. Is there a way I can send you the file I'm working on?

Cells formatted as Time, HH:MM.

D3 = B3-A3

Sheets does not want to seem to maintain the leading zero, but it works.

 

[hotaru.hino]

Thanks so much for this! I think it will work well. My only question has to do with cell format. Should all these cells be simple number, or automatic, or something else? Some of the outputs show negative numbers, or 15 digits following a period, etc. when clearly they should all be positive. Is there a way I can send you the file I'm working on?

You can create a sharable link and DM it to me if you'd like (no guarantees on when I'll get to it though)

 

[Bbud]

Thanks. I'm going to try to work this out myself, but if I still can't, I'll do that.

 

[ex_bubblehead]

Don't forget that you have to account for crossing 0000 as in Start 2100 Monday, End 0600 Tuesday. It's not just a straight subtraction.

 

[Bbud]

Don't forget that you have to account for crossing 0000 as in Start 2100 Monday, End 0600 Tuesday. It's not just a straight subtraction.

Got it! Everyone fills out times by day, not total elapsed, so that's taken care of. Thanks for the comment though.

 

[Bbud]

Got it! Everyone fills out times by day, not total elapsed, so that's taken care of. Thanks for the comment though.

Only problem now is adding a column of times. I'm using =SUM(F5:F57), but won't add times of 1:20, 2:30, 0:30, 2:00, etc.