Timesheet - Sumproduct, ignoring text and auto calculating lunch time

[MrAdelphi02]

I have been racking my brain trying to come up with a solution to this but to no avail, so hopefully someone here can help me out.

Open	Close	Open	Close	Open	Close
8:00 AM	4:00 PM	6:00 PM	12:30 AM	Time Off

So the current formulas I have created are:

Formula 1:
=24*SUMPRODUCT(MOD(COLUMN(A2:F2),2)*(B2:F2-A2:E2+(B2:F2<A2:E2)-((B2:F2-A2:C2+(B2:F2<A2:C2))>=6.5/24)*0.5/24)))

This formula calculates the time worked and reduces by 30 mins if they work longer than 6.5 hours. It also takes into account anyone to works past midnight. But it does not work if non-text is used.

Formula 2:
=24*SUMPRODUCT(--(MOD(COLUMN(A2:F2)-COLUMN(A2)+1,2)=0)-(MOD(COLUMN(A2:F2)-COLUMN(A2)+1,2)=1),A2:F2)

This formula calculates the time worked and takes into account anyone who works past midnight and works if non-text is used, but I cannot make it reduce the time by 30 mins if they work longer than 6.5 hours in a day.

Can anyone help?

 

[J.Ty.]

HI,

Welcome to the Board!

You are using SUMPRODUCT - does it mean that the row can be arbitrarily long, with very many OPEN-CLOSE pairs?
Or is it like in your example, with 3 pairs and that's it?
In the latter case, a long by simple formula
=IFERROR(IF(D2<C2,D2+1-C2,D2-C2),0)+IFERROR(IF(B2<A2,B2+1-A2,B2-A2),0)+IFERROR(IF(F2<E2,F2+1-E2,F2-E2),0)-IF(IFERROR(IF(D2<C2,D2+1-C2,D2-C2),0)+IFERROR(IF(B2<A2,B2+1-A2,B2-A2),0)+IFERROR(IF(F2<E2,F2+1-E2,F2-E2),0)>6.5/24,0.5/24,0)
seems to do the job.

J.Ty.

 

[MrAdelphi02]

Thank you for your reply. The above example is only for 3 pairs, but I am looking for a formula that works for 7 pairs (7 days).

Adelphi

 

[J.Ty.]

OK, I will prepare something for the larger case, but only much later today.

J.Ty.

 

[J.Ty.]

Does this rule reduces by 30 mins if they work longer than 6.5 hours apply to each day separately, or to their sum?

J.Ty.

 

[J.Ty.]

I did a formula which assumes that the lunch rule applies to each day separately. Should work for any number of days. Please test it carefully.
=SUMPRODUCT(IFERROR((B1:F1)-(A1:E1)+((B1:F1)<(A1:E1))-(((B1:F1)-(A1:E1)+((B1:F1)<(A1:E1)))>(6.5/24))*(0.5/24),0)*(MOD(COLUMN(B1:F1),2)=0))

 

[MrAdelphi02]

I did a formula which assumes that the lunch rule applies to each day separately. Should work for any number of days. Please test it carefully.
=SUMPRODUCT(IFERROR((B1:F1)-(A1:E1)+((B1:F1)<(A1:E1))-(((B1:F1)-(A1:E1)+((B1:F1)<(A1:E1)))>(6.5/24))*(0.5/24),0)*(MOD(COLUMN(B1:F1),2)=0))

You genius!!!

that worked perfectly!!!!!!!!

Thank you so much.

 

[J.Ty.]

Thanks for the feedback and good luck!

J.Ty.