Predict a Future Date?

**Brook963** · 07-13-2011, 05:28 PM

Hello,
I have been working on trying to come up with a formula all day and have searched the web and can't find what I need.

For example, I bill customers every three months on the second business day of the month (so not including holidays or weekends). I sent out a bill on 7/5/11 since the first was a friday, the 4th was a holiday that made the 5th of July the "Second Business Day".

I want to create a table that gives me all my future bill dates so I know when to bill going forward, instead of manually having to compute the data. So I billed 7/5/11 and will bill again 10/4/11. I have tried doing a WORKDAY formula, I have tried calculating the days in the month but that always puts me off a day or two randomly and I can't figure out how to get this to work.

I also would like to be able to say in a similar formula that the day should be a Monday. Sort of the same situation as above but if I bill on 7/11/11 I want to bill again 10/10/11. I want that formula to always have the date of a Monday.

Anyone have any clues for either situation?? Let me know if you need more information or anything! Thanks!

**daddylonglegs** · 07-13-2011, 05:50 PM

If you put the first bill date in A1, e.g. 7/5/2011 then you can get a list of bill dates in A2 down with this formula

=WORKDAY(EOMONTH(A1,2),2,H$2:H$10)

where H2:H10 contains a list of holiday dates

format in required date format

For your second question if you want the 2nd Monday of every 3rd month then with 1st date in C1 try this formula in c2 copied down

=EOMONTH(A1,2)+15-WEEKDAY(EOMONTH(A1,2)+13)

**Paul** · 07-13-2011, 05:51 PM

Hi Brook, welcome to the forum.

I think this may work for you to find the second business day 3 months after a date specified in another cell. First, you have to create a list of your holidays in an unused range. In the example below, I put various holidays in H1:H3, including 7/4/2011 (July 4). In A1 I have the date 4/15/2011. In B1 the following formula returns 7/5/2011. If I change A1 to 7/5/2011, B1 returns 10/4/2011.

=WORKDAY(DATE(YEAR(A1),MONTH(A1)+3,0),2,$H$1:$H$3)

One question, though. What if I entered 7/29/2011? Should it still return October 4th? Technically it is still the third month away, however it is definitely not a full three calendar months away.

**Brook963** · 07-14-2011, 08:56 AM

DLL, thanks! I am able to predict the bills for the 2nd business day of the month.
But I'm still having trouble with the second formula - to make a date land on a monday.

For example, say I sent a notice out on 7/11 - I want the next notice three months from that but on a Monday. So it should be 10/10. My other notice in July is sent on 7/18 and I want the next one for that person to be on 10/17. So for each it's almost exactly three months away but I want the date that the formula predicts to land on a Monday so sometimes it's one day less sometimes one day more (from three months exactly).

I tried the second formula you gave me and it worked for the first scenario, but when I copied it to the second it provided me with the same date as the first one?

Originally Posted by daddylonglegs

If you put the first bill date in A1, e.g. 7/5/2011 then you can get a list of bill dates in A2 down with this formula

=WORKDAY(EOMONTH(A1,2),2,H$2:H$10)

where H2:H10 contains a list of holiday dates

format in required date format

For your second question if you want the 2nd Monday of every 3rd month then with 1st date in C1 try this formula in c2 copied down

=EOMONTH(A1,2)+15-WEEKDAY(EOMONTH(A1,2)+13)

**daddylonglegs** · 07-14-2011, 09:05 AM

OK, I meant to use C1 in that formula......but I'm not sure it works anyway given your second example. Try this formula

=EDATE(C1,3)+4-WEEKDAY(EDATE(C1,3)+2)

assuming date in C1