+ Reply to Thread
Results 1 to 20 of 20

How To Calculate future dates from start date on a monthly cycle

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

    How To Calculate future dates from start date on a monthly cycle

    I'm trying to combine monthly calculations with "today" and with "workdays"


    Example:

    start date = 01/01/2009

    today's date 09/16/2009

    formula result = 10/01/2009 ; or if 10/01/2009 is a Sunday, result = 09/29/2009 (not 02/01/2009, 03/01/2009, etc)

    =edate gives me a month but it doesn't skip weekends or calculate beyond today's date
    Last edited by HOWTOEXCEL; 09-16-2009 at 05:43 PM. Reason: SOLVED

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: How To Calculate future dates from start date on a monthly cycle

    Do you just want to add a day to the end of the end month? And if it Sat/Sunday, then go to the previous Friday?

    If so, try

    Please Login or Register  to view this content.
    where A1 and B1 contain, start and end dates...

    else, more explanation, examples required.
    Last edited by NBVC; 09-16-2009 at 02:56 PM.
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

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

    Re: How To Calculate future dates from start date on a monthly cycle

    "Do you just want to add a day to the end of the end month? And if it Sat/Sunday, then go to the previous Friday?"

    No. I need to find the next monthly equivalent to any date.

    The example I posted was:

    Example:

    start date = 01/01/2009

    today's date 09/16/2009

    formula result = 10/01/2009 ; or if 10/01/2009 is a Sunday, result = 09/29/2009 (not 02/01/2009, 03/01/2009, etc)


    Basically,

    "I'm trying to combine monthly calculations with "today" and with "workdays""

    Let's say I need to find out my next payday. I get paid on the 19th of each month. My first payday is 1/19/2009. Today is 9/16/2009. The formula should result in 9/18/2009, because I have already been paid on 8/19, and because the 19th of September is a Saturday.

    Does this make sense?

    =edate would work except it doesn't omit weekends and it doesn't give future dates, only one single date exactly one month from the original start date. I want to be able to see future dates without updating the start date.
    Last edited by HOWTOEXCEL; 09-16-2009 at 03:18 PM.

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: How To Calculate future dates from start date on a monthly cycle

    Did you test my formula...how does it not help.. I am not sure I understand...?

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

    Re: How To Calculate future dates from start date on a monthly cycle

    O.K. Testing your formula now; be right back..

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

    Re: How To Calculate future dates from start date on a monthly cycle

    I'm sorry. I don't understand how to use this formula.

    What is B1 for?

    What is ym and ddd?

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: How To Calculate future dates from start date on a monthly cycle

    B1 would contain Today's date.. you can replace with TODAY() function...

    The "ym" is part of the Datedif() function which tells it to return the number of months between dates...

    the "ddd" is part of the Text() function and that converts the date to a text string made of the abbreviated day of the week name.


    The formula is probably wrong... since I am not exactly sure of your request... what other kinds of dates can be in your start date and what results would be required then???

    .
    .
    .
    Last edited by NBVC; 09-16-2009 at 03:28 PM.

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

    Re: How To Calculate future dates from start date on a monthly cycle

    Let's say I need to find out my next payday. I get paid on the 19th of each month. My first payday is 1/19/2009. Today is 9/16/2009. The formula should result in 9/18/2009, because I have already been paid on 8/19, and because the 19th of September is a Saturday.

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

    Re: How To Calculate future dates from start date on a monthly cycle

    ..and of course the point is to avoid creating a new formula every month so that if I open the spreadsheet next month it will automatically result in 10/19/2009. Sorry for leaving that out..

  10. #10
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: How To Calculate future dates from start date on a monthly cycle

    I think I will leave this one to our resident dates expert, daddylonglegs...

    Hopefully he will logon and help you...

  11. #11
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: How To Calculate future dates from start date on a monthly cycle

    Here is a more brute force formula that should work... but again dll will probably suggest something more efficient...

    Please Login or Register  to view this content.

    where A1 contains today's date or replace all A1 with Today() function.

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

    Re: How To Calculate future dates from start date on a monthly cycle

    I appreciate your effort but the date will not always be the 19th.

    I need to be able to calculate off a varying start date.

    For instance: Donkey and DLL gave me this for somethig else:

    =A1 + CEILING(TODAY() - A1, 14)


    This formula works perfectly for bi-weekly dates when the day of the week is always the same, and after I looked up what the "CEILING" function I was impressed with his application of this function, however when i tried to change the "14" to "30" or "31" it was not successful.

    You must understand I am VERY new to Excel. I don't even understand the basic functions yet, so when I am presented with a possible solution, I am unable to disect the formula and apply what works and disregard what doesn't. I am unfamiliar with the proper syntax in Excel as well.
    Last edited by HOWTOEXCEL; 09-16-2009 at 04:26 PM.

  13. #13
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: How To Calculate future dates from start date on a monthly cycle

    Last attempt with brute force method:

    Please Login or Register  to view this content.
    where A1 contains start date... so it uses the day number in A1 to determine the resulting date.

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

    Re: How To Calculate future dates from start date on a monthly cycle

    Wow. That was awesome. I wish I could figure out how it works so I could learn something from you, but it works.

    I would like to change this to solved because you have indeed solved that problem, but I have a few more scenarios in this same spread sheet that I would like to run by you if you dont mind. Should I leave this open, or can I PM you?

  15. #15
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: How To Calculate future dates from start date on a monthly cycle

    Well, I am going to be logging off shortly....so can't guarantee an answer tonight...

    ... I suggest you close this thread and start a new thread with new questions...

    ...somebody I am sure will help... Maybe even I if within 15 or so minutes...

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

    Re: How To Calculate future dates from start date on a monthly cycle

    K. I'm changing it to SOLVED.

    Here's my other problem(for which I will also create a new thread):

    Sometimes my due dates need to be on the 15th of the month, for which DLL and Donkey gave me:

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

    This works great except sometimes the 15th is on a Sunday which means I need the result to be the 13th, and sometimes it's on a Saturday which means I need the result to be the 14th.

    I would appreciate your help on this but honestly you guys have already been so helpful I am happy with what you have already given me.

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

    Re: How To Calculate future dates from start date on a monthly cycle

    This is a reply to your original question in this thread, not the last one........[with regard to the question in your last post...I note you posted the same question elsewhere....please try to stick to 1 question per thread and 1 thread per question.......]

    If your start date is 16th of the month and today is 16th then do you want it to show today's date or 16th of next month.

    What happens if the start date is, say 31st July? Is that possible, if so should the formula show the 30th September as there insn't a 31st?

    Here's one possible option

    =WORKDAY(EDATE(A1,DATEDIF(A1,TODAY(),"m")+1)+1,-1)

    It may need to be tweaked depending on your answers to the above.....
    Last edited by daddylonglegs; 09-16-2009 at 06:48 PM.

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

    Re: How To Calculate future dates from start date on a monthly cycle

    Quote Originally Posted by daddylonglegs View Post
    This is a reply to your original question in this thread, not the last one........

    If your start date is 16th of the month and today is 16th then do you want it to show today's date or 16th of next month.

    What happens if the start date is, say 31st July? Is that possible, if so should the formula show the 30th September as there insn't a 31st?

    Here's one possible option

    =WORKDAY(EDATE(A1,DATEDIF(A1,TODAY(),"m")+1)+1,-1)

    It may need to be tweaked depending on your answers to the above.....
    Yes. We are on the same page. start date of July 31st should result in 9/30. let me try this and get back to you.

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

    Re: How To Calculate future dates from start date on a monthly cycle

    Quote Originally Posted by daddylonglegs View Post
    I thought I answered this one already.......

    You can just apply WORKDAY, i.e.

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

    although if you are using WORKDAY you could also use EOMONTH, i.e.

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

    Note: I'm assuming you have access to functions WORKDAY, EDATE, EOMONTH which are part of Analysis ToolPak add-in...
    Yes you did. I just didn't know the syntax for adding workday in there. Lol, sorry. I read what you said and understood you, but I couldn't figure out how to formulate it correctly. I copied and pasted this formula and it worked perfectly.

    Your formulas are working just as you proposed they should, so I am asuming that I have this add-in. ???

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

    Re: How To Calculate future dates from start date on a monthly cycle

    Excellent. Thank you all so much. I now have a functioning spreadsheet.

+ 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