Calulating Time Past for Pay Roll

[Josephine48]

I am trying to speed up the way I do the time cards for the lady at the local tea room where I work part time. I see that some time back Bill Jensen gave a formula for making this calculation but it is a bit confusing. This is what he wrote:

If the times are in cells A1 & B1 and you want the elapsed time in C1, follow these steps:
In cell C1, set up a custom number format of [h]:mm
Enter this formula in C1: =MAX(A1:B1)-MIN(A1:B1)

The square brackets around the h will insure that times over 24 hours are still reported as hours instead of days.
Also, the result of a date/time calculation can never be negative. I've used the Max and Min to make sure the earlier time is always subtracted from the later time. You could also use =ABS(B1-A1) or simply make sure that B1 will always be greater than A1.

That is fine but if I do this using military times it comes out like this:
<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=192 border=0><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right width=64 height=20>945</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right width=64>1330</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 48pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right width=64>385</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" align=right height=20>945</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>1330</TD><TD style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right>385</TD></TR></TBODY></TABLE>
So the time from 9:45 to 1:30 PM is 385 but how does that compute to hours and minutes. I know if I work it out in my head it like 3hrs. 45 mins. but I can't see that in the number 385. I really don't have the money to spend on a computer program to do this for me. All I am doing is just doing the calculations for her. This is a very small place and only employs like 8 or 9 people and no one works 40 hours a week.

Can you give me some insight into how to read this or is there a formula that tell me hours and minutes even if that number appears as just a series of numbers. I just need the key to understand it.

I am smart enough to get myself into a lot of work but I am hoping that I am also smart enough to be able to set up a formula that will do the brain teasers for me.

Thank you,

 

[barry houdini]

Hello Josephine48, welcome to MrExcel,

You need to enter times with a colon separating the hours and minutes, so that Excel recognises it as a time.

So in A1 you put 09:45, and in B1 13:30

then in C1 you use the formula

=B1-A1

and you should get the difference in time format, i.e. 3:45

 

[Josephine48]

You are so wonderful. I have been struggling with this for a couple of hours and thought that I would check on line to see if there was a chart or something and found Mr. Excel. What a great time saver you are.

Thanks so much!!!!!!!!!