How can I autopopulate end date with months duration without using dependencies?

Michaela Kamenska

Hi all,

I am trying to work out how to autopopulate end date of the project based on start date and duration I have in months. I am trying to avoid using dependencies as those do not support months, only weeks, and that is not an exact value. We have several hundred projects running at the same time so really going and checking the duration and adjusting the weeks to approximately match the values is super tedious and multiplication of x months *4 is not precise at all. Let's say, the project starts 1-Jan-2020 and has a duration of 12 months, so the end date should be 31-Dec-2020. I tried using a formula I found here on this forum, on one of my sheets but it comes up as an invalid argument:

=IFERROR(DATE(YEAR([Start Date]1, MONTH([Start Date]1 + [Duration (months)]1, DAY([Start Date]1)), DATE(YEAR([Start Date]1 + 1, MONTH([Start Date]1 - [Duration (months)]1, DAY([Start Date]1)))))))

Any help is greatly appreciated.

Paul Newcome

Give this a whirl.

=DATE(YEAR([Start Date]@row) + ROUNDDOWN((MONTH([Start Date]@row) + [Duration (months)]@row) / 12, 0) + IF(IF(MOD(MONTH([Start Date]@row) + [Duration (months)]@row, 12) = 0, 12, MOD(MONTH([Start Date]@row) + [Duration (months)]@row, 12)) = 12, -1), IF(MOD(MONTH([Start Date]@row) + [Duration (months)]@row, 12) = 0, 12, MOD(MONTH([Start Date]@row) + [Duration (months)]@row, 12)), DAY([Start Date]@row))

Debbie Sawyer

How about this:

=IFERROR(DATE(YEAR([Start Date]1), MONTH([Start Date]1) + [Duration (Months)]1, DAY([Start Date]1)), DATE(YEAR([Start Date]1) + 1, MONTH([Start Date]1), DAY([Start Date]1)))

I think this works...

Kind regards

Debbie

Michaela Kamenska

Hi Debbie,

the formula did put actual date in, but for some unknown reason it only puts 1year forward to the same day (see screenshot). Any idea what could be wrong? (the red part is where the formula is, the yellow part is original date as calculated in excel). I would like to have the sheet dynamic as the project durations often change.

Debbie Sawyer

Yes, the IFERROR clause only kicks in if duration (months) + Month number in start date equate to more then 12, if it does it just adds a year to the year.

I'll work on this quickly and see if I can get it working for you.

Michaela Kamenska

Oh thank you so much, you're a star, Debbie. I've been trying to figure it out for the past few hours.

Paul Newcome

I recently helped someone else with a solution where they wanted to enter any number of months and have it added to a date. Let me dig through my notes, and I'll get back to you.

Debbie Sawyer

This isn't quite working but you can see the logic? I've been distracted this end...

=IFERROR(DATE(YEAR([Start Date]1), MONTH(MONTH([Start Date]1) + [Duration (Months)]1), DAY([Start Date]1)), DATE(YEAR([Start Date]1) + INT(MONTH([Start Date]1) + [Duration (Months)]1) / 12), MONTH(MONTH([Start Date]1) + [Duration (Months)]1 - INT((MONTH([Start Date]1) + [Duration (Months)]1) / 12) * 12), DAY([Start Date]1)))

Paul Newcome

Give this a whirl.

=DATE(YEAR([Start Date]@row) + ROUNDDOWN((MONTH([Start Date]@row) + [Duration (months)]@row) / 12, 0) + IF(IF(MOD(MONTH([Start Date]@row) + [Duration (months)]@row, 12) = 0, 12, MOD(MONTH([Start Date]@row) + [Duration (months)]@row, 12)) = 12, -1), IF(MOD(MONTH([Start Date]@row) + [Duration (months)]@row, 12) = 0, 12, MOD(MONTH([Start Date]@row) + [Duration (months)]@row, 12)), DAY([Start Date]@row))

Michaela Kamenska

Thanks everyone, this is absolutely stunning!

Thanks again for all your help! :)

Paul Newcome

Happy to help! 👍️

The original post looking for this was actually only about a month ago, and there was someone else just a couple of weeks ago looking for the same thing as well. I love when things like this come in waves because it makes it much easier to find notes and whatnot so a possible solution can be provided a little faster.

Michaela Kamenska

Yes, I used the formula you posted in one of the other threads, similar to the one in my original post (I don't like asking new questions if there is a chance someone already asked that) but unfortunately couldn't get it to work. Thanks a lot again :)

Debbie Sawyer

Paul Newcome

Sure thing. Your original formula does have some syntax issues, but overall, I don't feel it is a good fit because it was originally written for a solution that only went out an additional month. As you can see... Even if the problems are similar, the solutions can be radically different. I prefer to write them as simple as possible.

Adding a single month is as simple as MONTH+1 or (for December) YEAR+1 and make the month number 1.

Moving to your particular predicament could mean multiple years that need to be accounted for which added the additional complexity.

My point being... Don't be afraid to ask away. At the very least, you'll get a link to the other thread instead of a direct answer on yours. Haha. Either way you are getting the help you need.

Paul Newcome

@Debbie Sawyer Take a look at the solution I provided. It came about in response to a request for Excel's EDATE function. Leveraging the MOD function ended up being the biggest key factor (for me at least) when trying to get it figured out.

Debbie Sawyer

=IFERROR(DATE(YEAR([Start Date]@row), MONTH([Start Date]@row) + [Duration (Months)]@row, DAY([Start Date]@row)), DATE(YEAR([Start Date]@row) + INT((MONTH([Start Date]@row) + [Duration (Months)]@row) / 12), ((MONTH([Start Date]@row) + [Duration (Months)]@row) - (INT((MONTH([Start Date]1) + [Duration (Months)]@row) / 12) * 12)), DAY([Start Date]@row)))

I think I did it!! @Michaela Kamenska

Debbie Sawyer

@Paul Newcome - So sorry - just seen your comment to me. I was absorbed by trying to work out the formula!

Didn't manage to go and look at yours. Apologies - will do next time.

Edit: Just seen yours! Doh! Seems both yours and my formulas return the same results...Heyho! :) (always more than 1 way hey! ha ha)