nicosdj
02-16-2010, 11:04 AM
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.
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.