# Calculating future dates

#### Peak Seagull

##### Member
Hi,

In the attached spreadsheet I would like to be able to populate the ‘Next management review due’ (cell C7) with a date that is calculated from the ‘Date of last management review’ (cell C5) and the ‘Management review frequency’ (cell C6).

Is it possible to do this when cell C6 uses a drop down list? or is it better to have a specific number entered into that cell? Also, I would need to ensure that cell C7 is populated with a weekday bot a weekend.

#### Attachments

• 9.3 KB Views: 8

#### Peter Bartholomew

##### Well-Known Member
There is nothing wrong with using a dropdown list.
Code:
= LET(
interval, XMATCH(freq, frequencies),
number, INDEX({7,14,1,3,12}, interval),
fortnightly, 2,
IF( interval>fortnightly,
EDATE(priorDate, number),
priorDate + number)
)
Does a weekend convert to a Friday or the following Monday?

#### Peak Seagull

##### Member
There is nothing wrong with using a dropdown list.
Code:
= LET(
interval, XMATCH(freq, frequencies),
number, INDEX({7,14,1,3,12}, interval),
fortnightly, 2,
IF( interval>fortnightly,
EDATE(priorDate, number),
priorDate + number)
)
Does a weekend convert to a Friday or the following Monday?
Thank you Peter - a weekend would be a Saturday and Sunday.

#### Peter Bartholomew

##### Well-Known Member
Unless you have a very recent version of Excel 365 this solution will need to be refactored.
Code:
= LET(
interval, XMATCH(freq, frequencies),
number, INDEX({7,14,1,3,12}, interval),
fortnightly, 2,
date,
IF( interval>fortnightly,
EDATE(priorDate, number),
priorDate + number),
IF( WEEKDAY(date,16)<=2,
date - WEEKDAY(date,16),
date )
)

#### Peak Seagull

##### Member
Unless you have a very recent version of Excel 365 this solution will need to be refactored.
Code:
= LET(
interval, XMATCH(freq, frequencies),
number, INDEX({7,14,1,3,12}, interval),
fortnightly, 2,
date,
IF( interval>fortnightly,
EDATE(priorDate, number),
priorDate + number),
IF( WEEKDAY(date,16)<=2,
date - WEEKDAY(date,16),
date )
)
O.K. Thank you Peter

#### Excel Wizard

##### Member

=WORKDAY(CHOOSE(MATCH(C6,I5:I9,),C5+7,C5+14,EDATE(C5,1),EDATE(C5,3),EDATE(C5,12))-1,1)

#### Attachments

• 9.6 KB Views: 5