date diference function + label

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Andy - 27 Jul 2006 04:23 GMT

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

Reply to this Message

jimdilger@gmail.com - 27 Jul 2006 05:45 GMT

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

> 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
[quoted text clipped - 4 lines]
>
> Andy

Reply to this Message

Andy - 28 Jul 2006 01:39 GMT

> 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

Reply to this Message

jimdilger@gmail.com - 28 Jul 2006 11:20 GMT

You're welcome!

> Thanks for getting me past the block!
>
> Andy

Reply to this Message

Mac Forum / Applications / Excel / July 2006

date diference function + label