Extract days between dates ignoring years in Excel

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

Or

=IF(date1>date2, date1 - date2, date2 - date1)

Use the IF formula, if you don’t know which date to put first.

Example:

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.

Example 2:

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.

Syntax:

=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)

Explanation

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.

Notes:

  1. The function returns #VALUE! Error, if any argument to the function is non - numeric.
  2. Be careful while using Dates & percent number as cell reference.
  3. Dates in the formula must be entered using cell reference. Excel date format creates problem while computing results. 
  4.  If date 1 > date2, then the function returns the error, if error not handled carefully.

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.

Related Articles

Calculate age from date of birth

Calculate days, years and months from certain date

How to use the EDATE function in Excel

SUM if date is between

Vlookup by Date in Excel

Popular Articles

50 Excel Shortcut to Increase Your Productivity

Edit a dropdown list

Absolute reference in Excel

If with conditional formatting

If with wildcards

Leave a Reply

Your email address will not be published. Required fields are marked *

Terms and Conditions of use

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.

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube