Formula for Missing Date 1

[shelby55]

Hi

I am using Excel 2002 and I'm trying to calculate wait times for patients per month.

Col_1 is referral date, Col_2 is assessment date, Col_3 is Apr 2009, Col_4 is May 2009, Col_5 is Jun 2009 and so on.

There will be 3 scenarios to consider for calculation:

if col_1 and col_2 are blank then leave columns_3 etc will be zero

if col_1 has a date and col_2 is blank then assume col_2 is the end of the month so if the calculation is in col_3 (Apr)then the calculation will be col_1 minus Apr 30, 2009 and col_2 will be col_1 minus May 31, 2009 etc.

if col_1 and col_2 have valid dates then difference of then calculation is required for the two dates per each month column. For instance if col_1 is Apr 4 and col_2 is Apr 10 then col_3 (Apr) will be 6 days and zero in all other columns. If col_1 is Apr 4 and col_2 is May 10 then col_3 (Apr) will be 26 days and col_4 (May) will be 10 days and all other months will be zero days.

Currently I have:
=IF(AND($C2="",$B2=""),0,MAX(0,MIN(J$1,$C2)-MAX(I$1,$B2)))

but it's not letting me include the third scenario. How can I get this to work? Thanks for any and all assistance.

 

[SkipVought]

Hi,

Col_3 is Apr 2009, Col_4 is May 2009, Col_5 is Jun 2009 ...

Is that FIRST or LAST of month for those columns?

You should not have a condition for

if col_1 and col_2 are blank then leave columns_3 etc will be zero

Why even bother?

Are you saying that there are no dates earlier than 4/1/2009?

Wait times are duration values not date values. I don't get the dates across the top.

Please post sample dates and resulting values for ALL other scenarios in tabular format.


Here's how I would address it.

1. Enter ALL data in a table...
[tt]
referral date assessment date Wait (Formula)
3/5/2009 4/2/2009 28 =IF(B2="",TODAY(),B2)-A2
3/15/2009 61
3/30/2009 46
4/3/2009 5/4/2009 31
4/10/2009 5/1/2009 21
4/14/2009 4/29/2009 15
[/tt]
Then use the PivotTable wizard to report.


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Maybe this is the formula
[tt]
=IF(B2="",DATE(YEAR(TODAY()),MONTH(TODAY()),0),B2)-A2
[/tt]

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Thanks for replying.

To answer your questions, I need the third scenario to help with the other two scenarios. I don't want a pivot table because each line is one patient and I would still need the calculations to work out per month which the pivot table wouldn't give me.

The days will only be applicable to current months i.e. right now the report will only be run next for the end of May. For the purpose of this review I've added in May based on today's date, May 19.

So below is a sample of data:

referral date assessment date TtlWait Apr09 May09
4/1/2009 48 29 19
4/5/2009 44 25 19
4/6/2009 5/4/2009 28 24 4
4/10/2009 5/1/2009 21 20 1
4/14/2009 4/29/2009 15 15 0

Then at the end of each month column would be a total days' wait for that month. So Apr 09 column has to be different from May etc. because it will say if both assessment dates are blank then 0, if assessment date is blank then Apr 30 minus referral date and if assessment date not empty then split out the days per month from referral to assessment date.

Can you help? Thanks.

 

[SkipVought]

Make a table of dates starting with your min date. Its real simple.

Name your date range, Dtes

Here's your formula, assuming that the dates in row 1 are FIRST-OF-THE-MONTH dates, and using your column headings as source for named ranges (using Insert > Name > Create > Create names in TOP row...
[tt]
=SUMPRODUCT((Dtes>=D$1)*(Dtes<E$1)*(Dtes>=$A2)*(Dtes<=IF($B2="",TODAY(),$B2)))
[/tt]


Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

HI Skip

What do you mean by "assuming the dates in row 1 are first of the month dates"?

Also, I am not familiar with the use of "named ranges" AND when I try to use the method you suggested for named ranges I can an error message saying "that selection is not valid".

I appreciate all your help, thanks!

 

[shelby55]

Hi

I just thought of something else: does the named range change with additions to the worksheet? If not then this may not be the method to use because this worksheet will continue to grow as new patients added so there won't ever be an "end" to the column.

 

[SkipVought]

What do you mean by "assuming the dates in row 1 are first of the month dates"?

Exactly what is stated: The values in row 1 that represent your monthly dates, must be the first of the month -- apr 1 2009, may 1 2009, etc)

Also, I am not familiar with the use of "named ranges" AND when I try to use the method you suggested for named ranges I can an error message saying "that selection is not valid".

If you are not using Named Ranges on a regular basis in Excel, you are ignoring a GREAT feature of Excel. Use HELP and learn. Also faq68-1331.





Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Exactly what is stated: The values in row 1 that represent your monthly dates, must be the first of the month -- apr 1 2009, may 1 2009, etc)

That's what I thought you meant and that is not the case with this table. The table is a working census of patients referred to a certain program and tracking the time in days between referral and actual assessment. So any of the dates could be for any day of the month.

I will look at the link you gave me, thanks, but as stated I don't think this will be the correct method if the worksheet continues to added to. Or am I wrong on that?

 

[SkipVought]

I was NOT referring to the REFERRAL or ASSESSMENT dates in COLUMN 1.

I was referring to the MONTH dates in ROW 1.

Is that clear?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Here's your example with results from my formula, (reconstructed the sheet, including the Dtes reference ta ble, in less than 5 minutes)
[tt]
referral date assessment date TtlWait Apr-09 May-09 Jun-09
4/1/2009 50 30 20
4/5/2009 46 26 20
4/6/2009 5/4/2009 29 25 4
4/10/2009 5/1/2009 22 21 1
4/14/2009 4/29/2009 16 16 0
[/tt]

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Sorry but in your formula what column is D$1,E$1 and $A2 referring to?

Thanks.

 

[SkipVought]

I copied your example to A1.

The formula posted, resides in D2, in the Apr 2009 column, which is the first row of data.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Sorry but I can't get this to work...the cell responses even if I recreate the columns as I layed out in this query show up as o not 0 but the small letter o.

 

[SkipVought]

That is not possible with the formula I supplied. It is COUNTING; so the results are NUMERIC, not ALPHA.

Please post the formula that you are using and the cell from which you copied it.

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

The formula used in the April column (D) is:
=SUMPRODUCT((dtes>=D$1)*(dtes<E$1)*(dtes>=$A2)*(dtes<=IF($B2="",TODAY(),$B2)))

Below are the results:

Ref_date Assess_Date TtlDays April May
4/1/2009 1/0/1900 -39904 0 0
4/14/2009 4/14/2009 0 0 0
4/1/2009 4/1/2009 0 1 1
4/9/2009 1/0/1900 -39912 0 0
4/3/2009 4/8/2009 5 0 0
4/17/2009 5/4/2009 17 0 0
4/13/2009 1/0/1900 -39916 0 0
4/14/2009 4/14/2009 0 0 0
4/20/2009 5/6/2009 16 0 0
4/17/2009 4/27/2009 10 0 0
4/20/2009 4/20/2009 0 0 0
4/14/2009 1/0/1900 -39917 0 0
4/7/2009 4/16/2009 9 0 0
4/2/2009 4/7/2009 5 0 0
4/2/2009 4/7/2009 5 0 0
4/16/2009 1/0/1900 -39919 0 0
4/8/2009 4/13/2009 5 0 0
4/9/2009 4/23/2009 14 0 0
4/17/2009 4/30/2009 13 0 0
4/3/2009 4/14/2009 11 0 0
4/13/2009 4/13/2009 0 0 0
4/15/2009 4/28/2009 13 0 0

Thanks for your help!

 

[SkipVought]

What are the ACTUAL VALUES in the Formula Bar in the cells with April May?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

The actual values are the first of the month dates i.e. 4/1/09, 5/1/09. I ensured that these columns were date cells.

For the named range I couldn't get the tool bar method to work but I selected the top row (for the dates only) and in the cell beside the function key I typed in "dtes". I ensured this was a named range by looking under insert, name, define and looking up dtes to ensure the range was correct.

 

[SkipVought]

Works just FINE on my sheet.

YOUR problem is that the dates in ROW 1 are not REAL FIRST-OF-THE-MONTH DATES, as I instructed you have. Consequently you have ZEROS, contrary to your previous post.

Here are a protion of m results, with your most recent example...
[tt]
referral date assessment date TtlWait Apr-09 May-09
4/1/2009 50 30 20
4/14/2009 4/14/2009 1 1 0
4/1/2009 4/1/2009 1 1 0
4/9/2009 42 22 20
[/tt]

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I ensured this was a named range by looking under insert, name, define and looking up dtes to ensure the range was correct.

When you select Dtes from the Name Box, does it select your entire range of dates in the Dtes Table?

Do the dates in the Dtes range, start with 4/1/2009 and continue by days (4/2/2009, 4/3/2009 etc) until then last date in May 2009?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}