Forum Discussion

Copper Contributor

Aug 19, 2022

Solved

EDATE WITH CONDITIONS

Need help with this type of formula:

If M21 (a date cell) is blank, then evaluate D21 (length of stay). If D21 (length of stay) is less than or equal to 730, then add 6 months to C21(admit date). If D21(length of stay) is greater than 730, then add 1 year to C21(start date).

If M21 is not blank, then evaluate D21 (length of stay). If D21 (length of stay) is less than or equal to 730, then add 6 months to M21(last assessment date). If D21(length of stay) is greater than 730, then add 1 year to M21(last assessment date).

Thanks in advance,

Barb

Formulas and Functions

SergeiBaklan

Aug 19, 2022

BarbO404717

Literally

=IF( M21="",
     EDATE( C21, 6 + 6*(D21>730) ),
     EDATE( M21, 6 + 6*(D21>730) )
)

6 Replies

SergeiBaklan
Diamond Contributor
Aug 19, 2022
BarbO404717

Literally

=IF( M21="", EDATE( C21, 6 + 6*(D21>730) ), EDATE( M21, 6 + 6*(D21>730) ) )
- BarbO404717
  Copper Contributor
  Aug 19, 2022
  OMG! It worked! Thank you so much. Is there any chance you can describe what you did in prose/text? Thanks, Barb
  - SergeiBaklan
    Diamond Contributor
    Aug 19, 2022
    BarbO404717
    
    Not sure I understood the question. We add to the date 6 months or 6+6 months depends on condition returns TRUE or FALSE, which are in arithmetic calculations equivalent to 1 and 0 accordingly.
- BarbO404717
  Copper Contributor
  Aug 19, 2022
  Thanks Sergei. Does your formula cover both scenarios regarding cell M21?
  Barb
  - SergeiBaklan
    Diamond Contributor
    Aug 19, 2022
    BarbO404717
    
    I hope so. M21 is blank or not. In first case we calculate with C21, otherwise with M21. Same formula.
    
    However, I didn't test it.