If formula for waiting times

**sophy_1402** · 09-02-2011, 06:18 AM

Hi there,

I would like a formula in column L to calculate the waiting time of patients who are on the waiting list (Column H with a 1). There waiting time is todays date - their opt in date (col F). And if they are not on the waiting list then I would like the cell to remain blank.

Thanks for the help

Sophy

**Andrew-R** · 09-02-2011, 06:21 AM

In cell L4 put the formula =IF(H4<>1,"",TODAY()-F4) and copy down.

**PaulG2011** · 09-02-2011, 06:23 AM

Hi Soph,

So youre saying that if column H is a one, you would like column L to be the number of days difference between today and the "opt in date" in column F.

If this is the case all you need to do is having the following formula in cell L4 and copy it down?

=IF(H4=1,NOW()-F4,"")

I.e. If H4 is 1, then subtract the date in F4 from todays date, otherwise make the cell empty.

Let me know if this helps - if not we can try again!

Edit: Andrew beat me to it!

Anyway, just noticed that your column is headed in units of "weeks", so you could adapt the formula by having something like:

=IF(H4=1,ROUND((NOW()-F4)/7,0),"") to round to the nearest number of weeks (i.e. 10 days is 1 week) or

=IF(H5=1,ROUNDUP((NOW()-F5)/7,0),"") to round UP to the next number of whole weeks.

Happy Excelling!

Kind regards,

Paul