Date range calculation, including partial months

[Tskutnik]

Trying to get a formula or function for calculating profit for each month between selected dates. Partial month calculations are required.

Source Data (record) examples:
· Contract1: Startdate = 6/30/12; EndDate = 9/22/12; DailyProfit = $500; MonthlyProfit = $9500
· Contract2: Startdate = 7/7/15; EndDate = 12/17/12; DailyProfit = $600; MonthlyProfit = $11400
· Contract3: StartDate = 8/1/12; EndDate = 6/30/13; DailyProfit = $350; MonthlyProfit = $6650

User Query #1:
Notes:
· ReportStartDate will always be entered as the first day of the month;
· ReportEndDate will always be entered as the last day of the month
· The report will always show 6 consecutive months

User selects
· ReportStartDate = 7/1/12;
· ReportEndDate = 12/31/12

Notable conditions:
· The calculation for Contract 1 will have a partial month profit calculated for September 2012. The other months can show the MonthlyProfit value, with October and December being $0.
· The calculation for Contract 2 will have a partial month profit calculated for July 2012 and December 2012. The other months can show the MonthlyProfit value.
· The calculation for Contract 3 will have no partial months since the StartDate is on a month beginning, and the ReportEndDate is prior to the EndDate. July 2012 will show $0.

Request:
· I either need a super complicated IIF statement for 6 fixed columns in a query (one column for each month) or a smart function, neither of which I can do.

Any help is really appreciated.

 

[CJ_London]

I would do this something like this which you can then convert to a crosstab

[COLOR=green]Contract   StartDate   EndDate[/COLOR]   [COLOR=sandybrown]Month[/COLOR]  [COLOR=deepskyblue]Profit[/COLOR]

The green columns would be rows, the orange column and blue the value.

However before doing that - what is the calculation you require - you have two profit figures, daily and monthly where the monthly figure is 19 times the daily figure - if this is always the case, why do you need both?

So you could have for september, contract 1
22 days*$500
$9500/30 days in September*22 days
$9500*12 months/365 days*22 days
etc

Where does the 19 days come from? if working days, how do you define them - Mon-Fri? in which case how do you account for public holidays etc?

 

[Tskutnik]

The 19 days [MonthlyProfit] is not critical, I just have it for the months that will be full to make the reporting math easier.

You can use the daily and calculate up from there. I assume a month is always 19 working days since this is an average. If we can do actual working days for each month that would of course be better, but I did not need it to be that exact.

 

[CJ_London]

OK so for september contract 1, what value to you want to see and how would you calculate it?

e.g. dailyrate * number of days in month*19/30?

so $500*22*19/30=$6967

you are concerned about the number of iff's you may have but this is because you are choosing different ways to calculate - one basis using the monthly figure and one the daily with further complications when a contract starts on the first day of the month or ends on the last day of the month.

Why not go for a standard basis of calculation which I would suggest should be $9500*12 months/365 days*22 days

 

[Tskutnik]

The 19 vs. 22 days part does not matter much to me, so either way is fine. Considering what we are using this for it does not matter, so either way you like.
I am using 19 because people take days off and are sick so over the year it averages to about 19 or 20. ((5 days / week * 52 weeks a year - 10 holidays - 10 vacation days, - 5 sick days ) / 12 months = 19.6

For the full month of September you would take the daily and multiply it by the working days we are using, be it 19, 22, or the actual for September.

 

[CJ_London]

I'm not sure I understand your explanation but I would suggest you want SQL along the following lines.

PARAMETERS [Enter Month (mm/dd/yyyy):] DateTime;
TRANSFORM Sum([Monthprofit]*IIf([startdate]<[monthstart] And [enddate]>=DateAdd("m",1,[monthstart]),1,IIf(Format([startdate],"yyyymm")=Format([monthstart],"yyyymm"),(30-Day([startdate]))/30,IIf(Format([enddate],"yyyymm")=Format([monthstart],"yyyymm"),Day([enddate])/30,0)))) AS Profit
SELECT Table1.ID, Table1.startdate, Table1.enddate
FROM Table1, tblMonths
WHERE (((Table1.startdate)<=DateAdd("d",-1,DateAdd("m",6,[monthstart]))) AND ((Table1.enddate)>=[monthstart]) AND ((tblMonths.Monthstart) Between [Enter Month (mm/dd/yyyy):] And DateAdd("d",-1,DateAdd("m",6,[Enter Month (mm/dd/yyyy):]))))
GROUP BY Table1.ID, Table1.startdate, Table1.enddate
ORDER BY Format([Monthstart],"yyyymm")
PIVOT Format([Monthstart],"yyyymm")

This requires an additional table which I called tblMonths which has a single date type field I called monthstart which needs to be populated as follows (note this is UK format):
tblMonths
Monthstart
01/06/2013
01/07/2013
01/08/2013
01/09/2013
01/10/2013
01/11/2013
01/12/2013
01/01/2014
01/02/2014


This produces an output like this

[FONT=Calibri][COLOR=#000000]ID [/COLOR][/FONT][FONT=Calibri][COLOR=#000000]Startdate [/COLOR][/FONT][FONT=Calibri][COLOR=#000000]enddate [/COLOR][/FONT][FONT=Calibri][COLOR=#000000]201308 [/COLOR][/FONT][FONT=Calibri][COLOR=#000000]201309 [/COLOR][/FONT][FONT=Calibri][COLOR=#000000]201310 [/COLOR][/FONT][FONT=Calibri][COLOR=#000000]201311 [/COLOR][/FONT][FONT=Calibri][COLOR=#000000]201312 [/COLOR][/FONT][FONT=Calibri][COLOR=#000000]201401[/COLOR][/FONT][/FONT][/COLOR]
[COLOR=#000000][FONT=Calibri][B][FONT=Calibri][COLOR=#000000]1 [/COLOR][/FONT][/B][B][FONT=Calibri][COLOR=#000000]22/07/2013 [/COLOR][/FONT][/B][B][FONT=Calibri][COLOR=#000000]05/01/2014 [/COLOR][/FONT][/B][B][FONT=Calibri][COLOR=#000000]10000 [/COLOR][/FONT][/B][B][FONT=Calibri][COLOR=#000000]10000 [/COLOR][/FONT][/B][B][FONT=Calibri][COLOR=#000000]10000 [/COLOR][/FONT][/B][B][FONT=Calibri][COLOR=#000000]10000 [/COLOR][/FONT][/B][B][FONT=Calibri][COLOR=#000000]10000 [/COLOR][/FONT][/B][B][FONT=Calibri][COLOR=#000000]1666.66666666667[/COLOR][/FONT][/B]

Sorry - formatting not so hot!

 

[Tskutnik]

Thanks for this. I see where you are going. Makes sense. I'll give it a try and see what happens.