EDATE WITH CONDITIONS

[BarbO404717]

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

[#2 · Accepted solution]

@BarbO404717 

Literally

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

[#3]

Thanks Sergei. Does your formula cover both scenarios regarding cell M21?
Barb

[#4]

OMG! It worked! Thank you so much. Is there any chance you can describe what you did in prose/text? Thanks, Barb

[#5]

@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.

[#6]

@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.

[#7]

Thank you!!