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.

 

[shelby55]

Hi

D = 4/1/2009, E = 5/1/2009, F = 6/1/2009 etc. So it only has the initial date and it's not the entire range of 4/1/2009 to 4/30/2009.

Is there any way you can send me your sample Excel sheet that you got to work? Am I "allowed" to give you my e-mail address?

Thanks.

 

[SkipVought]

Yes, the Dates in Row 1 are only the first of the month.

But you must have a separate table of dates named Dtes that looks liks this...
[tt]
Dtes
4/1/2009
4/2/2009
4/3/2009
4/4/2009
4/5/2009
4/6/2009
4/7/2009
4/8/2009
4/9/2009
4/10/2009
4/11/2009
4/12/2009
4/13/2009
4/14/2009
... etc ...
5/31/2009
[/tt]

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

 

[SkipVought]

Here's the concept.

You create a table that has ALL THE DATES in it, from the very first month's dates to the very last month's dates for the dates that you want to calculate.

The SUMPRODUCT function then counts the dates in that table between the dates that you are referencing AND the dates related to the month (column).

That's all that's happening.

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

 

[shelby55]

Hi

I still can't get this to work Skip. Can you send me a file?

 

[SkipVought]

ii36250

at

bellhelicopter

dot

textron

dot

com

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

 

[SkipVought]

Shelby,

One of the advantages of using a date range, starting with the minimum lookup date, thru the maximum lookup date, for each date in between, is that you could also tag the WORK DAYS in the next column with a 1 and NON-WORK DAYS with a ZERO and sum the workdays, rather than just counting the days.

Just another option that is available.

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

 

[SkipVought]

Shelby,

I saw your workbook and I'd like to make some comments and I hope that you will not take this as a personal attack, but rather constructive criticism. Your workbook is really, in many ways, typical of how a lot of spreadsheet users design workbooks. So you are definitely, not alone in this caricature.

The Master sheet is designed to be a report and not a table.
[tt]
1. Two rows of descriptive data above the table headings.
2. Descriptive data is not isolated from the table.
3. All table columns do not have headings.
4. Aggregations and other data below the table.
[/tt]
Tables are plain but very functional. They are designed to be used to store data and analyze data. faq68-5184.

Reports are pretty and tell a story or convey information in an understandable way.

Unless you want to spend a lot of time maintaining your workbook, do not store data in a report.

So here's what is happening in this workbook. The Master sheet REPORT is used to store the data. Summary1 is referencing each DATA cell in two key columns: referral date and assessment date (like this =MASTER!C4). Here are the problems with that. You have SOME rows on the Master, where there is no data YET. So in the Summary, you have to programs for that WASTED & MEANINGLESS data. And what about when you ADD new rows of data to the Master, that is BEYOND the references that you have entered on the Summary? (BTW, there are more than ONE summary to have to worry about keeping in sync like this) This will become you life; an Excel MAINTENANCE albatross around your neck.

Use something like this as a general workbook design template.
[tt]
1. Each Table on a separate sheet, starting in A1, with one row of unique headings and contiguous rows of data. No other data to the RIGHT or BELOW the table.

2. Each report, which is generally some kind of aggregation, could be on one or more sheets, generated by the PivotTable Wizard, Chart Wizard, MS Query, lookup & aggregation functions or combinations thereof.

3. Where a report is identical in structure except for one or more parameters (like budget by time period, manager, etc) rather than generating multiple reports, consider using a simple drop down control to filter the data in your report. One report is a WHOLE lot easier to maintain than several dozen!


And now I d
e
s
c
end. Ahhh_hhhhhh

I feel better now.

[/tt]


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

 

[shelby55]

Hi Skip

I thank you so much for your assistance with this project. I totally appreciate your comments but I'm just not sure what you are suggesting for me to change to optimize my document.

If you can give me specifics that would be great!