# Calculate future dates from start date with varying time period/cycle

1. ## Calculate future dates from start date with varying time period/cycle

Greetings.

I need to determine a formula which will allow me to calculate a future date based upon a current date with varying time periods.

For example:

I have a bill which is paid on the 15th and last business day of each month. I would like to be able to see the next due date regardless of what day of the week it is.

I have a bill which is paid every other Tuesday. I would like to know the next due date without having to enter +14 for every due date in the future. In other words, it is preferable to be able to open the spreadsheet and automatically see the next due date, not use autofill to repeatedly add +14 to a previous date which would limit the # of future due dates that could be calculated.

I have a bill which is paid on the last business day of each month, not the last Friday of each month. I would need excel to return a value for the last day of the month which = Monday-Friday, regardless of what day of the week it may be as long as it isn't Saturday or Sunday(holiday exclusion would be nice but not required).

-Tony  Register To Reply

2. ## Re: Calculate future dates from start date with varying time period/cycle

15th of month on or after today: =DATE(YEAR(dateStart), MONTH(dateStart) + (DAY(dateStart) > 15), 15)

Last weekday of this month: =WORKDAY(EOMONTH(dateStart, 0) + 1, -1)  Register To Reply

3. ## Re: Calculate future dates from start date with varying time period/cycle

Wow. Thank you so much.

I am still entering something incorrectly:

I entered:

=DATE(YEAR(2009), MONTH(9) + (DAY(1) > 15), 15)

the result:

1/15/1905

I entered:

=WORKDAY(EOMONTH(9/30/2009, 0) + 1, -1)

the result:

31  Register To Reply

4. ## Re: Calculate future dates from start date with varying time period/cycle

=DATE(YEAR(2009), MONTH(9) + (DAY(1) > 15), 15)

In Excel date/time, whole numbers are elapsed days since 0 Jan 1900, and the fractional part is time. So 2009 is 02 Jul 1905, and Year(2009) is 1905. Similar for Month(9).

=WORKDAY(EOMONTH(9/30/2009, 0) + 1, -1)

9/30/2009 is not a date in that formula, it's an arithmetic expression.

Put a date in a cell and name it dateStart, then use the formulas shown.  Register To Reply

5. ## Re: Calculate future dates from start date with varying time period/cycle

Thank you. How do I "name" a cell?

Using:

=WORKDAY(EOMONTH(9/30/2009, 0) + 1, -1)

Excel resulted in 9/15/2009 (success!)

Using:

=WORKDAY(EOMONTH(dateStart, 0) + 1, -1)

Excel resulted in 40086 (confusion?)

Thank you again.  Register To Reply

6. ## Re: Calculate future dates from start date with varying time period/cycle

Thank you. How do I "name" a cell?
Enter the name in the Names box, left of the formula bar.

Excel resulted in 40086 (confusion?)
40,086 days after 0 Jan 1900. Format as a date.  Register To Reply

7. ## Re: Calculate future dates from start date with varying time period/cycle

Success!

Thank you again. Any ideas on how to automatically calculate every other Tuesday without changing the start date or repeatedly adding 14?  Register To Reply

8. ## Re: Calculate future dates from start date with varying time period/cycle

If A1 contains some past odd Tuesday, then the next off Tuesday on or after today is =A1 + CEILING(TODAY() - A1, 14)  Register To Reply

9. ## Re: Calculate future dates from start date with varying time period/cycle

Hello again. The formula you gave me for EOM works but it only returns the EOM for the start date. It does not calculate future dates.

For example:

start date is 8/01/2009, today's date is 9/14/2009; the formula results in 8/31/2009, not 9/30/2009.

I tried to use the formula for BI-WEEKLY to see if I could figure out which part of it calculates future dates but I'm afraid I am just too unfamiliar with excel formulas to figure it out on my own. I couldn't figure out the correct syntax for "CEILING".  Register To Reply

10. ## Re: Calculate future dates from start date with varying time period/cycle

In fact, the formula for the 15th does the same thing. I had not entered an updated start date to test it. These formulas return the next date and the next date only. I need the formula to check today's date and to return a result which is always in the future.  Register To Reply

11. ## Re: Calculate future dates from start date with varying time period/cycle

The last weekday of the current month is =WORKDAY(EOMONTH(TODAY(), 0) + 1, -1)  Register To Reply

12. ## Re: Calculate future dates from start date with varying time period/cycle

If you always want the formula to apply to TODAY's date then either put this formula in your startdate cell

=TODAY()

or use shg's formulas but replace Startdate with TODAY(), e.g.

=DATE(YEAR(TODAY()), MONTH(TODAY()) + (DAY(TODAY()) > 15), 15)

or you can use this formula for next 15th

=EOMONTH(TODAY()-15,0)+15  Register To Reply

13. ## Re: Calculate future dates from start date with varying time period/cycle

Is there a way to return the previous business day if the 15th or last falls on a weekend?  Register To Reply

14. ## Re: Calculate future dates from start date with varying time period/cycle

shg's formula already gives the last business day of the month, i.e.

=WORKDAY(EOMONTH(TODAY(), 0) + 1, -1)

For the 15th what result do you expect to get if today is Saturday 14th? In that scenario the next 15th is in the future (i.e. tomorrow) but the business day which precedes the next 15th is yesterday (i.e. in the past) so would you want to return that date or the 15th of the next month?

If you want next month then try

=WORKDAY(EOMONTH(TODAY(),-1+(WORKDAY(EOMONTH(TODAY(),-1)+16,-1)<TODAY()))+16,-1)  Register To Reply

15. ## Re: Calculate future dates from start date with varying time period/cycle

Preferably it would be Friday the 13th. This way I am getting reminded always before the actual due date and before the weekend comes.

Is there a different formula for monthly? I tried using =EDATE but I can't figure out how to make it ahead of today. =EDATE(start_date,1) always gives me the very next month, but not the future dates from today.  Register To Reply

16. ## Re: Calculate future dates from start date with varying time period/cycle

Did you guys give up on me? Sorry. I'm an Excel n00b.  Register To Reply