Help with dates for calculating proration and accruals

[Sirod]

Hello,

I'm working on a table that calculates prorated vacation. My formula is =IF(Q2>(EDATE($AP$1,-7)-1),DATEDIF(Q2,$AP$1+15,"M"),7). Q2 is the start date and $AP$1 is the start of the calendar year (Jan 1, 2024).

The formula works great providing that the start date is before the start of the calendar year. Where I'm running into challenges is when the start date is after Jan 1, 2024, i.e. Feb 28, 2024. If the start date is after the start of the calendar year, the returned value should be zero, and if the start date is prior to the start of the calendar year, the existing formula should kick in. Please assist me in revising the above formula to account for start dates after the start of the calendar year. Thanks.

 

[bobaol]

try this formula in one cell, it accounts for ISERROR =IF(ISERROR(DATEDIF(Q2,$AP$1+15,"M")),0,IF(Q2>(EDATE($AP$1,-7)-1),DATEDIF(Q2,$AP$1+15,"M"),7)) or you can try these formulas in 3 different cells... in cell BA2 =IF(Q2>(EDATE($AP$1,-7)-1),DATEDIF(Q2,$AP$1+15,"M"),7) in cell BB2 =ISERROR(BA2) result in cell BC2 =IF(BB2=TRUE,0,BA2) While this works, it would be easier to find the problem by breaking it down as much as possible using the available space you have to the right. Take for example below. hope this helps. Cheers!

''cell BA1
Start Date	Beginning of year	Beginning of year minus 7 months	result1	result1 > start date	To use for Datedif
			result minus one day		Start Date	Beginning of year	Plus 15	Days Difference	Approx Months	Exact Months1	Exact Months2
45213	45292	=EDATE(BB13,-7)	=BC13-1	=BD13>BC13	=BA13	=BB13	=BG13+15	=BH13-BF13	=BI13/30	=DATEDIF(BA13,BB13+15,"M")	=IF(ISERROR(BK13),0,BK13)
45229	45292	=EDATE(BB14,-7)	=BC14-1	=BD14>BC14	=BA14	=BB14	=BG14+15	=BH14-BF14	=BI14/30	=DATEDIF(BA14,BB14+15,"M")	=IF(ISERROR(BK14),0,BK14)
45081	45292	=EDATE(BB15,-7)	=BC15-1	=BD15>BC15	=BA15	=BB15	=BG15+15	=BH15-BF15	=BI15/30	=DATEDIF(BA15,BB15+15,"M")	=IF(ISERROR(BK15),0,BK15)
45351	45292	=EDATE(BB16,-7)	=BC16-1	=BD16>BC16	=BA16	=BB16	=BG16+15	=BH16-BF16	=BI16/30	=DATEDIF(BA16,BB16+15,"M")	=IF(ISERROR(BK16),0,BK16)
45350	45292	=EDATE(BB17,-7)	=BC17-1	=BD17>BC17	=BA17	=BB17	=BG17+15	=BH17-BF17	=BI17/30	=DATEDIF(BA17,BB17+15,"M")	=IF(ISERROR(BK17),0,BK17)