Calculating Length of Service from days to Years/Months/Days

Copper Contributor

I am trying to calculate employees Length of Service from Total days to Years/Months/Days.  What formula(s) would I use for that type of calculation?

05/27/24Length of Service 
Start DateDaysYearsYearsMonthsDays
10/29/1820375.585  
07/17/1725066.876  
09/07/10501113.7313  
01/05/2112383.393  
12/09/225351.471  
12/01/1627347.497  
01/20/2112233.353  
11/02/225721.571  
10/16/232240.610  
08/23/04721719.7719  
05/22/1725627.027  
01/03/323374892.4692  
01/14/09561215.3815  
09/07/10501113.7313  
05/20/2470.020  
05/20/2470.020  
09/06/11464712.7312  
11/04/08568315.5715  
01/05/2112383.393  
01/24/234891.341  
07/24/1724996.856  
09/13/219872.72  
05/29/12438112.12  
04/12/2111413.133  

I greatly appreciate any assistance. Kristin

4 Replies

Thank you very much. 

@Kristin_Lyons depending on what are your accuracy requirements. Your need to choose between two calculations:

 

  • Treat every month as a 30 days month, ignoring your Start Date field and leap years (every year will be 365 days long). This approach simpliflies the calculations but is not exact.
  • Start to count from your Start Date field, filling every month and taking into account leap years in order to correctly fill February. This approach is a little more complex.

 

Regardless of your choice, you don't need the first decimal Years field.

 

Hope this helps,

Miguel.

@mfcorral accuracy is what I need. Can you give me an example formula? I am thinking thatvwill become a long formula, like the 12 year calculation. Trying to understand what the formula would look like. 

 

Kristin

Hi again @Kristin_Lyons, more than a formula you need and algorithm. In C#:

 

using System;
					
public class Program
{
	public static void Main()
	{
		var startDate = new DateTime(2012, 5, 21);
		var totalDays = 2102;
		var span = GetDateTimeSpan(startDate, totalDays);
		
		Console.WriteLine($"Years: {span.Years}");
		Console.WriteLine($"Months: {span.Months}");
		Console.WriteLine($"Days: {span.Days}");
	}
	
	public static (int Years, int Months, int Days) GetDateTimeSpan(DateTime startDate, int totalDays)
	{
		int years = 0;
		int months = 0;
		int days = 0;

		var endDate = startDate.AddDays(totalDays);
		var currentDate = endDate;

		// If totalDays is negative, swap dates
		if (startDate > endDate)
		{
			endDate = startDate;
			startDate = currentDate;
		}

		// Count years
		currentDate = startDate;
		while (currentDate.AddYears(years + 1) <= endDate)
		{
			years++;
		}

		// Count months
		currentDate = currentDate.AddYears(years);
		while (currentDate.AddMonths(months + 1) <= endDate)
		{
			months++;
		}

		// Count days
		currentDate = currentDate.AddMonths(months);
		while (currentDate.AddDays(days + 1) <= endDate)
		{
			days++;
		}

		return (years, months, days);
	}
}

 

You can play with it at https://dotnetfiddle.net/. Be sure that you choose the following options:

 

  • Language: C#
  • Project type: Console
  • Compiler: .NET 8

 

Replace all the code, and you got it.

 

Regards,

Miguel.