1

I'm trying to create a timesheet where I can calculate the normal hours, overtime and weekend hours.

Here is the sheet I'm working on: https://docs.google.com/spreadsheets/d/1S4l2txvsjLQnWC4O5D3C0wj5kJWoeocNP7y4f2YOLcg/edit?usp=sharing

So far I managed to get the list of dates calculate hours worked and which is overtime, but what I can't figure out is how to calculate the worked hours on weekends, because I have 3 rates to pay (for normal hours, overtime and work on weekends).

I could to that if I put like a check box next to each date and manually select the weekends, but I wonder if I could do it automatically.

Improve this question

1 Answer 1

Reset to default
3

You can use WEEKDAY to find weekends. With default settings, it returns one for Sunday and seven for Saturday. Further using IF and OR you can process the data as you need. This formula calculates the number of hours worked:

=IF(OR(WEEKDAY(A11)=7,WEEKDAY(A11)=1),D11,0)

enter image description here

Improve this answer
2
  • First, thank you very much for the answer it helped me a lot, but i need just a small help with this answer, the formula is great but how can i make it that if A11 is empty then the cell with this formula to be blank as well. On Hours and Overtime i used the IFS function like =IFS(A11="", "", G11>0,0, G11=0, MAX(D11-8,0)) but not sure how to use this on your formula
    – John
    Commented Aug 27, 2021 at 14:59
  • I am glad to help. =IF(A11="", "", IF(OR(WEEKDAY(A11)=7,WEEKDAY(A11)=1),D11,0))
    – Boris Baublys
    Commented Aug 28, 2021 at 8:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.