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?
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
=MROUND(b1-a1,1/(24*4))

...where B1 contains end time & a1 start. assumes:

1) entries are held as genuine time values
2) times do not cross midnight, or if they do they are associated with a date value.
3) analysis tool pack is installed (tools | addins)
 
Upvote 0
I guess I'm more confused than I thought.

How do I enter the military time? What format do I use?

As an example: Beginning = 16:90

Ending = 0:38 -- This will also cross over a date.

I do have add-ins installed, but I'm not sure how this fits into the problem.

Any help is greatly appreciated!
 
Upvote 0
Hi jzuber:

Military time would be entered in 24 hour format without the need to enter AM or PM. So let us say I start work today (Aug-06-2003) at 16:45 and I work till 00:30 hour. Then my ...

Start Time is entered as 8/6/03 16:45
and
Quit Time is entered as 8/7/03 00:30

I hope this helps. If I have misunderstood your question -- my apologies!
 
Upvote 0
Hi Yogi:

Thanks for taking the time to help me out. I think the beginning of my confusion is keying the date into excel.

When I enter the time (ie., 15:84) excel automatically associates a date with it .... or tries to. How do I format the column?

Do I enter a date, then the time in the same cell?

As you can see, I'm quite confused.

Thanks again, JAZ
 
Upvote 0
Hi jzuber:

Excel handles Time as part of the day -- think about it a day has 24 hours, so an hour is 1/24 of a day, and so on.

Now in regard to entering Date and Time ...

Date and Time can be entered separately or together -- but please bear in mind since time is part of a day, whether these are entered together or separately may be a matter of need or preference. The biggest confusion comes in the Formating aspect -- Date and Time can be formated in different ways -- but regardless of how something is formated, its underlying value remains unchanged.

Please look at the following spreadsheet simulation ...
y030806h1.xls
ABCD
1
2StartTime8/6/0316:45
3QuitTime8/7/030:30
4
Sheet6


In cell B2, I entered the date, then a space and then hours, then a : and then minutes

Does it help?
 
Upvote 0
jzuber said:
Hi Yogi:

Thanks for taking the time to help me out. I think the beginning of my confusion is keying the date into excel.

When I enter the time (ie., 15:84) excel automatically associates a date with it .... or tries to. How do I format the column?

Do I enter a date, then the time in the same cell?

As you can see, I'm quite confused.

Thanks again, JAZ

15:84 is not a valid time in Excel. Are you wanting to convert time to decimals? If so, multiply by 24 and format cell as a number with two decimal places. 15.84 is approx 15:50

Edit:

Didn't see you Yogi, my apologies
 
Upvote 0
Hey No worries, Brian!

I am hoping that Jaz would actually enter the time based on my simulation -- that is why I mentioned Hours and Minutes separated by : -- so come time to actually making the entries, Jaz will say, Ah! but we don't talk about 84 minutes while talking about clock time in Hours and Minutes.

We all have to pick things up at our own pace!
 
Upvote 0
I finally understand what I was doing wrong and can see how this will work. I'm still not able to calculate the difference between ending and beginning hours though.

Using 15:84 as my start time, my clock calculates portions of an hour in 100th of a minute, so do I need to convert this to actual minutes (ie., x/60=84/100, which gives me 50.4 minutes) before I can calculate the elapsed time?

I used the MRound formula, but I didn't get anything close to the right time.

I'm really looking for something simple, so I don't want to have to enter the dates and hours, etc. I considered a very long IF statement to convert to normal time, then just subtract the time. Am I heading for the ozone yet?

JAZ
 
Upvote 0
Hi Jaz:

Let us go systematically -- once we have got the basics pat, it would be smooth sailing. As I mentioned in my posts as well as in my simulation(s), time is shown with hours and then a colon and then minutes, so since there are 60 minutes in an hour, we do not talk of time as being 15:84

Now if you hav time as 15 hour and 84/100 of an hour that is not shown as 15:84, but instead it will be shown as 15.84 hours -- Fair enough! Excel has its own lingo, and if we are going to work with Excel, the sooner we get past these nuances, the better.

So, if you are still with me,

tell me what is the StartTime
whether you tell it to me in Hours and Minutes, or in Hours and decimals of an hour, be clear about it and know the difference

and then tell me what is the QuitTime
again whether you tell it to me in Hours and Minutes, or in Hours and decimals of an hour, be clear about it and know the difference

Once we clearly know what is the QuitTime, and what is the StartTime, how long one actually worked is simply a matter of subtracting the StartTIme from the QuitTime

So, provide the requested information -- and then let us take it from there.
 
Upvote 0

Forum statistics

Threads
1,213,528
Messages
6,114,154
Members
448,553
Latest member
slaytonpa

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