Logging workhours and overtime!

Originally Posted by tailfeather

Hi!

I am trying to create a log (or report) for how many hours I work every month. I want it to automatically count how many hours I've worked and also when I work more than 6 hours for one day I want it to remove 45 min lunch.
This I have been able to do like following:

A9 = Monday
B9 = 1 (date)
C9 = Worked From (ex. 8:00)
D9 = Worked To (ex. 16:45)
E9 = =IF((((D9-C9)-INT(D9-C9))*24)<=6;(((D9-C9)-INT(D9-C9))*24);(((D9-C9)-INT(D9-C9))*24)-0.75)

BUT! This is the part I can't figure out. Fridays after 18:00 I have 75% extra pay all the way through the night until 7:00 in the morning.
Saturdays I have 50% extra pay until 14:00 from where I get 100% extra pay.
How should the formulas look like for the cells where I get extra pay?

For example if F9 is 50%, G9 is 75% and H9 is 100%.

Please help!
Thanks!

I don't like these sorts of problems. :-P They make me have to think too much.

My suggestion is for you to create a custom function in VBA to do this. It can be done with Excel functions, but it is going to be complicated and rather messy, whereas the Excel function can be made to look somewhat tidy. It would probably be sufficient to pass it the day of the week ("Monday"), the start time, and the end time.

From what you've said, the time periods with different pay rates are: Friday 7pm - midnight (75%), Saturday midnight - 7am (75%), Saturday 7am - 2pm (50%), Saturday 2pm - midnight (100%).

First, does anything happen on Sunday?

Second, where do you consider your 45 minute lunch to fall if there is some overlap? (ie. if you work Friday from 3pm to 11pm, is your lunch taken from the portion you earn your regular rate, or the portion you earn 75% more? I know which is most beneficial for you, but which way does your company do it?)

I can provide pointers for writing the function and critique what you have, but someone else will probably have to help you if you haven't done stuff like this before. (They'll need the answers to the above questions though)

Scott

Put the date in column A
Put the following into column B to get the day appropriate to that date.
=TEXT(A9,"dddd")

Put the start and end times into columnds C and D

Put the following into column E

=24*((D9-C9)-IF((D9-C9)<=TIME(6,0,0),0,TIME(0,45,0))+IF(AND(B9="Friday",D9>TIME(18,0,0)),(D9-TIME(18,0,0))*0.75))

I will leave it for you to add in the Saturday IFs

Mark.