In this article, we will learn how to calculate the number of years, months and days elapsed from a certain date using the function in Excel.
Excel stores dates as numbers, a simple mathematical formulation explained. So just need to subtract the two dates it will get the days between the two dates with considering years.
|=later_date - earlier date|
|=IF(date1>date2, date1 - date2, date2 - date1)|
Use the IF formula, if you don’t know which date to put first.
Let’s get this by using via testing the function on some examples. Here we have two given dates as Date 1 & Date 2.
We need to find the number of days which lay between the two given
Use the formula in C2 and D2 cells.
|= A2 - B2|
This formula gets you negative results if the date1 < date2.
If you don’t want to care which is smaller, just use the below formula in remaining cells
|= IF ( A4 > B4 , A4 - B4, B4 - A4 )|
This was the basic method to do so. As you can see in the above example explained you the condition. Now we move forward to using Excel built - in function.
Here's one more example to do the same using the DATEDIF function. But this time we will be using the built - in function.
DATEDIF function returns the number of years, months and days between the given two dates in Excel.
|=DATEDIF(start_date, end_date, unit)|
Start_date : First date in a valid excel format
end_date : Second date in a valid excel format
Unit : unit in quotes and crucial argument for the function
"y" gets the years between dates
"m" gets the months between dates
"d" gets the days between dates
"md" gets the days between dates, ignoring months and years
"ym" gets the months between dates, ignoring days and years
"yd" gets the days between dates, ignoring years
Try the below formula to Get Days between two dates, ignoring years
Use the Formula:
|= DATEDIF (A2, B2, C2)|
A2 : first date argument given as cell reference
B2 : Second date argument given as cell reference
C2 : specified unit. Value can be given directly within quotes "yd".
Marked boxes while using the function shows us that argument to the function given as cell reference.
The function returns the complete calendar years between the dates using the “yd”.
As you can see the formula returns the days between dates ignoring years. But the last result returns #NUM error because the start_date is AFTER the end_date.
Hope you understood how to Extract days between dates ignoring years in Excel. You can perform these tasks in Excel 2013 and 2010 workbook. Please share your query below in the comment box. We will assist you.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.