SOLVED

formula for weekday and overtime

%3CLINGO-SUB%20id%3D%22lingo-sub-2619715%22%20slang%3D%22en-US%22%3Eformula%20for%20weekday%20and%20overtime%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2619715%22%20slang%3D%22en-US%22%3E%3CP%3Ehello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20help%20for%20a%20formula.%20I%20have%20been%20struggling%20with%20this.%20Grateful%20to%20have%20some%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20week%20starts%20on%20Monday.%20From%20Monday%20to%20Friday%20the%20working%20hours%20are%208hrs%20and%20on%20Saturdays%20it%20is%205%20hours.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20need%20to%20calculate%20the%20overtime%20based%20on%20the%20total%20hours%20per%20day(th)%20in%20cell.%26nbsp%3B%20The%20conditions%20are%20as%20follows.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3COL%3E%3CLI%3Eif%20the%20(th)cell%20is%200%3A00%2C%20then%20it%20must%20return%200%3A00%2C%3C%2FLI%3E%3CLI%3Eif%20it%20is%20weekday(mon%20to%20fri)%20it%20must%20deduct%208%20hours%20from%20the%20total%20hours%3C%2FLI%3E%3CLI%3Eif%20the%20total%20hours%20for%20the%20weekdays%20is%20less%20than%208hr%20or%208hrs%20it%20must%20return%200%3A00%20for%20the%20overtime%3C%2FLI%3E%3CLI%3Eif%20it%20is%20a%20Saturday%20%2C%205hrs%20must%20be%20deducted%20from%20the%20(th)cell%3C%2FLI%3E%3CLI%3Eif%20the%20(th)%20is%20less%20than%205hrs%20for%20saturday%2C%20it%20must%20return%200%3A00%20as%20overtime%3C%2FLI%3E%3CLI%3Eif%20it%20is%20a%20sunday%2C%20it%20must%20return%20same%20value%20hrs%20as%20in%20cell(th)%3C%2FLI%3E%3C%2FOL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20help.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2619715%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2619863%22%20slang%3D%22en-US%22%3ERe%3A%20formula%20for%20weekday%20and%20overtime%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2619863%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1122208%22%20target%3D%22_blank%22%3E%40ASP-01%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHere%20is%20an%20example%3A%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22S0651.png%22%20style%3D%22width%3A%20722px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F301072iF398B95234CA4763%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22S0651.png%22%20alt%3D%22S0651.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EThe%20formula%20in%20C2%20is%3C%2FP%3E%0A%3CP%3E%3DMAX(B2-CHOOSE(WEEKDAY(A2)%2C0%2C8%2F24%2C8%2F24%2C8%2F24.8%2F24%2C8%2F24%2C8%2F24%2C5%2F24)%2C0)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2623070%22%20slang%3D%22en-US%22%3ERe%3A%20formula%20for%20weekday%20and%20overtime%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2623070%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F127945%22%20target%3D%22_blank%22%3E%40Hans%20Vogelaar%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20so%20much!%20i%20was%20struggling%20with%20this%20for%20a%20long%20time.%26nbsp%3B%20Thank%20you%20so%20much%20for%20helping%20me%20out.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2623099%22%20slang%3D%22en-US%22%3ERe%3A%20formula%20for%20weekday%20and%20overtime%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2623099%22%20slang%3D%22en-US%22%3EHello%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20got%20a%20problem%20with%20the%20formula.%3CBR%20%2F%3Efor%20wednesday%20it%20is%20sunbstrating%2020%20minutes%20instead%20of%208%20hrs.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20tried%20to%20remove%20the%20dot%20in%20the%20formula%20and%20replace%20it%20by%20a%20comma%2C%20then%20it%20substracts%20the%208%20hours.%20However%2C%20then%20for%20saturday%20it%20shows%200%3A00%20hrs%20even%20when%20there%20is%20overtime.%3CBR%20%2F%3E%3CBR%20%2F%3ECan%20you%20please%20help%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2623234%22%20slang%3D%22en-US%22%3ERe%3A%20formula%20for%20weekday%20and%20overtime%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2623234%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1122208%22%20target%3D%22_blank%22%3E%40ASP-01%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EOh%20dear%2C%20what%20a%20stupid%20typo.%20Sorry!%20It%20should%20have%20been%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%3DMAX(B2-CHOOSE(WEEKDAY(A2)%2C0%2C8%2F24%2C8%2F24%2C8%2F24%2C8%2F24%2C8%2F24%2C5%2F24)%2C0)%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

hello,

 

I need help for a formula. I have been struggling with this. Grateful to have some help.

 

The week starts on Monday. From Monday to Friday the working hours are 8hrs and on Saturdays it is 5 hours.

 

I need to calculate the overtime based on the total hours per day(th) in cell.  The conditions are as follows.

 

  1. if the (th)cell is 0:00, then it must return 0:00,
  2. if it is weekday(mon to fri) it must deduct 8 hours from the total hours
  3. if the total hours for the weekdays is less than 8hr or 8hrs it must return 0:00 for the overtime
  4. if it is a Saturday , 5hrs must be deducted from the (th)cell
  5. if the (th) is less than 5hrs for saturday, it must return 0:00 as overtime
  6. if it is a sunday, it must return same value hrs as in cell(th)

 

Please help.

5 Replies

@ASP-01 

Here is an example:

S0651.png

The formula in C2 is

=MAX(B2-CHOOSE(WEEKDAY(A2),0,8/24,8/24,8/24.8/24,8/24,8/24,5/24),0)

best response confirmed by ASP-01 (New Contributor)
Solution

@Hans Vogelaar 

 

Thank you so so much! i was struggling with this for a long time.  Thank you so much for helping me out.

Hello,

I have got a problem with the formula.
for wednesday it is sunbstrating 20 minutes instead of 8 hrs.

I have tried to remove the dot in the formula and replace it by a comma, then it substracts the 8 hours. However, then for saturday it shows 0:00 hrs even when there is overtime.

Can you please help

@ASP-01 

Oh dear, what a stupid typo. Sorry! It should have been

 

=MAX(B2-CHOOSE(WEEKDAY(A2),0,8/24,8/24,8/24,8/24,8/24,5/24),0)

Thank you, really worked well.