Military time used in calculating payroll hours

jzuber

New Member
Joined
Oct 13, 2002
Messages
45
I have payroll hours in military time. It is in 100th of a minute.
I need to:

enter beginning hours, in military time
enter ending hours, in military time

calculate actual hours elapsed, rounded to the nearest quarter hour

:rolleyes:
Can anyone help me?
 
Using Paddy's formula suggestion and Yogi's input
Book1
ABCD
115:400:308:458.75
Sheet1


You can't enter time in Excel as 15:84. What you can do is convert to decimal as shown in the above example. You can also hide any columns you wish, but you'll need to enter times that Excel recognizes.

Yogi...again pardon the intrusion. :LOL:
 
Upvote 0

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.
Hi Yogi!

You are amazingly patient.

My start time -- right off the time clock = 15:84
My end time = 0:23

You're right that this should be easy. I can't seem to just enter 15:84. No matter how I format the cell, when I enter 15:84 (or anything else with a colon), it gives me a decimal. (ie., 15:84 becomes .633333).

I can do the math in my head faster than this!

Thanks for your help.

JAZ
 
Upvote 0
jzuber said:
Hi Yogi!

You are amazingly patient.

My start time -- right off the time clock = 15:84
My end time = 0:23

You're right that this should be easy. I can't seem to just enter 15:84. No matter how I format the cell, when I enter 15:84 (or anything else with a colon), it gives me a decimal. (ie., 15:84 becomes .633333).

I can do the math in my head faster than this!

Thanks for your help.

JAZ

Hi Jaz:

Clear understanding, or explanation of the question is the hard part. If the question is clearly understood, I believe, solution can be found. Now I still have a couple of more questions for you, and once those are cleared up, I think we will head for the home stretch pretty comfortably.

I recall your mentioning that when you say the StartTime is 15:84, you mean to say 15th hour and 84/100 of an hour which means the Start time is 15.84 hours from the 0 hour (midnight) last night. I believe we are all set on this one.

When you say EndTime is 0:23 -- normally when we enter 0:23, we would expect it to mean we have gone 23 minutes past the midnight (that means now we have gone into the next day (date wise). The 23 minutes part is the one where I have the question, if 15:84 meant 15th hour and 84/100 of an hour, so would 0:23 per your clock mean 0th hour and 23/100 of an hour? In that case in Excel we would write it 0.23 hour

so if my StartTime is 15.84 hour on 7-Aug-2003
then my EndTime is 0.23 hour on Aug-8-2003

so I am going to base my solution on the assumption in the preceding, as depicted in the following simulation ...
Book1
ABCDEF
1StartTime15.84hour07-Aug-03Given
2EndTime0.23hour08-Aug-03intermediarycomputation
30.34958hourworkTimeindaysresultsinHoursandMinutes
48:23workTimeinHoursandMinutesresultinHoursandfractionsofanhour
58.39workTimeinHoursandfractionsofanhour
6
Sheet1


So, the simulation is based on several presumptions since your description does not provide definitive information. I hope the simulation will give you some idea about the implications of knowing the correct given information. If I have missed the point again, my apologies!
 
Upvote 0
Yogi:

I'm making progress on this -- I know that's kind of hard to believe. I understand B1, B2, B3 and B5 in your simulation. I understand the results in B4, but I can't figure our what formula created this result.

If I'm reading the simulation correctly, it says b4=b3. Is this a formatting issue or am I missing a formula?

Clearly I've missed the boat already! Once again, thanks for your help.

JAZ
 
Upvote 0
jzuber said:
Yogi:

I'm making progress on this -- I know that's kind of hard to believe. I understand B1, B2, B3 and B5 in your simulation. I understand the results in B4, but I can't figure our what formula created this result.

If I'm reading the simulation correctly, it says b4=b3. Is this a formatting issue or am I missing a formula?

Clearly I've missed the boat already! Once again, thanks for your help.

JAZ

Jaz,

Since I'm always butting in and don't see Yogi around,

B4 is customed formatted as [h]:mm
B5 is formatted as Number, two decimal places
 
Upvote 0
Brian from Maui said:
....
Since I'm always butting in ....
Hi Brian:
on the contrary -- I find you as being very helpful all the time -- and thanks for pitching in.

Keep EXCELing!
 
Upvote 0
Yogi & Brian


It worked! It worked! Not only did it work, I actually understand what it did.

Thanks to the Masters ! You are my heros.

JAZ
 
Upvote 0
I am having issues with figuring out time card that uses military time but this person works the graveyard shift so he goes in at 17.32 one night and clocks out at 6.03. How do I figure this out?:(
 
Last edited:
Upvote 0
For shifts that are less than 24 hours,

Code:
      --A-- --B-- --C-- ----------D-----------
  1    In    Out  Time                        
  2   17:32 06:03 12:31 C2: =B2-A2 + (B2 < A2)
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top