Convert days into months

[nicosdj]

Hi


I'm trying to make a workbook that can calculate, how many days a person has been hired with us in total. If an employee has been hired with us in several different periods, then we accumulate the amount of days in the different periods to calculate when their 25th anniversary is for instance.

I’ve created a form, where you can type the different dates, and it then puts them into a sheet. I’ve been trying to use formulas, instead of just doing the calculations in VBA, so that the user can figure out, what is going on.
I use Datedif to extract how many days, months and years that have passed from the start date to the end date. After each new entry I calculate the sums of days, months and years. When the sum function is used, it simply adds up the amount of days, months and years. Now the sum of months can exceed 12, so I made the following code to adjust for that:


'Calculates if the number of months exceed 12
'If so it subtracts 12 from from the sum of months and adds a year instead

For Each cell In Worksheets("Anciennitetsberegner").Range("E6:E" & LastRow).Cells
month_sum = month_sum + cell.Value
Next
If month_sum >= 12 Then

Do

month_sum = month_sum - 12
month_counter = month_counter + 1

Loop Until month_sum < 12

Range("B2").FormulaR1C1 = month_sum

For Each cell In Worksheets("Anciennitetsberegner").Range("F6:F" & LastRow).Cells
year_sum = year_sum + cell.Value
Next

Range("B3").FormulaR1C1 = year_sum + month_counter


Else

Range("B2").FormulaR1C1 = "=SUM(R[4]C[3]:R[" & LastRow - 2 & "]C[3])"
Range("B3").FormulaR1C1 = "=SUM(R[3]C[4]:R[" & LastRow - 3 & "]C[4])"


End If


Now that works fine. But I can also end up with 120 days for instance, and I then want it to convert that into months. But how do I do that, seeing that months vary from 28 to 31 days?

I thought of converting the sums of months and years into a date and then gradually adding the days by “filling” the succeeding months with days, until they did not cover an entire month. But is that the right way to do it? Doesn’t it create problems, because I have different dates in different years?
I have attached my workbook, but everything besides the code is in Danish, so you might not understand what is going on. If you want me to translate it let me know.

[Bob Phillips]

I don't think that you can get a meaningful answerr if the dates are not contiguous, 14 months might be all January's, or some mixture.

[nicosdj]

Ok...

Is it meaningful to use 365 days / 12 months = 30,416666 instead of twelve in the code?

[Bob Phillips]

I don't understand the question.

[nicosdj]

Sorry :)

I was just thinking out load.
In the code I check, if the months are less than 12 months. If they are not, I add another year. Similarly, is it then meaningful to check, if the days are less than 30,416666 (the average length of a month in days) and add another month, if they are not?

[bdsii]

I am new to all this but it seems there are a couple of options for you and hopefully this may help in some way.

The following code calculates the number of months without having to fool with the number of days and then calculate the number of months. I hardcoded the cells to match you spreadsheet so you can see how it may work. However, it only calculates the number of months assuming it is in the same year. You might be able to edit this to allow for using more than one year.

Dim startmonth As Long
Dim endmonth As Long
startmonth = Month(Range("B6"))
endmonth = Month(Range("C6"))
Range("G6").Value = endmonth - startmonth

startmonth = Month(Range("B7"))
endmonth = Month(Range("C7"))
Range("G7").Value = endmonth - startmonth

startmonth = Month(Range("B8"))
endmonth = Month(Range("C8"))
Range("G8").Value = endmonth - startmonth


another option is the following formula I found on the web which probably is really what you are looking for. It calculates the number of months between cell B6 and cell C6.
=DATEDIF(B6,C6,"m")

If you want to calculate the number of months between cell B6 to todays date then you would use:
=DATEDIF(B6,TODAY(),"m")


I hope this helps :-)

[Paul_Hossler]

Now that works fine. But I can also end up with 120 days for instance, and I then want it to convert that into months. But how do I do that, seeing that months vary from 28 to 31 days?


1. What would expect that answer to that to be?

2. Outside the box, how about dealing in months and fractional months?

Period 1 = May 15, 2006 to Feb 10, 2009 so

(31-15)/31 + 32 + (10/28) = 32.87 months

(partial May 06) + (Jun 06 to Jan 09) + (partial Feb 09)


Paul

[SamT]

I don't think you can get better than 1 or 2 days accuracy. Too many variables.

If hire time covered a leapday is that one month or 29 days? In continuous hire, anniversaries are from date to date, ignoring leapdays.

If hire time is 5 years is that counted as 5 years plus 1 or even 2 leapdays?

Does it matter?

I would just total the days, not years and months, then divide by 365.25. Then count backwards from NOW with the remainder to see how many months and days the remainder resulted in.

That would limit the error to a max of one day. After 25 years, 9000+ days, who's counting. Besides you, I mean. LOL

SamT

[macropod]

Hi nicosdj,

Another approach, which might be more relevent since you're working with split periods, is to express fractional months as a proportion of the length of the month concerned, and count whole month as integers. For example, if a person worked from 16 February 2008 to 15 May 2008 inclusive, instead of counting that as 3 months, you might count it as being:
(29-15)/29 + 2 + 15/30 = 2.9666 months
Conversely, if the person worked from 16 February 2009 to 15 May 2009 inclusive, the period would calculate as 2.9482 months. Whilst on some estimations these examples might seem to disadvantage the employee, other scenarios could work to their advantage (eg 16 March to 15 June inclusive = 3.0161 months). If you wanted, you could introduce logic to the effect that if the start day is one more than the end day, treat the period as whole months - aside from that, the formula nevertheless shows how you could intelligently handle part months.

BTW, the average month length, taking account of leap years with the Gregorian calendar, is 30.436875 days, since the average year has 365.2425 days. However, unless you're working with date ranges extending beyond the 20th & 21st centuries, a month average of 30.4375 days (based on an yearly average of 365.25 days) is reasonable.