how to simplify the long formula

hi, i am new here in this forum, i was just wondering if you could help me with my formula. what i am trying to do is to get the total overtime hours of employees for the whole month. here is the formula i used but actually this one is only for seven days
"=IF(AND(WEEKDAY($B$4)>=6,OR(C5>=8,C5<8)),C5,IF(C5<=8,0,(C5-8)))+IF(AND(WEEKDAY(E$4)>=6,OR(F5>=8,F5<8)),F5,IF(F5<=8,0,(F5-8)))+IF(AND(WEEKDAY(H$4)>=6,OR(I5>=8,I5<8)),I5,IF(I5<=8,0,(I5-8)))+IF(AND(WEEKDAY(K$4)>=6,OR(L5>=8,L5<8)),L5,IF(L5<=8,0,(L5-8)))+IF(AND(WEEKDAY(N$4)>=6,OR(O5>=8,O5<8)),O5,IF(O5<=8,0,(O5-8)))+IF(AND(WEEKDAY(Q$4)>=6,OR(R5>=8,R5<8)),R5,IF(R5<=8,0,(R5-8)))+IF(AND(WEEKDAY(T$4)>=6,OR(U5>=8,U5<8)),U5,IF(U5<=8,0,(U5-8)))"

i am considering fridays and saturdays as overtime and those exceeding 8 hours in weekdays.

any help is appreciated, thanks

Hi, and welcome to the board,

please refrain from starting a thread within another thread . See our forum rules. I therefore started a thread for you.

As for your problem maybe this site will help

Cheers

Why use OR(C5>=8,C5<8)?

This is always TRUE.

Your spreadsheet design means that any formulas you use will probably be more complex than necessary. It's much easier if you have one record per row, e.g. dates in A2:A32 and corresponding hours in B2:B32

Having said that......for your setup, assuming dates are every 3rd cell from B4 to CN4 try this formula

=SUM(IF(MOD(COLUMN(B4:CN4)-COLUMN(B4),3)=0,IF(WEEKDAY(B4:CN4)>5,C5:CO5,IF(C5:CO5>8,8-C5:C5))))

also confirmed with CTRL+SHIFT+ENTER

Note: to confirm with CTRL+SHIFT+ENTER select cell with formula, press F2 key, hold down CTRL and SHIFT keys and press ENTER so that curly braces like { and } appear around the formula in the formula bar

thanks for the formula youve given to me. after analyzing it and made some small changes, it works perfectly.

"=SUM(IF(MOD(COLUMN(B4:CP4)-COLUMN(B4),3)=0,IF(WEEKDAY(B4:CP4)>5,C5:CP5,IF(C5:CP5>8,C5:CP5-8))))"

again, thank you!

one more thing, what if one of the columns contain text value (example "leave",etc). i tried the same formula but returns with error.

please help