Calculating months between dates

[Steven Swallow]

Although, not quite as simple as that.

I have a pretty straightforward recordset, with fields including "Start_Date", "End_Date" and "Revenue". "Start_Date" is always the first of a month and "End_Date" is always the last day of a month. The space between "Start_Date" and "End_Date" can be anything from 1 to 3 years.

I need to calculate the revenue per year, so if the "Start_Date" is 1/4/10 and the "End_Date" is 31/7/12, I need to calculate the revenue for 2010 (from 1/4/10 to 31/12/10), for 2011 (the whole year), and for 2012 (1/1/12 to 31/7/12).

In this case the 2010 calculation would give me 9 months, 2011 would give me 12 months and 2012 would give me 7 months but my main question is how to do this...I have no idea how to get these answers out of Filemaker...this is my first foray into calculated fields.

I know I then need to divide the total revenue by the number of months the entire contract runs for (so I can add the above 3 together for this I guess), then multiply that by the number of months calculated for that particular year...again, don't know how although I feel the complicated bit is getting the number of months in a year that a contract runs.

Hope you can help!

[comment]

The problem with your question is that you could end up with up to three separate results - and nowhere to put them.

[Steven Swallow]

I don't see how I could end up with 3 results. I gave examples of expected answers, I just don't know how to get them.

If I can get the calculation working, then I do have somewhere to put the answers. I have prepared fields ready and waiting but since I don't know what calculation to give those fields, they're just set up as number fields at the moment.

I have set up fields called "Months_In_YYYY" and "Revenue_In_YYYY" where "YYYY" is the year. "Months_In_YYYY" will (hopefully) store the number of months in a particular year that the contract runs, allowing me to then work out "Revenue_In_YYYY" for myself.

I'm not entirely sure this is the best approach to be honest, but I can only work with the data I've got and I'm trying to create a spreadsheet that has grown out of control into a much more user-friendly database.

I'm not really sure whether this is the right approach though. Like I say, first foray into calculated fields.

[comment]

I don't see how I could end up with 3 results.

Perhaps I misunderstood your original post:

In this case the 2010 calculation would give me 9 months, 2011 would give me 12 months and 2012 would give me 7 months

The reason I raise this is that it's not clear (to me, at least) what do you intend to do with these results once you get them. Even if you use 3 fields (or a repeating field) to hold them, they cannot be summarized easily, since each record can have a different year in the same column.

I have set up fields called "Months_In_YYYY" and "Revenue_In_YYYY" where "YYYY" is the year.

That doesn't sound like a good approach. Time marches on and you'll find yourself running out of fields periodically.

---

BTW, this is hardly a good choice of a problem if you are only starting with calculations,

Edited August 13, 2012 by comment

[Steven Swallow]

OK, let's try explaining it differently.

I have three fields, and this is all I have to work with from a legacy spreadsheet.

Start_Date - This holds the start date in a DD/MM/YYYY format of a sponsor & advertising contract for a publication we create.

End_Date - This holds the end date of said contract in the same format.

Revenue - This holds monies received for the entirety of the contract. It is a number field.

What I need to do (for our accounts department) is calculate how much revenue is split over each year of the contract.

I figured I would need to first calculate how many months the contract runs in each year (and store the answer in a field "Month_In_YYYY"). I though that once I've got this tricky part out of the way, I could then simply divide the revenue by the number of months the contract runs in total (which also need to be calculated and stored), then multiply by the number stored in "Months_In_YYYY". This answer would then be stored in "Revenue_In_YYYY" giving me exactly what I want.

All this given that contracts run strictly from the 1st of a month to the last day of any month, and are never less then 1 year in length, and never more than 3 years in length.

I understand that as the years go by it will require tweaking, but I can't see that this will be used for more than 2 or 3 years. It's an interim solution for austere times!

BTW, this is hardly a good choice of a problem if you are only starting with calculations

I'm afraid I didn't choose the problem, it chose me! My proposed solution doesn't sound good to me either, but I can't see any other way to do it. Please note the "newbie" next to my name in the posts and the skill level of "novice". I'm OK with calculations in general, being say, intermediate with Excel, but generally it's the syntax of the calculations I struggle with.

If it's not possible then fine, I'll go back to the drawing board.

[comment]

Let me try and put it this way: suppose you have a StartDate and a EndDate. The number of months in a given year can be calculated as =

Let ( [

rangeStart = 12 * Year ( StartDate ) + Month ( StartDate ) ;

rangeEnd = 12 * Year ( EndDate ) + Month ( EndDate ) ;



yearStart = 12 * gYear + 1 ;

yearEnd = 12 * gYear + 12

] ;

Max ( Min ( rangeEnd ; yearEnd ) - Max ( rangeStart ; yearStart ) + 1 ; 0 )

)

where gYear is the given year (as a number).

[Steven Swallow]

:hmm:

Right, I'm trying to decipher and kind of backwards pseudo it so I can understand what it's doing...

Let rangeStart = 12 * the year stored in Start_Date + the month stored in Start_Date (so if the year is 2010, and the month is October, you're ending up with 24,130 in rangeStart)

Let rangeEnd = 12 * the year stored in End_Date + the month stored in End_Date (so if the year is 2011, and the month is October, you're ending up with 24,142 in rangeEnd)

yearStart = 12 * gYear + 1 (so if gYear is given as 2010, then you'll get 24,121)

yearEnd = 12 * gYear + 12 (so if gYear is given as 2010, then you'll get 24,132)

Max ( Min ( 24,142 ; 24,132 ) - Max ( 24,130 ; 24,121 ) + 1 ; 0 )

I can't quote decipher this line fully, but it's taking 24,132, minusing 24,130 (giving 2) then adding 1 correct? Leaving 3 which is correct (October, November and December) in 2010. When I say I can't decipher the line fully, I'm not sure what the Max at the beginning is doing, nor the 0 at the end. Like I say, not a coder!

So, from this, you're saying the problem is where gYear is coming from, right?

[comment]

I'm not sure what the Max at the beginning is doing, nor the 0 at the end.

it rejects any negative values and replaces them with zero. A negative value is obtained when the two ranges do not overlap.

So, from this, you're saying the problem is where gYear is coming from, right?

Correct. Of course, you could hard-code it the way you said, but it's hardly good practice. If it were me, I'd make gYear a global field, add a repeating calculation field to calculate 5 or 6 consecutive years from there and another repeating calculation field with the above formula (adapted to a repeating field by using Extend() where appropriate), referencing the years.

[Steven Swallow]

I was wondering whether it may be possible to have the code above altered so that gYear starts at 2010 automatically, increments by 1 and runs the codes again until it hits 2020 (for the sake of argument).

As it happens, I've adapted the code above to work as Excel formulas and it's all working beautifully in that. I'll likely revisit the Filemaker options later as I'd like to learn more, but for now, and with the time I've got, Excel will do nicely.

Thanks for your help, it's been most useful, even though the solution ended up outside of Filemaker!

[comment]

In Excel, you could create a row of cells starting with 2010 and incrementing by 1. Then, in the next row, set the formula to refer to the cell directly above in order to get the year.

[Steven Swallow]

Pretty much what I've done.