Counting Days in a range by Month

[Sade]

Ok, so I am collecting Admit Date and Term Date for each record. I want to count the number of days by month between those dates. So for, Admit Date = 01/20/11 and Term Date = 02/05/11, I want the following output:

Jan, 11 = 12 days ... (31 - 20 + 1 to include the first day)
Feb, 11 = 5 days

I've been using the Datediff function, and it works fine but I'm having trouble accounting for all possibilities. ie: how to identify the jump between months, what if the range spans 3+ months, how to account for leap year, if the Term Date is blank, how can I get the formula to use the date() function to use the current date.

Is there an easy way to account for all this without having to code out every possible combination and hard code the number of days in each month?

Thanx

 

[xenou]

Difference between dates is easiest with simple arithmetic: Date2 - Date1.

If one of the dates is Null, then there's no logical answer and you'll get Null as the result. The only way around that would be to supply a default value such as the current date.

 

[Phildaburn]

You could create a temporary table with a row for each date between Admit Date and Term Date where the first record is equal to Admit Date and the last record is equal to Term Date. In the first field of this temporary table, I would put the full date. In the 2nd column, I would put the date in the format of 2011-01, 2011-02, etc and in the 3nd column, simply a 1. Then, you can write a query against this temporary table grouping the data on the reformatted date and summing the 1's. Beginning date and ending date won't matter.

To step through the dates using vba code use a variable that starts out as being equal to your Admit Date (vBegDate). Insert the rest of the code to populate your temporary table, then before you loop, increment your variable by 1 (vBegDate = vBegDate + 1).

Hope that makes sense and helps.
Phil...

 

[Sade]

Thanx Phil, I like it! It took some time to realize I needed the "#"'s around the date variable in my SQL statement, but it all works now. THANX!

 

[Phildaburn]

Excellent news! Congrats!

My pleasure to have helped.

Phil...