Calculating Overtime hours in one cell - is it possible

Hi there

I'm new to the forum and hope the brain's trust can put me right. Needs must! I've tried Countifs, sumifs, productifs...I basically don't have the right syntax...

I am trying to have excel calculate overtime hours as numbers are inputted in other cells

It's easy enough where we have technicians working on a weekend - value is multiplied by 1.5 and shown in AK

eg: =SUM(V3,AB3:AC3,AI3)*1.5

However....

What I would also like to do in column AK then every 18 columns in the range is have a calculation take place that looks at the weekday hrs. Where the value is over 8 but under 10 then column AK will add the overtime amount of 0.5 and where the value is greater than 10 will do double time

In other words

Where Mon to Fri is > 8 =< 10 then 2 hrs should be mulitplied by 0.50
eg: 9 hours = 9.5hrs so AK increments 0.5
Where Mon to Fri is >10 then hours first two hours over 8 are multiplied by 0.5 and then multiplied by 2
eg: 12 hours = 15hrs so AK increments by 3

How on earth do I do this and also ensure my existing calculation for weekends is not affected.

Hoping it's possible or other suggestions would be most appreciated

B

Hi and welcome to the forum

I think I have what you want.

1st, instead or using text for the weekdays (row 2) use this, copied across...
=WEEKDAY(V1,1)
format custom as ddd this will give you a weekday for each day

next, you have 2 options.
OPTION 1
this will split the OT calc into 3 parts - weekday OT <10, W/D OT > 10, W/E OT
add 3 helper columns before your current OT column (AK)
in the 1st column, calc OT <10, use this, copied down
=(SUMIFS(V3:AI3,V1:AI1,">1",V1:AI1,"<7",V3:AI3,">8",V3:AI3,"<10")-(COUNTIFS(V1:AI1,">1",V1:AI1,"<7",V3:AI3,">8",V3:AI3,"<10")*8))+(SUMIFS(V3:AI3,V1:AI1,"<6",V3:AI3,">=10")-(COUNTIFS(V1:AI1,"<6",V3:AI3,">=10")*10))*1.5
in the 2nd column, calc OT >10, use this, copied down
=(SUMIFS(V3:AI3,V1:AI1,"<6",V3:AI3,">=10")-(COUNTIFS(V1:AI1,"<6",V3:AI3,">=10")*10))*2
in the 2rd column, calc WE OT, use this, copied down
=(SUMIFS(V3:AI3,V$2:AI$2,1)+SUMIFS(V3:AI3,V$2:AI$2,7))*1.5
in your current OT column (now AN), add them together
=SUM(AK3:AM3)

OPTION 2
dont use helper columns and have all of the above combined into 1 cell (which is what you want, I think, but it will be much harder to edit or modify later)
=(SUMIFS(V3:AI3,V1:AI1,">1",V1:AI1,"<7",V3:AI3,">8",V3:AI3,"<10")-(COUNTIFS(V1:AI1,">1",V1:AI1,"<7",V3:AI3,">8",V3:AI3,"<10")*8))+(SUMIFS(V3:AI3,V1:AI1,"<6",V3:AI3,">=10")-(COUNTIFS(V1:AI1,"<6",V3:AI3,">=10")*10))*1.5
+(SUMIFS(V3:AI3,V1:AI1,"<6",V3:AI3,">=10")-(COUNTIFS(V1:AI1,"<6",V3:AI3,">=10")*10))*2
+(SUMIFS(V3:AI3,V$2:AI$2,1)+SUMIFS(V3:AI3,V$2:AI$2,7))*1.5

I have a feeling that later you will also want to split W/E OT >10, OPTION 1 will make doing this adjustment easier

I used a single array formula (no helper cells) to get the total overtime, i.e. this formula for AN3

=SUM(IF(Y$2:AL$2=1,Y3:AL3*2,IF(Y$2:AL$2=7,Y3:AL3*1.5,CHOOSE(MATCH(Y3:AL3,{0,9,10}),0,0.5,Y3:AL3-9))))

confirmed with CTRL+SHIFT+ENTER

That calculates Sundays @ time *2, Saturdays @ time * 1.5 and weekdays as per your description - that gives me 54.5 for your example

Hey

Definitely an elegant formula. You guys rock! Can you confirm this takes into account the >8<10hrs @ time and half and >10 @ double time for weekdays? I calculate the Weekend hours and Weekday OT at 55hrs and not 54.5. Can you advise where the rounding up, if that is the problem, occurs

If I take Sundays as being double time and Saturdays as time and a half I calculate there should only be in total 138 hrs which is 83 normal hours + 55 as OT. Reattached file for anyone interested but looking purely at the new formula

If I can account for that 30 minutes (is it me or is it you??) then I can finally put this to bed!!

Hi Baiona

Good idea. Best to keep things simple just in case someone else has to work on it.

OK, going with that simpler approach try these formulas:

In AB23 copied across for normal hours

=IF(OR(AB21={"Sat","Sun"}),0,MIN(8,N(AB22)))

then in AB25 copied across for double time hours

=MAX(0,N(AB22)-IF(AB21="Sun",0,IF(AB21="Sat",8,10)))

and finally in AB24 the formula can give you all the remaining hours at "time and a half"

=N(AB22)-AB23-AB25

Using N(AB22) will return the number from AB22 if it contains a number........but zero if it contains text - so the formulas cope with text like "sick" or "hols"

OK, these get pretty big, so test them well...
in AO use this...

Please Login or Register  to view this content.

in AP use this....

Please Login or Register  to view this content.

and in AQ use this...

Please Login or Register  to view this content.

let me know how you make out please?

Happy to help and glad we arrived at what you wanted

Thanks for the rep, too

Hi again

Sorry, I gotta get my head into this again lol Just tto confirm, we are still using these rules, right?

Where Mon to Fri is > 8 < 10 then 8 normal hours and up to 2 hrs in "*1.5" cell
Where Mon to Fri >10 hrs then 8 normal hours, 2 in "*1.5" then rest in "*2"
Where Saturday hours first 8 hours in "*1.5" then balance in "*2"
Where Sunday all hours in "*2"

in other words
W/D to 8 = regular time
W/D to 10 = 2hr @ 1.5
W/D to 12 = 2hr @ 1.5 + 2hr @ 2
Sat to 8 = 1.5
Sat past 8 = @ 2
all Sun = @ 2

so Sat 11 hrs =
8 hrs @ 1.5
3 hrs @ 2

Ok thanks, just wanted to make sure.

I will work on it and get back to you (its getting a bit late here now, so I will probably take a look inthe morning when im fresh)

edit: test these, they seem to work according to your rules now.
for the 1.5 column...

Please Login or Register  to view this content.

for the 2 x column...

Please Login or Register  to view this content.

Ok, I think I have it this time...

for the 1.5....

Please Login or Register  to view this content.

Happy to help, look forward to your next question