Calculate OT/TIL hours when 1st 2 hrs are double time, rest are 1:

[Corinne]

Hi,

I have an Excel sheet to keep track of overtime worked on programs
(converted to time in lieu) which is in desperate need of a formula or 2! I
hearby confess to using a calculator to help me with the spreadsheet - sorry
;-)

Any hours worked on Sunday equals double time (ie. 1 hour equals 2) and for
any other day the first 2 hrs are double, and after that it is 1 for 1.
So on a Sunday 2 hrs equates to 4 hours, on a Friday 2 hours equates to 3
hrs (and 3 is 5, and 4 is 7 etc etc).
So I think what I need is an 'if statement' to calculate the amount of extra
hours worked and what that equates to using the above formula - and taking
into consideration if it is Sunday.
At the end of each program I sum up the total calculated OT hours accrued
and any remaining from the last program so I have an ongoing total of OT/time
in lieu.
Our standard working day is 9 - 5 so we split the sheet out so we can see
what was worked in the morn and the eve.
My cell headings are
AM OT start / AM OT finish /PM OT start / PM OT finish /OT hours /Actual
hours/ Hours taken

Then after each program I have a summary row that totals the above columns
and a running total taking into account past programs.
Happy to restructure the sheet if anyone can suggest an easier way.
Hope this makes sense!
Any help appreciated...

Thanks,
Corinne.

 

[JLatham]

You haven't mentioned where the date or other day of the week indicator is
at, and that's crucial to determining if the workday is Sunday. Also, is
that information stored as a date or just as a word like "Sunday" or "Monday"
(or an actual date entry that's formatted to display a word like "Sunday" or
"Monday").

Also, you said that "Our standard working day..." Is there a possibility
that a person would work across midnight; i.e. they start at say 10 pm and
end a shift at 6 am?

 

[Joerg Mochikun]

There are lots of questions open, but let's give it a try:
Suppose that
A1 holds a valid date
B1 holds Time-In
C1 Holds Time-Out

You can calculate the gross number of hours in D1:
=IF((C1-B1)*24<0,(C1-B1)*24+24,(C1-B1)*24)
Above formula takes into account cases where Time-Out is past midnight.

To obtain the values you requested put following formula into E1:
=IF(OR(WEEKDAY(A1,2)=7,D1<2),D1*2,D1+2)

Example:
A1: 39950 (formatted as date it will result in May 17th,2009 - a Sunday)
B1: 9:00
C1: 12:00
D1: 3
E1: 6

Cheers,
Joerg Mochikun