Confidently Calculate Days in Excel: DATEDIF, Formulas, and TODAY Function. In this article, we’ll explore how to calculate the number of days in Microsoft Excel.
Confidently Calculate Days in Excel: DATEDIF, Formulas, and TODAY Function
There are a couple of methods. You can subtract the older date from the more recent one, or you can utilize the Excel DATEDIF function. Sometimes, this function doesn’t appear automatically; you’ll need to type it out completely. To calculate a person’s age in days, months, or years in Excel, you can select today’s date using the ‘today’ function explained in the example.
The DATEDIF function in Excel is designed to determine the full calendar period between two dates. It’s commonly used to calculate a person’s age, expressing it in years, months, and days.
The syntax for the DATEDIF function is as follows:
=DATEDIF(start_date, end_date, unit)
Start_date: The initial date in a valid Excel format.
End_date: The subsequent date in a valid Excel format.
Unit: Represents the desired unit enclosed in quotes.
For instance:
‘y’ returns the years between dates.
‘m’ returns the months between dates.
‘d’ returns the days between dates.
‘md’ returns the days between dates, disregarding months and years.
‘ym’ returns the months between dates, ignoring days and years.
‘yd’ returns the days between dates, ignoring years.
It’s crucial to specify the unit argument accurately.
Let’s delve into an example to better understand its usage.
We have some examples to test the formula. Given the start date and end date, we aim to calculate the difference in days between dates.
Use this formula:
=DATEDIF(A2, B2, C2)
A2: The first date provided as a cell reference.
B2: The second date provided as a cell reference.
C2: The specified unit.
When applying the function, marked boxes indicate the function’s argument given as a cell reference.
The function returns the complete calendar years between the dates using ‘y’ as the unit.
Note:
Dates in Excel must be in a valid format.
The unit argument can be given as a cell reference or directly in double quotes.
The start_date must precede the end_date; otherwise, the function will display an error (#NUM!).
The function returns a #VALUE! error if the date is not valid.
One of the most common uses of this function is to determine a person’s age in Excel.
Here’s an example of determining a person’s age in Excel using the DATEDIF function in combination with the TODAY function.
The TODAY function in Excel generates today’s date in a cell.
Use this syntax:
=TODAY()
Let’s calculate the age of Ned Stark’s children as an example.
Apply the formula to get the children’s ages:
=DATEDIF(C4, TODAY(), ‘y’)
Date of birth is the start date in cell C4.
TODAY() function is used to obtain today’s date as the end date.
‘Y’ specifies the age in years.
Note:
Here, TODAY is used as the end date. You can use any end date of your choice.
We’ve calculated Jon Snow’s age using the DATEDIF function. To find the ages of the other children, copy the formula to other cells. Select the cells starting from where the formula has been applied, then use the shortcut Ctrl + D to calculate the ages of the remaining children.
Similarly, we’ll determine the ages in months and days using the same function but different units.
We obtained the ages in months and days respectively.
Now, if you want to find the years, months, and days between two dates altogether, use this formula:
=DATEDIF(B2, TODAY(), ‘y’) & ‘y ‘ & DATEDIF(B2, TODAY(), ‘ym’) & ‘m ‘ & DATEDIF(B2, TODAY(), ‘md’) & ‘d’
‘y’ returns the years between dates with ‘y ‘ attached.
‘ym’ returns the months between dates, ignoring days and years with ‘m ‘ attached.
‘md’ returns the days between dates, ignoring months and years with ‘d’ attached.
By using this function, you can calculate the duration of service in days in Excel.
I hope this article about How to Calculate the Number of Days in Microsoft Excel has been elucidating. For more information on calculating date and time values and related Excel formulas, explore additional articles available
