Number of days off calculation

[kaz123]

Hello

I wanted help with calculating extra hour worked by employees and the resulting time off based on the month the hours were worked as it will be based on a different rules for some months.

I have the table/attachment below and wanted the time-off column populated as follows:

> If total hours worked by employees exceeds 16 hours then they get 2 days off regardless of when they worked those hours. (see John Smith)
> If total hours worked is less than 16 hours and they worked in Jan and Feb and/or April then the days off should be based on the latest rule (Feb March) - see Dev Patel
> if however, they done less then 16 hours and only worked extra hour in January then it will be based on Jan rule - see Steve Brown
> If they worked Feb and/or March, they get 1 day off if they worked 5 hours or more as the rule is different to Jan - see Abby Smith

Jan rule:
2hr to 5.5 hrs = 0.5 days
5.5hrs to 8hrs = 1 day
8hrs to 12hrs = 1.5 days
12hrs to 16hrs = 2 days


Feb and March rule
2hr to 5 hrs = 0.5 days
5hrs to 8hrs = 1 day
8hrs to 12hrs = 1.5 days
12hrs to 16hrs = 2 days

Thanks

 

[queuesmef]

I would probably create 12 Vlookup tables (one for each month, even if some months are the same). Then use vLookup formula on each of the 12 tables...you can use the month abbreviation to label each lookup table, so 'Jan' is one table with it's own criteria, 'Feb' etc. then use the formula

=VLOOKUP(A2,INDIRECT(B2),2,1)

 

[kaz123]

I would probably create 12 Vlookup tables (one for each month, even if some months are the same). Then use vLookup formula on each of the 12 tables...you can use the month abbreviation to label each lookup table, so 'Jan' is one table with it's own criteria, 'Feb' etc. then use the formula

=VLOOKUP(A2,INDIRECT(B2),2,1)

View attachment 48571

Hi, thanks for the response. I only two sets of rule as its same rule for Feb and March and there are only 3 months in total for my purpose. How could I get the data populated in column "Time off" based on your method?

*apologies - just seen your attachment - thanks, will have a look into your suggestion

 

[kaz123]

I would probably create 12 Vlookup tables (one for each month, even if some months are the same). Then use vLookup formula on each of the 12 tables...you can use the month abbreviation to label each lookup table, so 'Jan' is one table with it's own criteria, 'Feb' etc. then use the formula

=VLOOKUP(A2,INDIRECT(B2),2,1)

View attachment 48571

Hi, I still cannot get this to work and look like the how it appears in the attachment, the ExtraHrs in the look is hardcoded whereas what I have value between a range

 

[queuesmef]

You can create 2 lookup tables: maybe call one STANDARD and the other NONSTD. Then you'll replace INDIRECT(B2) with the name of the proper table (either STANDARD or NONSTD. Range names make it easier to have the ABSOLUTE Range of cells....you could always use the cell references and make them ABSOLUTE references with the F4 key. So for the Standard lookup use =vlookup(a2, STANDARD, 2, 1) or =VLOOKUP(A2,$I$1:$J$5,2,TRUE)

 

[kaz123]

You can create 2 lookup tables: maybe call one STANDARD and the other NONSTD. Then you'll replace INDIRECT(B2) with the name of the proper table (either STANDARD or NONSTD. Range names make it easier to have the ABSOLUTE Range of cells....you could always use the cell references and make them ABSOLUTE references with the F4 key. So for the Standard lookup use =vlookup(a2, STANDARD, 2, 1) or =VLOOKUP(A2,$I$1:$J$5,2,TRUE)

Thanks for your advice, will look into this.