How to calculate years of service in Excel using TODAY

In this article, we will learn how to calculate the number of years, months and days elapsed from a certain date using DATEDIF function in Excel.We will learn how to use Today func

How to calculate years of service in Excel using TODAY

In this article, we will learn how to calculate the number of years, months and days elapsed from a certain date using DATEDIF function in Excel.

We will learn how to use Today function to get Todays date in Excel.

DATEDIF function returns the number of years, months and days between the given two dates in Excel.

Syntax:

=DATEDIF(start_date, end_date, unit)

Unit:

y denotes Years between dates
m denotes between dates
d denotes days between dates
"md" denotes days between dates, ignoring months and years
"ym" denotes months between dates, ignoring days and years
"yd" denotes days between dates, ignoring years

TODAY function in Excel is used to generate todays date in a cell.
Syntax:

Lets get this with an example shown below.
Here we need to calculate the age of Ned Starks children.

Use the formula to get the age of children.
Formula:

Date of birth is the start date in the C4 cell.
Today() function is used to get Todays date as an end date.
Y to get the age in years.

Note:
Here we are using Today as an end date. You can use end date as your choice.

We got the age of Jon Snow using DATEDIF function
Copy the formula in other cells, select the cells taking the first cell where the formula is already applied, use shortcut key Ctrl + D to find the age of rest of the children.

Similarly, we will find out months and days using the same function but different unit.

We got the age in months

We got the age in days

Now Some of you are wondering if we need to find the years, months and days between the two dates.

Use the formula to get the years, months and days together

=DATEDIF(B2,TODAY(),"y") & "y " &
DATEDIF(B2,TODAY(),"ym")&"m " &
DATEDIF(B2,TODAY(),"md") & "d"

Explanation:
DATEDIF(B2,TODAY(),"y") & "y " denotes years between dates with string y  attached to the number
DATEDIF(B2,TODAY(),"ym")&"m " denotes months between dates, ignoring days and years with string m  attached to the number.
DATEDIF(B2,TODAY(),"md") & "d" denotes days between dates, ignoring months and years with string d attached to the number.

As you can see their age now. You can use this function to calculate the number of days in service in Excel.

Hope you understood how to calculate the number of years, months and dates from a certain date. You can perform these tasks in Excel 2013 and 2010 workbook. Please share your query below in the comment box. We will assist you.

Popular Articles:

50 Excel Shortcuts to Increase Your Productivity

How to use the VLOOKUP Function in Excel

How to use the COUNTIF function in Excel

How to use the SUMIF Function in Excel

Video liên quan