Date + days remaining = auto generate estimated completion date

Elizabeth Jones

Hi! I have a sheet where I'm making projected completion dates. I know that for the total work remaining we have 88 days. Is there a formula to add today's date (dynamic) + 88 days (dynamic)?

Thanks!

Genevieve P.

Hi Elizabeth,

I'm not quite sure I understand what you're looking to do... you could use the TODAY function to return today's date, and then simply + the number of days you want to have a different date returned. For example:

=TODAY() + 88

This would need to be put in a Date column. However when tomorrow rolls around and you open the sheet, it will show you 88 days from that date now, without adjusting this to be 87. Are you wanting it to adjust each day?

Also, are you wanting to add Days in general (including weekends) or you do just want to figure out the Working Days?

I would suggest that once you have figured out what the date would be 88 days from now, you could use either the NETDAYS or the NETWORKDAYS function to return the number of days left, until that date.

All Days example:

Today + 88 regular days = July 12th, 2020

So you could just figure out the regular days left with this:

=NETDAYS(TODAY(), DATE(2020, 07, 12))

OR Working Days:

Today + 88 working days (not including Sat/Sun) = August 17th, 2020

=NETWORKDAYS(TODAY(), DATE(2020, 8, 17))

This will return a number and count down the days until that specific date, updating when you open the sheet each day without any other action needed. If you also wanted to show the End Date, you could reference that cell in your formula instead of using the DATE function.

Let me know if this makes sense, or if you'd like to see some examples!

Cheers,

Genevieve

Genevieve P.

Great! Happy to help 😊

Genevieve P.

Hi Elizabeth,

I'm not quite sure I understand what you're looking to do... you could use the TODAY function to return today's date, and then simply + the number of days you want to have a different date returned. For example:

=TODAY() + 88

This would need to be put in a Date column. However when tomorrow rolls around and you open the sheet, it will show you 88 days from that date now, without adjusting this to be 87. Are you wanting it to adjust each day?

Also, are you wanting to add Days in general (including weekends) or you do just want to figure out the Working Days?

I would suggest that once you have figured out what the date would be 88 days from now, you could use either the NETDAYS or the NETWORKDAYS function to return the number of days left, until that date.

All Days example:

Today + 88 regular days = July 12th, 2020

So you could just figure out the regular days left with this:

=NETDAYS(TODAY(), DATE(2020, 07, 12))

OR Working Days:

Today + 88 working days (not including Sat/Sun) = August 17th, 2020

=NETWORKDAYS(TODAY(), DATE(2020, 8, 17))

This will return a number and count down the days until that specific date, updating when you open the sheet each day without any other action needed. If you also wanted to show the End Date, you could reference that cell in your formula instead of using the DATE function.

Let me know if this makes sense, or if you'd like to see some examples!

Cheers,

Genevieve

Elizabeth Jones

this makes sense and is now performing exactly as I'd like. Thank you for your help.

Genevieve P.

Great! Happy to help 😊

Jeana

@Genevieve P I have a related question if you don't mind. I'm working on a formula that will return the week (Mon - Fri dates) based on the date in another column (END DATE).

So if the END DATE is 10/21/2020 I would like to return the Mon-Fri dates for that week - 10/19/20 - 10/23/20.

Based on this thread I can easily determine the the date 14 days in advance, 21 days in advance, etc. I'm stumped on the formula to actually return the Mon-Fri dates for the week that the END date falls in, if that makes sense.

I'm already calculating how many weeks in advance the date is but I'd like to display on a Dashboard the dates of that week. Any help you could offer would be greatly appreciated.

Thanks,

Jeana

Genevieve P.

Hi @Jeana

No problem, I'm happy to work this out with you! Now, is this date in the same sheet as the dates you want returned? I'll build this out presuming that they are.

So you essentially need 7 results, or 7 formulas, one for each day of the week, correct?

In that case, in the SUNDAY date cell, you'll want to use a Nested IF statement with the WEEKDAY function to say, if this date is Sunday, return that date. But if it's Monday, return that date minus 1 (which would be Sunday). If it's Tuesday, return that date minus 2, etc.

Here's an example if the date you're looking for in the END DATE column is in the same row as your Sunday column:

=IF(WEEKDAY([End Date]@row) = 1, [End Date]@row + 2, IF(WEEKDAY([End Date]@row) = 2, [End Date]@row + 1, IF(WEEKDAY([End Date]@row) = 3, [End Date]@row, IF(WEEKDAY([End Date]@row) = 4, [End Date]@row - 1, IF(WEEKDAY([End Date]@row) = 5, [End Date]@row - 2, IF(WEEKDAY([End Date]@row) = 6, [End Date]@row - 3, IF(WEEKDAY([End Date]@row) = 7, [End Date]@row - 4)))))))

Otherwise, if the date is only listed once in your sheet, you'll need to use an absolute row reference. Then for your MONDAY date, you'll need to adjust how many days you're taking off or adding on to the End Date, based on when it falls during the week.

For example, here's Tuesday, referencing the End Date in row 1:

=IF(WEEKDAY([End Date]$1) = 1, [End Date]$1 + 2, IF(WEEKDAY([End Date]$1) = 2, [End Date]$1 + 1, IF(WEEKDAY([End Date]$1) = 3, [End Date]$1, IF(WEEKDAY([End Date]$1) = 4, [End Date]$1 - 1, IF(WEEKDAY([End Date]$1) = 5, [End Date]$1 - 2, IF(WEEKDAY([End Date]$1) = 6, [End Date]$1 - 3, IF(WEEKDAY([End Date]$1) = 7, [End Date]$1 - 4)))))))

Look how I adjusted the returned value to say =IF(WEEKDAY([End Date]$1) = 1, [End Date]$1 + 2

This means, if the date is 1, or Sunday, and I want to return TUESDAY of that week, well then I will want the End Date + 2.

Let me know if I've understood what you're looking to do, and if this will work for you! If not, it may be helpful to know a bit more about where this End Date is stored and where you want your formula output to be (screen captures would be ideal, but please block out any sensitive data).

Cheers,

Genevieve

Genevieve P.

Hi @Jeana

I've just re-read your post and I realize I may have misunderstood. Did you want the text "10/19/20 - 10/23/20" or the text of the Monday date - then - Friday date for that week to appear in a cell in the same row as the End Date? With many possible End Dates in that column?

If so, we would use the same formulas as discussed above, just add the one for Monday and the one for Sunday together with a = " - " between them.

This will look long & complicated but it's just two long formulas added together. Since I used @row I converted this to a Column Formula as well.

Here's the sheet:

Here's the formula. I've bolded the + signs so you can see where I've added two together:

=IF(WEEKDAY([End Date]@row) = 1, [End Date]@row + 1, IF(WEEKDAY([End Date]@row) = 2, [End Date]@row, IF(WEEKDAY([End Date]@row) = 3, [End Date]@row - 1, IF(WEEKDAY([End Date]@row) = 4, [End Date]@row - 2, IF(WEEKDAY([End Date]@row) = 5, [End Date]@row - 3, IF(WEEKDAY([End Date]@row) = 6, [End Date]@row - 4, IF(WEEKDAY([End Date]@row) = 7, [End Date]@row - 5))))))) + " - " + IF(WEEKDAY([End Date]@row) = 1, [End Date]@row + 5, IF(WEEKDAY([End Date]@row) = 2, [End Date]@row + 4, IF(WEEKDAY([End Date]@row) = 3, [End Date]@row + 3, IF(WEEKDAY([End Date]@row) = 4, [End Date]@row + 2, IF(WEEKDAY([End Date]@row) = 5, [End Date]@row + 1, IF(WEEKDAY([End Date]@row) = 6, [End Date]@row, IF(WEEKDAY([End Date]@row) = 7, [End Date]@row - 1)))))))

Let me know if this works!

Genevieve