• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Calculating future dates

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.

Thanks for your help.
 

Attachments

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?
 
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 )
  )
 
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
 
Top