Talka
Registered User regular

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:

__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:

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.__

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 yearFor each of these six types, I want to feed Excel the X variable above (e.g., "Due

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.26I'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?

0

## Posts

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).

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

=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)

PailryderonBlizzard: Pailryder#1101

GoG: https://www.gog.com/u/pailryder

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

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

=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 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

=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))

PailryderonBlizzard: Pailryder#1101

GoG: https://www.gog.com/u/pailryder

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

adate that's X workdays after the start of a month, but not always thenextdate 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 ofamonth, 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

amonth, but it misses 2012.11.05, which is thenextsuch 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?

TalkaonSo 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.

nethonBlizzard: Pailryder#1101

GoG: https://www.gog.com/u/pailryder