Overtime based on weekends AND holidays

[rockson]

Hi guys,
I am trying to create a time sheet for my wife in which there are 2 columns, where I struggle to get the right data when there is working time on holiday.
My sheet looks like this:

A - date (1.4.2024)
B - working time from
C - working time till
D - working time sum
E - total working time sum
F - difference to 8hrs workday, taking into consideration all overtime, but only when monday to friday (without weekend AND holidays)--> formula used, which works fine for workdays and weekends: =IF(WEEKDAY(A28;2)>5;0;($E28-$M$3-G28-H28)). Here I would need additional check, whether the date is a holiday or not (1.4.2024 was easter). If weekend OR holiday, than this cell should be empty or zero.
G - overtime weekdays - formula used: =IF(WEEKDAY(A28;2)<6;IF(E28>8;E28-$M$3;0);0) --> works fine, when there is no holiday. Here I would need additional check, whether the date is a holiday or not. If weekend OR holiday, than this cell should be empty or zero.
H - overtime weekends/holidays - formula used: =IF(WEEKDAY(A28;2)>5;E28;0) --> works fine for weekends. Here I would need additional check, whether the date is a holiday or not. If weekend OR holiday, than this cell should get the value from E column.

I have separate sheet for all holidays, called "Prazniki":

I hope you can help me, cause I have been at this for hours.
Thank you

 

[rockson]

Based purely on that statement, I take it that the value of E compared to 8 has no relevance in H. Also reference you made in post#7 about F compared to 8 can be ignored.
Hence value in H is just dependant upon whether it is a weekday so = 0 or w non weekday so = value in E

So, try....

=IF(AND(WEEKDAY(A28;2)<6;ISNA(MATCH(A28;Prazniki!$B$3:$B$20;0));E28);0;E28)

@Snakehips Thank you so much, it works perfectly
Is there any way I can repay your effort?

 

[Snakehips]

@Snakehips Thank you so much, it works perfectly

Great stuff, we got there in the end!

Is there any way I can repay your effort?

In theory, we Forum Members all help for the satisfaction of assisting others. Maybe even educating them along the way, and / or learning something new ourselves. In general, we enjoy the challenge and repayment is not expected.
However, in your case, given that you thoroughly confused me with your E's, F's, 8's and transposed double IF's statements and caused me to miss watching large chunks of the weekend's top football on TV, I think you should pay! So please private message me with your full bank account details and mother's maiden name and we'll call it quits!

Seriously, you are welcome. Maybe if there is a next time, download XL2BB from the link above and use it to post a snapshot of the relevant part of your worksheet That will greatly assist any helper.
Then clearly define your needs and logic as best you can.
I wish you well with the rest of your project.

 

[rockson]

Hahaha, joker
If you would say something about Paypal, I would buy it

I tried to get XL2BB, but as I was working on my corporate laptop, there was no way to install it, so that is why I needed to get creative and try to give you as good infos as possible. I guess I failed, but will try harder next time.

I really appreciate your effort and help, so I can't thank you enough.

Have a great week
Rok