Excel Help: Calendar Logic

Talka

For my job, I have a number of recurring tasks. Those 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. Every "solution" I've tried just breaks them in a different way.

I understand this isn't the most straightforward of tasks for Excel, but I feel like what I've tried so far is needlessly complicated. Does anyone know a better way of approaching this? Or does anyone want to try their luck at correcting my formulas?

Please note I have my reasons why this needs to be done in Excel. Calendar software is not an option.

Pailryder

seems like you had it right? Except for 'due X workdays after start of year', which you need to swap your 12.31 with 1.1
Let's say your sheet looks like the one below with row 1 being headers, row 2 containing your input date and row 3 being your number of days (let's hope this doesn't look too horrible, comma delimited).

Values, Item, Date
6/7/2013, Due X workdays after start of month, 6/5/2013
3, Due X workdays before end of month, 6/26/2013
, Due X workdays after start of quarter, 4/3/2013
, Due X workdays before end of quarter, 6/26/2013
, Due X workdays after start of year, 1/4/2013
, Due X workdays before end of year, 12/27/2013

now here are your formulas in column C (make sure your format as a date field)

=WORKDAY(EOMONTH(A2,-1),A3)
=WORKDAY(EOMONTH(A2,0)+1,-A3)
=WORKDAY(DATE(YEAR(A2),FLOOR(MONTH(A2)-1,3)+1,1)-1,A3)
=WORKDAY(DATE(YEAR(A2),CEILING(MONTH(A2),3)+1,0)+1,-A3)
=WORKDAY(DATE(YEAR(A2),1,1),A3)
=WORKDAY(DATE(YEAR(A2),12,31)+1,-A3)

so unless i'm just missing what you are asking for, not sure what else you need.

Pailryder

A friend told me to do it more betterererer so we are actually going to use two sheets to keep this clean. Your first sheet contains three columns: (Dates, Description, Other) and will be your 'Inputs'. You are going to want to refer to http://office.microsoft.com/en-us/excel-help/workday-HP005209339.aspx for explanation of the Holiday idea but seems like you should be able to get it from what you've posted. Our second sheet will be our outputs and you'll be using a vlookup for the "Start Date", "Interval", and your holidays. Here's the data:

Description Date Other
Start Date 12/1/2012
Interval 10
Holiday 11/22 Thanksgiving
Holiday 12/25 Christmas
Holiday 2/20 Presidents Day
Holiday 7/4 4th of July

Now those dates aren't always static so you'll need to enter formulas. I'll refer you to http://books.google.com/books?id=vLVVkvl6YToC&pg=PT193&lpg=PT193&dq=excel+presidents+day+formula&source=bl&ots=9qPxemmZu1&sig=mkXIaaQ2f_tpx4czzTiOdL-DxOQ&hl=en&sa=X&ei=OAW4UKGxHuHxiwLEo4DIAg&ved=0CCwQ6AEwAA but also here are the formulas for those specific holidays:

thanksgiving: =DATE(YEAR(VLOOKUP("Start Date",A:B,2,0)),11,CHOOSE(WEEKDAY(DATE(YEAR(VLOOKUP("Start Date",A:B,2,0)),11,1)),26,25,24,23,22,28,27))
christmas: =DATE(YEAR(NOW()),12,25)
presidents day: =DATE(YEAR(VLOOKUP("Start Date",A:B,2,0)),2,1)+IF(2<WEEKDAY(DATE(YEAR(VLOOKUP("Start Date",A:B,2,0)),2,1)),7-WEEKDAY(DATE(YEAR(VLOOKUP("Start Date",A:B,2,0)),2,1))+2,2-WEEKDAY(DATE(YEAR(VLOOKUP("Start Date",A:B,2,0)),2,1)))+((3-1)*7)
July 4th: 7/4/2012

Enter whatever other holidays you want to have on there. Now on to the 'Output' sheet. Your 'Output' sheet is going to have two columns (Item, Date). The Item formula will be:
=CONCATENATE("Next time 'Due' ", VLOOKUP("Interval",Inputs!A:B,2,0), " workdays after start of month' occurs:")

You will want to change the last part of that for each row that you are going to have different items. Based on what was above, here is the data.

Item
Item Date
Next time 'Due' 10 workdays after start of month' occurs: 1/14/2013
Next time 'Due' 10 workdays before end of month' occurs: 12/18/2012
Next time 'Due' 10 workdays after start of quarter' occurs: 1/15/2013
Next time 'Due' 10 workdays before end of quarter' occurs: 12/18/2012
Next time 'Due' 10 workdays after start of year' occurs: 1/15/2013
Next time 'Due' 10 workdays before end of year' occurs: 12/18/2012

The first formula is actually problematic based on if you always want the next months calculation or you want it variable depending on if you are within those first X days. So i'm assuming we can keep this simple and the formulas for the equivalent rows are as follows (in order):

=WORKDAY(EOMONTH(VLOOKUP("Start Date",Inputs!A:B,2,0),0),VLOOKUP("Interval",Inputs!A:B,2,0)+1, VLOOKUP("Holiday",Inputs!A:B,2,0))

=WORKDAY(EOMONTH(VLOOKUP("Start Date",Inputs!A:B,2,0),0),1-VLOOKUP("Interval",Inputs!A:B,2,0), VLOOKUP("Holiday",Inputs!A:B,2,0))

=IF(INT(MONTH(VLOOKUP("Start Date",Inputs!A:B,2,0))/3)+1=1, WORKDAY(DATE(YEAR(VLOOKUP("Start Date",Inputs!A:B,2,0)), 4, 1),VLOOKUP("Interval",Inputs!A:B,2,0), VLOOKUP("Holiday",Inputs!A:B,2,0)), IF(INT(MONTH(VLOOKUP("Start Date",Inputs!A:B,2,0))/3)+1=2, WORKDAY(DATE(YEAR(VLOOKUP("Start Date",Inputs!A:B,2,0)), 7, 1),VLOOKUP("Interval",Inputs!A:B,2,0), VLOOKUP("Holiday",Inputs!A:B,2,0)), IF(INT(MONTH(VLOOKUP("Start Date",Inputs!A:B,2,0))/3)+1=3, WORKDAY(DATE(YEAR(VLOOKUP("Start Date",Inputs!A:B,2,0)), 10, 1), VLOOKUP("Interval",Inputs!A:B,2,0), VLOOKUP("Holiday",Inputs!A:B,2,0)), WORKDAY(DATE(YEAR(VLOOKUP("Start Date",Inputs!A:B,2,0))+1, 1, 1), VLOOKUP("Interval",Inputs!A:B,2,0), VLOOKUP("Holiday",Inputs!A:B,2,0)))))

=IF(INT(MONTH(VLOOKUP("Start Date",Inputs!A:B,2,0))/3)+1=1, WORKDAY(DATE(YEAR(VLOOKUP("Start Date",Inputs!A:B,2,0)), 4, 1), 0-VLOOKUP("Interval",Inputs!A:B,2,0), VLOOKUP("Holiday",Inputs!A:B,2,0)), IF(INT(MONTH(VLOOKUP("Start Date",Inputs!A:B,2,0))/3)+1=2, WORKDAY(DATE(YEAR(VLOOKUP("Start Date",Inputs!A:B,2,0)), 7, 1), 0-VLOOKUP("Interval",Inputs!A:B,2,0), VLOOKUP("Holiday",Inputs!A:B,2,0)), IF(INT(MONTH(VLOOKUP("Start Date",Inputs!A:B,2,0))/3)+1=3, WORKDAY(DATE(YEAR(VLOOKUP("Start Date",Inputs!A:B,2,0)), 10, 1), 0-VLOOKUP("Interval",Inputs!A:B,2,0), VLOOKUP("Holiday",Inputs!A:B,2,0)), WORKDAY(DATE(YEAR(VLOOKUP("Start Date",Inputs!A:B,2,0))+1, 1, 1), 0-VLOOKUP("Interval",Inputs!A:B,2,0), VLOOKUP("Holiday",Inputs!A:B,2,0)))))

=WORKDAY(DATE(1+YEAR(VLOOKUP("Start Date",Inputs!A:B,2,0)),1,1),VLOOKUP("Interval",Inputs!A:B,2,0))

=WORKDAY(DATE(YEAR(VLOOKUP("Start Date",Inputs!A:B,2,0)),12,31),1-VLOOKUP("Interval",Inputs!A:B,2,0))

If that works or isn't what you wanted, i'd sure love to know.

Talka

@Pailryder, thanks so much for typing this all out. I hadn't considered holidays. I've tested your holiday setup and it seems to work great. I'll definitely incorporate it into my file.

However, I'm still having problems with the original calendar formulas. For simplicity's sake, let's focus on just the first condition ("Due X workdays after start of month") and put the holiday part to the side for the moment.

Here's my current formula when A2 is the date and A1 is the number of workdays: =WORKDAY(EOMONTH(A2,-1),A1)

This returns a date that's X workdays after the start of a month, but not always the next date that's X workdays after the start of a month. For example, if I feed the formula A2 = 2012.11.29 and A1 = 3, it returns 2012.11.05. That's three workdays after the start of a month, but it's in the past. The correct output should be 2012.12.05.

In this particular instance, I can get the correct output with this formula: =WORKDAY(EOMONTH(A2,0),A1)

However, this formula has the same problem with certain other dates! If I feed it A2 = 2012.11.01 and A1 = 3, it returns 2012.12.05. Again, that's three workdays after the start of a month, but it misses 2012.11.05, which is the next such date.

I've established that in all cases, one of these two formulas is right. So I've been experimenting with an IF() statement that calculates both formulas and returns whichever is 1) smaller, and 2) in the future.

But the formula has gotten so long and ugly... and this is just one of the six pieces I need to work out! I feel like there must be a cleaner way of calculating this! Am I mistaken?

neth

Hmmm, looking at your example the problem you are having seems to be being caused by your testing. the right formula should be WORKDAY(EOMONTH(A2,0),A1) because you put in 11/29/2012 that gets you the end of november (11/30/2012) and adds 3 workdays to it (12/5/2012). remember EOMONTH just gets the end of the month of the month of the date you fed the formula, so when you put in 11/1/2012 it gets you the end of november again (11/30/2012) and adds 3 work days again for 12/5/2012.
So back to your broken test case, if this process is something you are doing 3 days after the start of the month, 11/1/2012 wouldn't really be a valid date, or if that was when you did the task (you did it early) the next date you would want is after the start of december isn't it? so it would be working fine.

Pailryder

agree, the reality is that if you have to take into account the possibility of being in the current month or past months than you have to set up a condition evaluation. compare against today() and use whichever method seems best. Personally if i could, i would just build into the assumption that you are doing this for future dates and not hassle with making the formulas more robust. you alternately just add one month to your values so you are always looking at next month and see where that falls.