Calculating Days Across Fiscal Years

[shelby55]

Hi

I have a patient database for patients that could be in hospital over more than one fiscal year. A fiscal year is April 1 to March 31. I have 3 years worth of data and in fact some of these patients may not be discharged by the end date of the period (March 31, 2009).

I have the admit date and the discharge date (if there is one) and wish to create a column of days that occur over 2006/07, 2007/08 and 2008/09. Admission date doesn't count as a day so April 1, 2006 to March 31, 2007 would be 364 days.

Below is some example data and what I would expect to see:

[tt]AdmitDate DisDate 2006 2007 2008
Mar 16, 2007 May 6, 2006 33 0 0
Apr 1, 2006 May 6, 2007 364 36 0
Sep 5, 2008 0 0 207[/tt]

So what formulae would I use to accomplish this? Thanks.

 

[SkipVought]

Hi,

You ought to still have the sheet of individual dates convering your earliest and latest dates.

Use the YEAR of the two dates to compare with the YEAR in row 1. Then calculate the days, from the sheet ocntaining the individual dates.

Poat back with a formula that you have tried to use, if you need help.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi

Not sure what you're referring to, Skip? Please elaborate.

 

[Gavona]

Above your Year headings insert a row with the date of the first day in the financial year. Actually leave a blank row between your headings and these dates.

Formula along the lines of:
=Min(DisDate,StartOfNextFinYr)-Max(StartOfFinYr,AdmitDate)

That will work for anything that has some days in the year in question but for cases where the admission ois after the start of the FinYr you need to add a formula:
=if(OR(DisDate<StartOfFinYr,AdmitDate>EndOfFinYr),"",formula)

Adjust by adding / subtracting 1 from dates to meet your more specific requirements

Gavin

 

[xwb]

Is this in excel or access? The solution might be different depending on what you're using.

 

[Gavona]

Oops, slight error. I think you want:

=if(OR(DisDate<StartOfFinYr,AdmitDate>[red]=[/red]StartOfNextFinYr),"",Min(DisDate,StartOfNextFinYr[red]-1[/red])-Max(StartOfFinYr,AdmitDate))

Just substitute in the appropriate cell references

Gavin

 

[shelby55]

Hi Gavin

But how do I combine the two formulae so that I get the days I want per year?

Thanks.

 

[Gavona]

Good point xwb. The logic of my solution should be ok but I illogically assumed Excel.

Gavin

 

[Gavona]

shelby55, I guess you ARE using Excel.

I think I answered in our cross post.
The start of year cell references should have a dollar sign before the row, e.g. E$1 so when you copy down it always refers to the same row.
The AdmitDate and DisDate references should have a dollar before the column letter, e.g. $A4 spo that when you copy across it always refers to the same column.

Gavin

 

[dallen43]

To get the number of days if the patient hasn't been discharged (as in the last example), you would need the formula as follows:

=IF(OR(and(DisDate<StartOfFinYr,DisDate<>""),AdmitDate>=StartOfNextFinYr),"",MIN(DisDate,StartOfNextFinYr-1)-MAX(StartOfFinYr,AdmitDate))

Thanks,
Deb