Timesheet Day hours Night hours Nght Rates Overtime rates

This is amazing. I just need few changes. The overtime starts after 8 hours from starting time.
I am so sorry, I should have mentioned it in the first post. I was trying to make the changes myself but can't figure it out sorry.
And Saturdays and Sundays are flat rate. There is no difference between day-night-or overtime, but I don't think it makes any difference. It is good to have it in there
for the future. Thank you so much for your hard work and effort. If you could sort out the O/T after 8 Hours that would be great. I hope there won't be too much to change
Also I just realized, that the hours come from Aux1 will I have to copy it for every following week? I have all 52 weeks of the year in one sheet, I only posted four as an example, I thought I could just copy and paste
once I have the formulas done, but it looks like I would have to adjust it for each new week.
Thank you so much.

Oooh, I got it now. I got confused. because it showed 2 reg hours and 13.75 night and 3.75 overtime. I thought it was adding it altogether, but now I see in the O/T formula subtracts from 13,75 8 and only the rest multiplies by O/T rate.
I just noticed that overtime is not calculated properly. If you look on 3/1/2018 for example I worked 14 hours. Started at 23:00 so it should give 7 night + 1 day hour + 6day Overtime but nothing comes up.
Same for night O/T if the case is that the overtime falls on the night. Could you fix this please?

Thank you

Hello, viper8283.

I have managed to build a volatile formula with COUNTIFS and OFFSET functions to accurately and consistently count the first eight hours worked and tell Day from Night hours.
Then I do the same for the overtime hours.
All this calculation is in helper columns in Aux2 tab.

Again, please run some test and let us know how it goes.

Hi there,
sorry I didn't reply earlier. I have been very busy since the last time. I have worked on the timesheet today and it seems to work fine except it returns a negative value on one of the days.
I attached my actual timesheet so you have better idea and to see exactly where it happens. It is on TBC-2018 sheet at position "Y-38". You'll see it. Also, if you put work on Sunday the Day O/T rate for example at Q-25
returns error, All other days work fine. Other then that seem to be working perfect so far.
Thank you again for your help and time. It is amazing what you have done with it

Hi, viper! You're welcome!

I see that I forgot to change formulas for regular hours pay. Old ones were based on my first model.
Sorry about that.

Since you changed the structure of your sheet, I made these formulas simpler:

Please paste in R25:
=IF(N25="","",N25*O25)

And Y25:
=IF(U25="","",U25*V25)

Then copy down.

That will take care of that erroneous negative value.

For work on Sundays, the error cause must be in column C. The LOOKUP function seeks a client there, so please check if it is Day off instead of a client name from the range A2:A18.

Anyway, I changed the LOOKUP range in the formula, for O25

=IF($A25="Day",$O$24,IF($N25="","",VLOOKUP($C25,$A$2:$N$18,IF(WEEKDAY($B25)=7,9,IF(WEEKDAY($B25)=1,10,5)),0)))

And Q25:

=IF($A25="Day",$Q$24,IF($P25="","",VLOOKUP($C25,$A$2:$N$18,IF(WEEKDAY($B25)=7,11,IF(WEEKDAY($B25)=1,13,6)),0)))

Then copy down.

There will be errors though, if, for example, client Clipper is selected for Sunday. There is no value for Clipper in Sunday column.
The same for other clients depending on column selected.

Please check that if an error comes.


Godspeed!

Hello, Viper!
I'm glad we are getting there!
I made a few adjustments in formulas from column J thru Z, just removed some conditions that are no longer required, and it works with Sunday and Saturday clients.
Please find attachment.
I use winrar to compress files, but it's not compatible here, so I deleted rows in auxiliary tabs. Please copy them back down to fit your rows in front tab.

Take care!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

You, too. OT, but I was born in Derby.