+ Reply to Thread
Results 1 to 16 of 16

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

  1. #1
    Registered User
    Join Date
    09-11-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    22

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

    Im new to excel but I follow directions well. Thank you in advance for your help.

    -Tony
    Last edited by HOWTOEXCEL; 09-16-2009 at 07:24 PM. Reason: MODERATOR APPEASEMENT

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    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)
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    09-11-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    22

    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

  4. #4
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    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.

  5. #5
    Registered User
    Join Date
    09-11-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    22

    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.

  6. #6
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    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.

  7. #7
    Registered User
    Join Date
    09-11-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    22

    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?

  8. #8
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    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)

  9. #9
    Registered User
    Join Date
    09-11-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    22

    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".
    Last edited by HOWTOEXCEL; 09-14-2009 at 06:48 PM.

  10. #10
    Registered User
    Join Date
    09-11-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    22

    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.

  11. #11
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,678

    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)

  12. #12
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    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

  13. #13
    Registered User
    Join Date
    09-11-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    22

    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?

  14. #14
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    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)

  15. #15
    Registered User
    Join Date
    09-11-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    22

    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.

  16. #16
    Registered User
    Join Date
    09-11-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    22

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1