Let's say I have a number of recurring tasks. These tasks occur either monthly, quarterly or yearly. Each task is due either several days
after the start of each month/quarter/year, or several days
before the end of each month/quarter/year. This means there are six types of recurring tasks:
- Due X workdays after start of month
- Due X workdays before end of month
- Due X workdays after start of quarter
- Due X workdays before end of quarter
- Due X workdays after start of year
- Due X workdays before end of year
For each of these six types, I want to feed Excel the X variable above (e.g., "Due
3 workdays after start of month"), as well as a date. I then want Excel to tell me the next time the recurring task is due after the date provided. For example, if I feed Excel X=3 and date=2012.11.28, Excel should tell me:
- Next time "Due 3 workdays after start of month" occurs: 2012.12.05
- Next time "Due 3 workdays before end of month" occurs: 2012.11.28
- Next time "Due 3 workdays after start of quarter" occurs: 2013.01.03
- Next time "Due 3 workdays before end of quarter" occurs: 2012.12.26
- Next time "Due 3 workdays after start of year" occurs: 2013.01.03
- Next time "Due 3 workdays before end of year" occurs: 2012.12.26
I've spent a few hours messing around with the WORKDAY(), EOMONTH(), DATE(), FLOOR() and CEILING() functions. Nothing I try works. Particularly difficult are dates near the calendar cutoffs (e.g., 2012.12.31). Find below my flawed attempts. In these formulas, cell A1 is the number of workdays (i.e., "X") and cell A2 is the date.
- Next time "Due X workdays after start of month" occurs: =WORKDAY(EOMONTH(A2,-1),A1)
- Next time "Due X workdays before end of month" occurs: =WORKDAY(EOMONTH(A2,0)+1,-A1)
- Next time "Due X workdays after start of quarter" occurs: =WORKDAY(DATE(YEAR(A2),FLOOR(MONTH(A2)-1,3)+1,1)-1,A1)
- Next time "Due X workdays before end of quarter" occurs: =WORKDAY(DATE(YEAR(A2),CEILING(MONTH(A2),3)+1,0)+1 ,-A1)
- Next time "Due X workdays after start of year" occurs: =WORKDAY(DATE(YEAR(A2),12,31),A1)
- Next time "Due X workdays before end of year" occurs: =WORKDAY(DATE(YEAR(A2),12,31)+1,-A1)
None of these are correct in their current formats.
Does anyone know a better way of approaching this? Or does anyone want to try their luck at correcting my formulas?