waiting times for different conditions

[sophy_1402]

Hi there,

I am creating a database for calculating waiting times for patients. I would like to separate out inpatient (IP) and outpatient (OP) waiting times and calculate the times based on different conditions. I have attached an example database.

The different conditions are:

1. If the patient is an IP (column B) and has both a referral date (column C) and an appointment date (column E), then their waiting time (column F) is the time between the 2 dates (see Robin Hood). If the IP has a referral date, but not yet an appointment date then the waiting time is from the referral date to present time (Jon Carrot). If the IP does not have a referral date or an appt date then I would like their waiting time cell to remain blank (Susie Bone). And finally, in the IP waiting time column I only want waiting times for the IP's, for any OP's I would like the cells in this column to remain blank.

2. If the patient is an OP and has both an opt in date (column D) and an appointment date, then their waiting time is the time between the 2 dates (Jane Kane). If the OP has an opt in date, but not yet an appointment date then the waiting time is from the referral date to present time (Des Line) If the OP does not have an opt in date or an appt date then I would like their waiting time cell to remain blank. (Chip Smith). And finally, in the OP waiting time column I only want waiting times for the OP's, for any IP's I would like the cells in this column to remain blank.

Thanks very much

Sophy

[JBeaucaire]

Try these formulas. In F2 and then copied down:

=IF($B2="IP", CHOOSE(COUNT($C2:$E2)+1, "", TODAY()-$C2, $E2-$C2), "")


In G2 and then copied down:

=IF($B2="OP", CHOOSE(COUNT($C2:$E2)+1, "", "", TODAY()-$C2, $E2-$D2), "")

[realniceguy5000]

Here is another method,

See Attachment

[sophy_1402]

That's great, thanks very much. The formula's are working well, there are a couple of things I would like to add in and wonder if you could help.
Firstly when a patient (either IP or OP) does not have a referral date, opt in or appt yet the waiting time is coming up 0. I would like this to remain blank so the average waiting times are not miscalculated. Also, is it at all possible to flag up any patients who do not have an appt date yet (i.e. those who are on the waiting list). Perhaps in a differerent colour until they are given an appt date.

thanks so much for your help

Sophy

[JBeaucaire]

I edited formulas in post #2 to take out the zeros.... you could've probably spotted that.

You can use conditional formatting to change the color of one cell based on the contents or lack of contents in another cell.

[sophy_1402]

Thanks thats great, one final things where do I add the /7 if I want the waiting times to be in weeks instead of days. Thanks alot fo your help!

[JBeaucaire]

Each of the small formulas has to have that added individually:

F2: =IF($B2="IP", CHOOSE(COUNT($C2:$E2)+1, "", (TODAY()-$C2)/7, ($E2-$C2)/7), "")
G2: =IF($B2="OP", CHOOSE(COUNT($C2:$E2)+1, "", "", (TODAY()-$C2)/7, ($E2-$D2)/7), "")

[sophy_1402]

Thanks very much for all of your help.

[JBeaucaire]

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.