Employer's odd time statement

[Jakeyboy]

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 ]

[Aladin Akyurek]

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.

[Jakeyboy]

Aladin,

I tried something similar but it doesn't work. What returns is simply -1!?

[Aladin Akyurek]

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

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!!

[Jakeyboy]

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 ]