DateDiff fractional months

[supmktg]

I need to (accurately) calculate a rental period to fractions of a month.
ie) 1/1/2006 to 2/14/2006 would equal 1.5 months.
DateDiff("m",[Start],[End]) returns whole months.
DateDiff("d",[Start],[End])/30 is very close , but not precise.

Is there a way to accurately calculate fractions of a month?

Thanks,

Sup

 

[SleepyJay]

Instead of:
DateDiff("d",[Start],[End])/30

The following code may have a more elegant solution, but this works...

'Function to get fractional months

Function DateFraction(ByVal dt_start As Date, ByVal dt_end As Date) As Double

Dim valD As Integer
Dim valM As Integer
Dim valF As Integer
Dim dtF As Date
Dim valFrac As Double
Dim valFinal As Double

'---Testing with:
'dtsta = "1/1/2007"
'dtend = "2/14/2007"
'---Returns: 1.5

'Get the whole number of months
valM = DateDiff("m", dt_start, dt_end)

'This is the numerator in the fractional part
valD = Day(dt_end)

'This is the last day of the month in question.
'If it makes your head spin, it's this (read bottom up, obviously):
'  -- construct the first day of the ending month as date
'  -- add one month to it
'  -- subtract 1 day from it
' and voila! You have the last day of the month as a date.

dtF = DateAdd("d", -1, _
               CDate(DateAdd("m", 1, _
                             CDate(Month(dt_end) & "/1/" & Year(dt_end)))))
                             
'Take the last day of the ending month as the number of days in that month
valF = Day(dtF)

'This is the fraction of days in the month
valFrac = valD / valF

'And this is the whole date span with the fraction.
valFinal = valM + valFrac

'And return it, because you want it.
DateFraction = valFinal

End Function

Hope it helps.

jason.

 

[SleepyJay]

Upon further reflection...

On my way home, I realized I over did this. I was originally thinking in terms of a single line solution--something you might put into a query. What I wrote below should be rather straight forward to convert to an expression.

But, if it's going to be code, you could easily change the part where I'm trying to find the last day of the month with all of those date functions:

dtF = DateAdd("d", -1, _
               CDate(DateAdd("m", 1, _
                             CDate(Month(dt_end) & "/1/" & Year(dt_end)))))

to a simple look-up array. This would store the number of days in each month, and you could look this up by the ending month. Note that you'd have to adjust for leap years. But, this would be faster and probably even more readable.

jason.

 

[neileg]

Wrong concept?

Half of April is fifteen days, half of July is fifteen and a half, and half of February is fourteen, except when it is a leap year! How can you translate this into days with any degree of precision, and if you could, how would this help?

 

[supmktg]

Sleepyjay,

Thank you very much! Your DateFraction function comes as close as possible to what I'm trying to do.

Thanks,

Sup

 

[SleepyJay]

Wha...?

I thought the purpose was to change Feb 14 into .5 months so that you could mutliply that by some one's rent to decide how much they owe.

I just showed two ways below how to get that. All you need is the ratio between the day your ending on and the number of days in a month.

To reverse this, you just multiply the fraction by the number of days in the month. So, Feb 13 = 0.4643 months. .4643 months is 13.0004 days in Feburary. But why would you have that fraction and not the number of days? Still you just need the fraction, the month, and the number of days in that month (which you could calculate or look-up).

Anyway, remember that the number of days of a month never changes, except every four years in Febuary. So you say something like:

...
daysInThisMonth = ArrayOfDaysOfMonths(month)

if (month = 2 and IsaLeapYear(year)) then daysInThisMonth = daysInThisMonth + 1

and to determine if it is a leap year, just find one--2004 was--and you determine if it's within some multiple of four from a known leap year, something like:

  IsaLeapYear = ((year mod baseLeapYear) = 4)

Be sure that your baseLeapYear is earlier than ones you'll test against (or else this test will fail).

Does this help?

jason.

 

[supmktg]

Jason,

That is exactly what I'm trying to do! I'm trying to calculate a rental fee based on a monthly rate. The rental period may be any fraction of a month or months. An item's rent may begin on any day of a month and end on any day of a month.

When I replied: "Your DateFraction function comes as close as possible to what I'm trying to do.", I meant that as far as I can tell this is the closest that I can come to a fair and accurate calculation because the number of days in each month differs.

Again, I thank you very much for your help, it is greatly appreciated!

Sup

 

[The_Doc_Man]

Part of this problem is conceptual. To resolve what it really means to have a fraction of a month, you first need to look at what the BUSINESS definition of a month happens to be. If a month is 30 days, you have 12.16 months in a year. If a month is by calendar, you run into the issue of variable base rates. You wil confuse your customers by charging them more for returning a rental on the 15th of Feb (0.535) than for the 15th of Apr (0.500) and charging them less on the 15th of Jan (0.484).

Never mind what conventional wisdom says. What does the RENTAL AGREEMENT say?

 

[SleepyJay]

One Dollar Rent Special!

Hey, I just was happy to answer the technical question.

The lesson being to use it at your own business risk.

Although, it doesn't seem that strange to me at all that you pay the same rent every month, and yet that means that Feburary my apartment is much more expensive than January. I'm not sure that I'd question why I paid $(15/31) in July, but only $(15/30) in June. Since it's explainable and easily calculatable, I doubt anyone is going to question it. Plus, it's going to be spelled out in exactly this way in the rental agreement--as you say.

It is perhaps that I'm actually renting a years worth of apartment and that costs me, say, $12 for the whole year. It just so happens that I pay that out in 12 equal installments. In that sense, the last six months of the year are cheaper than the first six months--3 more days for the same $6. But, if I quit this agreement early, I simply have to pay more in an "expensive" month.

Anyway, this conversation might make most people want to pay you the extra bit just to make you stop talking about it!

The solution is to change our planetary motion in order to have easier numbers with which to work...

BTW: supmktg: I sent the second post at 9am explaining myself because of neileg's post. But, I'm glad it helped you.

jason.

 

[pstraton]

I’m aware that this is an old thread, but it is an issue that keeps cropping up in various VBA contexts and I have yet to find a complete solution, so I thought I’d offer one:

  Public Function MonthDiffFloat(ByVal StartDate As Variant, ByVal EndDate As Variant) As Single
      'Returns the difference between the two specified dates as a floating-point number of months in which the
      'integer part is the whole number of months between the two dates (if any) and the fractional part is the
      'difference between the month fraction of the end-date and the month fraction of the start-date.  Note that,
      'for an end-date month fraction that is greater than the start-date month fraction, there will be a "carry"
      'value of 1 added to the whole-month integer part and, for an end-date month fraction that is less than the
      'start-date, there will be a "borrow" subtracted from the whole-month integer part, in a manner analogous to
      'standard decimal arithmetic.
      '
      'IMPORTANT: This is a purely mathematical month-fraction calculation that, for both the start-date month
      'and the end-month month, depends on the number of days in their respective months.  For example, the month
      'fraction for the first 15 days of May is 15/31 = 0.484, whereas the fraction for the first 15 days of June
      'is 15/30 = 0.500, February 2014 is 15/28 = 0.536, and February 2016 is 15/29 = 0.517.
      '
      'This subtraction technique can produce results that are counter-intuitive, such as the month-difference
      'value between 1/15/2014 and 2/15/2014 is 1.052 (not 1.000) since the difference between the fractional parts
      'is .536 - .484 = 0.052.
      '
      'CAUTION: As a result, this type of month fraction calculation may not be suitable for a particular business
      'requirement, such as calculating a particular company's rental car fee or an investment interest value.
   
      Dim MonthDiff As Single
      Dim Sign As Integer
   
      If CDate(EndDate) < CDate(StartDate) Then
          'End date is earlier than start date, so set the Sign of the result to negative and swap the dates so that
          'the (positive) date arithmetic will work correctly.
      
          Sign = -1
          Dim Temp As Variant
          Temp = StartDate: StartDate = EndDate: EndDate = Temp
      Else
          'End date is equal to or later than start date, so set the Sign of the result to positive.
      
          Sign = 1    '+1
      End If
      
      'Note that a date range that spans the new-year is not a special case due to the normal borrow/carry effect
      'described in the function-description paragraph, above.
   
      MonthDiff = (Month(EndDate) + Day(EndDate) / DaysInMonth(EndDate)) - _
                  (Month(StartDate) + Day(StartDate) / DaysInMonth(StartDate))
   
      MonthDiffFloat = Sign * (12 * (Year(EndDate) - Year(StartDate)) + MonthDiff)
  End Function
   
  Function DaysInMonth(Dateval As Variant) As Integer
      DaysInMonth = Day(DateDiff("d", 1, DateSerial(Year(Dateval), Month(Dateval) + 1, 1)))
  End Function

 

[jdraw]

Interesting concept, but I haven't seen it cropping up.

Where exactly would you see using it?

I can see rent payments $xxx/per month and payable in 2 parts - 1 on the 15th and 1 on last day of month.

I'm sure there's a theoretical answer to --when is half passed Spring, but then Why?

Don't get me wrong, I like puzzles and some odd things and appreciate the theoretical.

 

[pstraton]

jdraw: The context in which I had to sort this out is one in which people get paid a monthly rate for a service being provided but they can work whole and/or fractions of months. So it raised the question of what a "fraction of a month" actually means when they have different lengths and, especially, when the fractional time period spans two adjacent months with different lengths.

 

[jdraw]

I'm sure it will be useful to someone - obviously you saw a need.

I agree there are 31 days in January, but not necessarily 31 working days.
Are there issues when you are dealing with a calendar month vs the number of workdays in that month? Does that play into your function? Should it?
How do you handle overtime in this scenario?

I ask because I'm just curious - you saw a need and created a function and mentioned people getting paid.

 

[pstraton]

Good questions, which obviously bring in more complex calendar variables such as holidays and personal calendars and such. In this case, the rate is strictly monthly (not daily or hourly per month), so those factors don't come into play.

 

[jdraw]

Thanks,
Good luck.