Employer's odd time statement

Jakeyboy

New Member
Joined
Feb 16, 2002
Messages
6
Below is a portion of my employer's time statement. As you can see, the idea is to round up the total to 37.00 hrs for the week. But how on earth do they end up with 7.40 hrs, when the start and end times calculate to a different time in reality? Can anyone figure out the formula, or are they just entering these figures manually!

Planned Time


Day Date From To Hours

Sunday 20/01/2002 00:00 00:00 0.00
Monday 21/01/2002 08:30 16:54 7.40
Tuesday 22/01/2002 08:30 16:54 7.40
Wednesd 23/01/2002 08:30 16:54 7.40
Thursda 24/01/2002 08:30 16:54 7.40
Friday 25/01/2002 08:30 16:54 7.40
Saturda 26/01/2002 00:00 00:00 0.00

Cheers
Jake
This message was edited by Jakeyboy on 2002-02-18 02:25
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
On 2002-02-18 02:23, Jakeyboy wrote:
Below is a portion of my employer's time statement. As you can see, the idea is to round up the total to 37.00 hrs for the week. But how on earth do they end up with 7.40 hrs, when the start and end times calculate to a different time in reality? Can anyone figure out the formula, or are they just entering these figures manually!

Planned Time


Day Date From To Hours

Sunday 20/01/2002 00:00 00:00 0.00
Monday 21/01/2002 08:30 16:54 7.40
Tuesday 22/01/2002 08:30 16:54 7.40
Wednesd 23/01/2002 08:30 16:54 7.40
Thursda 24/01/2002 08:30 16:54 7.40
Friday 25/01/2002 08:30 16:54 7.40
Saturda 26/01/2002 00:00 00:00 0.00

Cheers
Jake
This message was edited by Jakeyboy on 2002-02-18 02:25

I think they use "a formula" like:

=((D4-C4)*24)-1

where C4 is the start time,D4 the end time, and -1 represents the total of breaks (lunch, coffie, etc).

Enter this formula in column F and format its cell as General, then copy down.
 
Upvote 0
Jake,

Lets say that A1:E8 houses your sample data:

{"Day","Date","From","To","Hours";
"Sunday","20/01/2002",0,0,0;
"Monday","21/01/2002",0.354166666666667,0.704166666666667,7.4;
"Tuesday","22/01/2002",0.354166666666667,0.704166666666667,7.4;
"Wednesday","23/01/2002",0.354166666666667,0.704166666666667,7.4;
"Thursday","24/01/2002",0.354166666666667,0.704166666666667,7.4;
"Friday","25/01/2002",0.354166666666667,0.704166666666667,7.4;
"Saturday","26/01/2002",0,0,0}

The strange looking numbers are times in the internal representation that Excel works with.

Start and end times are formatted as Time with 13:30 as type.

In E2 enter: =IF(C2,((D2-C2)*24)-1,0)

Format E2 as General and copy down as far as needed.

I expanded the formula because Sunday and Saturday entries which are zero (You have apparently applied the shorter version on the zero entries, which will result in -1). The expanded version will produce the intended result also wrt zero entries.

If intersested in a WB that shows the workings of the formula, just drop me a line.

Aladin
 
Upvote 0
I don't see what the big problem is.

You start at 8:30 am, and work till 4:54 pm.
That is a total of 8.40 hours. I guess u take an hour long lunch (unpaid), subtracting which gives you 7.4 hours!!

Hence you work 7 hours and 24 minutes per day, which totals to exactly 37.00 hours per week!!
 
Upvote 0
Anonymous,

The portion of the statement is cosmetic, as I am salaried. The times are not true, as we don't get a lunch break as such. They've just put these hours there to reflect the "normal" 37 hr week that we get paid for. As you rightly state, the times add up to 8.40, if we had an unpaid lunch break it would be correct, but we don't, and the query was really just one of curiosity as to how they came up with those figures. I'll ask payroll, but I doubt if even they know. It's not important anyhow. For your information, I don't work those hours at all - I work nightshift, normally from 6:45pm to 08:30am!!!!
This message was edited by Jakeyboy on 2002-02-18 09:47
 
Upvote 0

Forum statistics

Threads
1,213,486
Messages
6,113,932
Members
448,533
Latest member
thietbibeboiwasaco

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