date diference function + label

[Andy]

I am trying to have a cell calculate the time elapsed between visits by
a patient for post-op surgical visits. Using the DATEDIF function is
easy enough, but I would like the output to be clear about what this
is. Ideally, it would display X days if < 7 days since surgery, X weeks
if it has been 1-3 weeks since surgery, X months if months have passed
since the surgery date. Any suggestions?

Andy

 

[jimdilger]

Andy,

I don't think that the DATEDIF function can do this easily. However,
here is a way.
If cell A1 has the date of the last visit and cell B1 has today's date
then use nested IF statements like this:

=IF(B1-A1<7,"It has been "&ROUND(B1-A1,0)&" days since
surgery",IF(B1-A1<22,"it has been "&ROUND((B1-A1)/7,1)&" weeks since
surgery","it has been "&ROUND((B1-A1)/30,1)&" months since surgery"))

If you don't like having the decimals, eg 3.4 weeks, change the 1 to a
0 in the second argument of the ROUND function.
A little more work is required to have it display "week" instead of
"weeks" when it is 1 week.

Jim

 

[Andy]

Andy,

I don't think that the DATEDIF function can do this easily. However,
here is a way. . . .
Jim

Thanks Jim - You are correct - that should work. I completely blanked
on using nested if functions. Sometimes I just get set in the "This is
the way I'm gonna do it mode."

Thanks for getting me past the block!

Andy

 

[jimdilger]

You're welcome!