I am stumped on a date formula

[reportminder]

Hi - I am stumped. I need to split and count days between a start date and end date. These dates can happen anytime during the month, but I need to split between 3 months. So the start month date maybe be January 14 and the end date might be March 1. Using this scenario - Month 1 would have a count of 17 days. Month 2 would have a count of 28 days (unless a leap year, then 29) and Month 3 would have a count of 1 day.

How do I go about this?

 

[BettyJ]

Try inserting a group on the Date field. While doing so, in the Group Expert window click on Options button and select for each month under [tt]The section to be printed[/tt]

Then,
Insert --> Summary -->
[tt]Choose the field to summarize[/tt]
YourDateField[tt]
Calculate this summary[/tt]
Count[tt]
Summary Location[/tt]
Group#1:YourDateField-A

 

[reportminder]

Thanks for that - that doesn't quite answer it.

I need 3 columns 1 Month (count of days in this month), 2 Month (count of days in this month), 3 Month (count of days in this month)

So the start month date maybe be January 14 and the end date might be March 1. Using this scenario - Month 1 would have a count of 17 days. Month 2 would have a count of 28 days (unless a leap year, then 29) and Month 3 would have a count of 1 day.

1 Month 2 Month 3 Month
17 28 1

Thanks!

 

[BettyJ]

Do you want to display it in two rows as you wrote
1 Month 2 Month 3 Month
17 28 1

If it is possible for you to display the above results in the report footer section, try the following:
To get the first line, you can write a formula to concatenate the Special Field, GroupNumber with the word Month from the group footers.

For the second line, write a formula to concatenate the results you got from what I posted earlier. Insert another report footer section and display the result there.

If you need help with the formulas let me know.

 

[reportminder]

Thanks again its not quite what I am after, I need to list the months (not like month 1 = January). It doesn't matter what month it starts or ends in. But I need to count the days in each month that the record is in but not past the 3 month span. So from the start date - I need to assign January as month 1, and then February as month 2 and March as month 3 and then count how many days are in that month that the record is in. But the span of records could start in April as well and that would be month 1.

I know once I can get a month assigned I can use datediff to get the difference of the start date and end date. But I need just the 3 columns as Month 1, Month 2 and Month 3 generically.

Does that make sense?

 

[pmax9999]

If I understand the situation correctly, the start date to the end date will always span 3 calendar months, and you require the number of days in each of those calendar months.

To achieve this you will require 3 formulas, 1 for each month a follows:

[Code {@Mth1}]
DateSerial(Year({Table.StartDate}), Month({Table.StartDate}) + 1, 1) - 1 - {Table.StartDate}
[/Code]

[Code {@Mth2}]
DateSerial(Year({Table.StartDate}), Month({Table.StartDate}) + 2, 1) - DateSerial(Year({Table.StartDate}), Month({Table.StartDate}) + 1, 1)
[/Code]

[Code {@Mth3}]
{Table.EndDate} - DateSerial(Year({Table.StartDate}), Month({Table.StartDate}) + 2, 1) + 1
[/Code]

Hope this helps.

Cheers
Pete

 

[reportminder]

Thank you Pete. No they won't always span 3 months, but theoretically it could. They do want a 3 month summary (a summary for each month). At any time the end date could be filled in during that 3 months, or it could stay null.

SO would I have to check for the end date to be null in each instance, right - am I thinking right?

 

[pmax9999]

Yes, you would need to check for nulls in each formula and where the end date is null, also check against the current date. In addition, if it could span more than 3 months, you would need 1 formula for every month. Also, if it could span less than 3 months, you would need to check for that as well.

This will get pretty complex I think. If you need further assistance, please explain all of the possible combinations so we can endeavour to cover them all.

Cheers
Pete

 

[reportminder]

Thanks again. Yes it covers all months. A start date can occur in any given month and any day. But it won't go over a 3 month span, it would have to have a renewal start date.

 

[pmax9999]

OK, I may have made this more complicated than it needs to be but without a full understanding of what is and isn't possible in terms of the data I have endeavoured to cover every possibility (every one I can think of anyway). The one assumption that I have made is that only days up to the date the report is run are counted. In other words, if the record has an end date of 25 September, when run on 12 September will only include 12 days for the relevant month.

The three formulas now look like this:

[Code {@Mth1}]
WhilePrintingRecords;
Local DateVar END;


If Isnull({Table.EndDate})
Then If Month({Table.StartDate}) = Month(CurrentDate)
Then If {Table.StartDate} > CurrentDate
Then END := Date({Table.StartDate})
Else END := CurrentDate
Else END := DateSerial(Year({Table.StartDate}), Month({Table.StartDate})+1,1) -1
Else
If Month({Table.StartDate}) = Month(CurrentDate)
Then If {Table.EndDate} > CurrentDate
Then END := CurrentDate
Else END := Date({Table.EndDate})
Else
If Month({Table.EndDate}) = Month({Table.StartDate})
Then If {Table.EndDate} > CurrentDate
Then END := CurrentDate
Else END := Date({Table.EndDate})

Else END := DateSerial(Year({Table.StartDate}), Month({Table.StartDate})+1,1) -1;

END - {Table.StartDate}

[/Code]

[Code {@Mth2}]
If Isnull({Table.EndDate})
Then If Month({Table.StartDate}) = Month(CurrentDate)
Then 0
Else
If Month({Table.StartDate}) = Month(CurrentDate) - 1
Then CurrentDate - Minimum (MonthToDate) + 1
Else DateSerial(Year({Table.StartDate}), Month({Table.StartDate}) + 2, 1) - DateSerial(Year({Table.StartDate}), Month({Table.StartDate}) + 1, 1)
Else
If Month({Table.StartDate}) = Month({Table.EndDate}) or
Month({Table.StartDate}) = Month(CurrentDate)
Then 0
Else
If Month({Table.EndDate}) - Month({Table.StartDate}) = 1
Then Day({Table.EndDate})
Else
If {Table.EndDate} <= CurrentDate
Then DateSerial(Year({Table.StartDate}), Month({Table.StartDate}) + 2, 1) - DateSerial(Year({Table.StartDate}), Month({Table.StartDate}) + 1, 1)
Else
If Month({Table.StartDate}) = Month(CurrentDate) - 1
Then CurrentDate - Minimum(MonthToDate)
Else DateSerial(Year({Table.StartDate}), Month({Table.StartDate}) + 2, 1) - DateSerial(Year({Table.StartDate}), Month({Table.StartDate}) + 1, 1)
[/Code]

[Code {@Mth3}]
If Isnull({Table.EndDate})
Then If Month({Table.StartDate}) in [Month(CurrentDate) to Month(CurrentDate) -1]
Then 0
Else
If Month({Table.StartDate}) = Month(CurrentDate) - 2
Then CurrentDate - Minimum (MonthToDate) + 1
Else 0
Else
If Month({Table.EndDate}) - Month({Table.StartDate}) < 2
Then 0
Else If {Table.EndDate} > CurrentDate
Then CurrentDate - DateSerial(Year({Table.StartDate}), Month({Table.StartDate}) + 2, 1) + 1
Else {Table.EndDate} - DateSerial(Year({Table.StartDate}), Month({Table.StartDate}) + 2, 1) + 1
[/Code]

I have tested the code pretty extensively so I believe it works for the sample data I created. If I have misinterpreted the requirements then it obviously won't return the correct results. With the benefit of a thorough understanding of data (date) combinations it may well be possible to simplify the code.

Hope this helps.

Cheers
Pete

 

[reportminder]

Let me try this.

 

[reportminder]

This works except it does not count for 1 day - if the last day of the month in any of the months - it should count as one day. How do I solve for that? Thanks again for the help!

 

[pmax9999]

Please explain what you mean, providing examples. I interpreted your last post as meaning that if the end date was the last day of a month that it was not being counted. I retested and I believe it is working.

Cheers
Pete

 

[reportminder]

If the start date was 6/30/2014 and the end date was also 6/30.2014 - the count is 0, not 1. So I am trying to figure out why it is 0 instead of 1. Is it because it is a datetime datatype?

 

[pmax9999]

It is because the number of days from 30 June to 30 June is zero. It is only 1 day on 1 July.

Pete

 

[Charliy]

t get the number of actual days in a range: ({end date} - {start date})+1