Excel Help: Calendar Logic

TalkaTalka Registered User regular
edited November 2012 in Help / Advice Forum
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.

Talka on

Posts

  • PailryderPailryder Registered User regular
    edited November 2012
    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 on
  • PailryderPailryder Registered User regular
    edited November 2012
    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.

    Pailryder on
  • TalkaTalka Registered User regular
    edited November 2012
    @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?

    Talka on
  • nethneth Registered User regular
    edited November 2012
    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.

    neth on
  • PailryderPailryder Registered User regular
    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.

Sign In or Register to comment.