Forum Discussion
Using Datedif within Countif
I'm trying to create a formula that will run a datedif to give a day count and then to compare that day count to a listing of dates for the purposes of calculating an estimated accrual of PTO for work. I've attached the workbook I'm working in. Column R on the active page, specifically.
Formula needs to do the following:
1) Datedif to get the number of days between the hire date and the pay period date.
2) Countif to compare that number to the lower threshold and upper threshold day counts to select the correct multiplier.
3) Determine if the number of hours in that pay period is greater than the maximum amount allowed per pay period for accrual (80 hours in this case)
4) Multiply the number of hours worked by the correct multiplier to determine the accrual for that pay period.
The problem with the formula I am currently using is that it uses the total tenure only with no regard to the pay period, rather than basing it on the tenure for that pay period. It works, but I'm having to copy and paste values after calculation, and I would much rather have it just stay having the formula instead.
Jonathan, was not able to load your second file as well.
As a comment, bit shorter variant of your formula is
=IF((Tenure!$H$8-$A2>=$U$2)*(Tenure!$H$8-$A2<=$V$2),$X$2*MAX($C2,80),…
Hi Jonathan,
It looks like uploaded file is corrupted
That's odd. Let me try and upload it again.
I was able to accomplish the necessary result by eliminating the countif function and using a regular if/then statement with the datedif function as my logical expression. Looks something like "=if(and(datedif($a2,Tenure!$H$8,"D")>=$U$2,datedif($a2,Tenure!$H$8,"D")<=$V$2),if$C2>80,80*$X$2,$C2*$X$2,..." and repeats throughout the whole of the chart.
Jonathan, was not able to load your second file as well.
As a comment, bit shorter variant of your formula is
=IF((Tenure!$H$8-$A2>=$U$2)*(Tenure!$H$8-$A2<=$V$2),$X$2*MAX($C2,80),…
