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

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

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

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

4. 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. Re: How To Calculate future dates from start date on a monthly cycle

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

6. 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. 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???

.
.
.

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

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

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

Originally Posted by daddylonglegs
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. Re: How To Calculate future dates from start date on a monthly cycle

Originally Posted by daddylonglegs
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. 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.

Users Browsing this Thread

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

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