Date count (Excel 97)

Topic

New Reply

Is there a command in Excel that will tell me the number of days within a month for a date range? Example: Date Admitted: 10/8/01 Date released: 12/13/01
I want to know how many days in Oct = 22, Nov = 30, Dec = 13 that this person was under our care per month.

Any suggestions???

Thanks,
Deborah

Reply | Quote

Replies

Deborah, see the attachment which uses the =DATEDIF(,,) function to show:

1. The number of stay days in the admit month
2. The number of stay days in the discharge month if the discharge month is not the admit month
3. The total number of stay days in the intermediate months between admit and discharge dates if any.
4. The total number of days stay.
5. A check to see that the count is internally correct.

I did not test this beyond the four sample dates shown , so please conduct your own accuracy tests to ensure I haven’t missed anything.

If you need to break out each intermediate month, things will need to be more complex; all I have done here is add a line for the total number of stay days in the intermediate months between the admit and discharge month, not a list of stay days by each intermediate month.

The attachment also provides Chip Pearson’s documentation of the =DATEDIF(,,) Function and a link to his great website.

HTH.

Reply | Quote

There is no specific command (function) for what you want, but a series of formulae should work, one for each month under consideration.

If the start date is in B2, the end date in C2, and the first of Jan in E1, the following formula will th enumber of days for January. The formula can then be copied for each of the other months + 1.

=IF(F$1-1<$B2,0,DATEDIF(MAX(E$1,$B2),MIN(F$1-1,$C2),"d")+1)

A worksheet is attached which might help see what I am doing, and maybe somebody will come up with a clever way of doing the same thing.

Andrew C

Reply | Quote

Andrew,

I entered the formula and it works great. There is one problem, when the date is greater than the end date, I get a #num! error message. I have tried to make some changes but to not avail. Can you provide some suggestions?? I am using dates through the end of the year – I would like a 0 in the fields instead of the #num! message.

Thanks,
Deborah

Reply | Quote

Can you explain what you mean by “when the date is greater than the end date”? There are only admit and discharge dates, Andrew calls them In and Out.

BTW, Andrew, your formulas do not return correct results for stays which begin in one year and are discharged next year, such as an In of 12/20/01 and an Out of 1/10/02. But your layout is better than my approach.

Reply | Quote

I was referring to the date at the top such as Feb01 and then the out date. I have read in the Pearson Software web site that the formula will return the #num! if the month (feb01) is greater than the out date. I should be able to add something to this formula to produce an 0 or the words “out of range” but what ????

Thanks,
Deborah

Reply | Quote

Andrew (where are you, Andrew?) has used the “d” argument to =DATEDIF(), so the only time the formula should error out is if the out date is chronologically the same day as or before the in date, which can’t happen in reality, though it could be entered in error.

You could handle this by surrounding the existing formulas with:

=IF(C2>B2,existing_formula,”Invalid Dates”)

(I’ll try to work on this in more depth later.)

Reply | Quote

Deborah, you are quite correct – sorry. The following should fix your problem

=IF(MAX(E$1,$B2)>MIN(F$1-1,$C2),"",DATEDIF(MAX(E$1,$B2),MIN(F$1-1,$C2),"d")+1)

Sample attached. For clarity if a given month has no value I have left it blank. If ypu want to use 0, then replace “” with 0 in the formula.

John, the formula should work across years but I probably did not make it clear enough that it is dependant on the top row extending at least one month beyond the discharge date.

Andrew C

Reply | Quote

Oh, I see, there have to be as many month columns as months of stay.

Reply | Quote

Sorry, forgot to re-attach the file after the Preview.

Here it is

Reply | Quote

Works Great!!!

THANKS…..

Deborah

Reply | Quote