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

 

[Snakehips]

@rockson See if the below helps.
I have typically used ISNA(MATCH(A28,Prazniki!$B$3:$B$20,0)) yo determine if date is on the holiday list or not.
Substitute my text strings with your required formula calls.

Picking%20Teams (version 1).xlsb
	A	B	C	D	E	F	G	H
28	4/1/24					Weekend or Holiday	Weekend or Holiday
29
30	4/2/24					Normal Working	Normal Working
31
32	4/3/24					Normal Working	Normal Working
33
34	4/4/24					Normal Working	Normal Working
35
36	4/5/24					Normal Working	Normal Working
37
38	4/6/24					Weekend or Holiday	Weekend or Holiday
39
40	4/7/24					Weekend or Holiday	Weekend or Holiday
41
42	4/8/24					Normal Working	Normal Working
43
44	4/9/24					Normal Working	Normal Working
45
46	4/10/24					Normal Working	Normal Working
47
48	4/11/24					Normal Working	Normal Working
49
Sheet5

Cell Formulas
Range	Formula
F28,F30,F32,F34,F36,F38,F40,F42,F44,F46,F48	F28	=IF(OR(WEEKDAY(A28,2)>5,NOT(ISNA(MATCH(A28,Prazniki!$B$3:$B$20,0)))),"Weekend or Holiday","Normal Working")
G28,G30,G32,G34,G36,G38,G40,G42,G44,G46,G48	G28	=IF(AND(WEEKDAY(A28,2)<6,ISNA(MATCH(A28,Prazniki!$B$3:$B$20,0))),"Normal Working","Weekend or Holiday")

 

[rockson]

@Snakehips Thank you for your help.
Your strings do work as they are, but how do I additional insert:
F column --> (E28-$M$3-G28-H28) instead of "Normal Working"? I tried --> =IF(OR(WEEKDAY(A28;2)>5;NOT(ISNA(MATCH(A28:Prazniki!$B$3:$B$20;0))));0;($E28-$M$3-G28-H28)) which always returned empty cell, no matter whether weekdays or weekend/holiday.

G column --> (E28>8;E28-$M$3) instead of "Normal Working"? I tried --> =IF(AND(WEEKDAY(A28;2)<6;ISNA(MATCH(A28;Prazniki!$B$3:$B$20;0)))+IF(E28>8;E28-$M$3;0)*E28-$M$3;0) which always returned 0 no matter whether weekdays or weekend/holiday.

 

[Snakehips]

I'm struggling to see your intended logic with G but try below with F and see if it helps.

 =IF(OR(WEEKDAY(A28;2)>5;NOT(ISNA(MATCH(A28,Prazniki!$B$3:$B$20,0))));(E28-$M$3-G28-H28);0)

 

[rockson]

I'm struggling to see your intended logic with G but try below with F and see if it helps.

 =IF(OR(WEEKDAY(A28;2)>5;NOT(ISNA(MATCH(A28,Prazniki!$B$3:$B$20,0))));(E28-$M$3-G28-H28);0)

This string for F works, just had to invert data for true and false. Thanks a lot.

About G: Apart from defining if it is a workday or not, I have to check, if the value in E is greater than 8 (more than 8 working hours). If it is, then the string should return value (E28-$M$3) --> M3 being the value of 8 working hours. It could also be (E28-8), but if the normal working hours get changed, than it is easier for me, to change only value in M3, rather than in the string. If the value in E28 is smaller or equal 8, than the cell should stay empty or 0.

I hope it helps.

 

[Snakehips]

Try.

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

 

[rockson]

Try.

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

It works Thx a lot.

Now I have just one additional question, regarding column H. This column is similar to G, but here I need the value from F, if it is weekend/holiday. I tried to adapt your string for G and used

=IF(AND(WEEKDAY(A50;2)<6;ISNA(MATCH(A50;Prazniki!$B$3:$B$20;0));E50>8);E50-$M$3;0)

And it works just fine as long the value in F is greater than 8. If it is smaller or equal 8, than it returnes the value from F even if it is a workday. Example from my sheet:

Weekend (blue) is ok, as is row 58, as the value in F is greater than 8. All other F values are smaller than 8.
If it is a workday, the value in H should always be 0, if it is weekend/holiday, than the value from E should be entered.

I am really sorry to bother you so much

 

[Snakehips]

I'm confused. Is E being > 8 relevant in H formula?
Does the below statement fully describe the logic for H ?

If it is a workday, the value in H should always be 0, if it is weekend/holiday, than the value from E should be entered.

 

[rockson]

I'm confused. Is E being > 8 relevant in H formula?
Does the below statement fully describe the logic for H ?

That statement is accurate. That is why I am also confused, why I get a value for workday returned, if the value in E is lower or equal to 8. If if is greater than 8. than I get no value entered, which is correct, but can+t figre out, why this is not the case if the value in E ist 8 or lower.

 

[Snakehips]

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)