Need help with DATEDIF

[worley3]

I have two columns that I have a report date (AL) and end date (BA). I am using DATEDIF to calculate the number of days in between.

I also want to use those days to convert to how many days of leave acquired. For instance, if the number of days is 1-6 they would acquire .5 days, 7-12 equals 1 day, 13-18 equals 1.5 days, 19-24 equals 2 days, etc.

Is there a way to do all of this within the same column?

 

[Dave Patton]

Try something like the following
You could build a lookup table.

T202006a.xlsm
	B	C	D
1	Report Date	End Date
2	01-06-20	10-06-20	1
4b

Cell Formulas
Range	Formula
D2	D2	=LOOKUP((C2-B2),{1,0.5;7,1;13,1.5;19,2})

T202006a.xlsm
	B	C	D
1	Report Date	End Date
2	01-06-20	10-06-20	1
3	01-06-20	10-06-20	1
4
4b

Cell Formulas
Range	Formula
D2	D2	=LOOKUP(C2-B2,{1,0.5;7,1;13,1.5;19,2})
D3	D3	=LOOKUP(DATEDIF(B3,C3,"d"),I2:J5)