DAYS Function in Excel Returns the Difference in Days between Two Dates, ignoring time! DAYS Function: A Brief DAYS function in Excel is used to calculate the number of days between two dates. If you have a start date in cell A1 and an end date in cell B1, the formula =DAYS(B1, A1) will return the total number of days between those two dates. number of days between two dates start date A1 end date B1 =DAYS(B1, A1) If your system uses the mm/dd/yy date format but your formula uses a different format like dd/mm/yyyy, Excel may show a #VALUE! error. This happens because the date format in the formula doesn’t match your system’s date setting, causing Excel to misread the date. mm/dd/yy dd/mm/yyyy #VALUE! #VALUE! date format in the formula doesn’t match your system’s date setting Objective

Value Returned by function



Aim to return days between two dates in number

DAYS Function will extract & return the number of days between the Ending date and the Starting date. Objective

Value Returned by function



Aim to return days between two dates in number

DAYS Function will extract & return the number of days between the Ending date and the Starting date. Objective

Value Returned by function Objective Objective Value Returned by function Value Returned by function Aim to return days between two dates in number

DAYS Function will extract & return the number of days between the Ending date and the Starting date. Aim to return days between two dates in number Aim to return days between two dates in number DAYS Function will extract & return the number of days between the Ending date and the Starting date. DAYS Function will extract & return the number of days between the Ending date and the Starting date. DAYS Function: A Syntax =DAYS(end_date, start_date) =DAYS(end_date, start_date) end_date: The later date.
start_date: The earlier date. end_date: The later date. end_date start_date: The earlier date. start_date: Note: The result will be a positive or negative number depending on the order of dates. Note: Function Compatibility: The DAYS function was introduced in Excel 2013. It is supported in all modern versions of Excel, including Excel 2013, 2016, 2019, and Microsoft 365. If you are using older versions of Excel, then you might need to use the formula: =DATEDIF(start_date, end_date, "d"). DAYS Excel 2013 Excel 2013, 2016, 2019, and Microsoft 365 =DATEDIF(start_date, end_date, "d") Basic Example: S.no

End Date

Start Date

Final Result

Formula Used

Comments



1

12-Mar-24

01-Jan-24

71

=DAYS(B4,C4)

Includes Feb 29, so leap year is considered



2

05-Jan-23

25-Dec-22

11

=DAYS(B5,C5)

Days between December and January, different years



3

19-Jun-21

01-Aug-21

-43

=DAYS(B6,C7)

Reversed dates, returns a negative number



4

03-Oct-20

05-Sep-20

28

=DAYS(B8,C8)

In 2020, a leap year, but Feb not in range



5

08-May-23

01-Jan-23

127

=DAYS(B9,C9)

Days between two dates in the same year



6

30-Jun-22

15-Jun-22

15

=DAYS(B10,C10)

Days between two dates in the same month and year S.no

End Date

Start Date

Final Result

Formula Used

Comments



1

12-Mar-24

01-Jan-24

71

=DAYS(B4,C4)

Includes Feb 29, so leap year is considered



2

05-Jan-23

25-Dec-22

11

=DAYS(B5,C5)

Days between December and January, different years



3

19-Jun-21

01-Aug-21

-43

=DAYS(B6,C7)

Reversed dates, returns a negative number



4

03-Oct-20

05-Sep-20

28

=DAYS(B8,C8)

In 2020, a leap year, but Feb not in range



5

08-May-23

01-Jan-23

127

=DAYS(B9,C9)

Days between two dates in the same year



6

30-Jun-22

15-Jun-22

15

=DAYS(B10,C10)

Days between two dates in the same month and year S.no

End Date

Start Date

Final Result

Formula Used

Comments S.no S.no End Date End Date Start Date Start Date Final Result Final Result Formula Used Formula Used Comments Comments 1

12-Mar-24

01-Jan-24

71

=DAYS(B4,C4)

Includes Feb 29, so leap year is considered 1 1 12-Mar-24 12-Mar-24 01-Jan-24 01-Jan-24 71 71 =DAYS(B4,C4) =DAYS(B4,C4) Includes Feb 29, so leap year is considered Includes Feb 29, so leap year is considered 2

05-Jan-23

25-Dec-22

11

=DAYS(B5,C5)

Days between December and January, different years 2 2 05-Jan-23 05-Jan-23 25-Dec-22 25-Dec-22 11 11 =DAYS(B5,C5) =DAYS(B5,C5) Days between December and January, different years Days between December and January, different years 3

19-Jun-21

01-Aug-21

-43

=DAYS(B6,C7)

Reversed dates, returns a negative number 3 3 19-Jun-21 19-Jun-21 01-Aug-21 01-Aug-21 -43 -43 =DAYS(B6,C7) =DAYS(B6,C7) Reversed dates, returns a negative number Reversed dates, returns a negative number 4

03-Oct-20

05-Sep-20

28

=DAYS(B8,C8)

In 2020, a leap year, but Feb not in range 4 4 03-Oct-20 03-Oct-20 05-Sep-20 05-Sep-20 28 28 =DAYS(B8,C8) =DAYS(B8,C8) In 2020, a leap year, but Feb not in range In 2020, a leap year, but Feb not in range 5

08-May-23

01-Jan-23

127

=DAYS(B9,C9)

Days between two dates in the same year 5 5 08-May-23 08-May-23 01-Jan-23 01-Jan-23 127 127 =DAYS(B9,C9) =DAYS(B9,C9) Days between two dates in the same year Days between two dates in the same year 6

30-Jun-22

15-Jun-22

15

=DAYS(B10,C10)

Days between two dates in the same month and year 6 6 30-Jun-22 30-Jun-22 15-Jun-22 15-Jun-22 15 15 =DAYS(B10,C10) =DAYS(B10,C10) Days between two dates in the same month and year Days between two dates in the same month and year From the above example, The 1st explains that the DAYS function automatically checks whether the given dates include a leap year. If so, it adds one extra day for the month of February.
The 2nd shows that the DAYS function can calculate the difference in days between dates that fall in different years.
The 3rd explains that if the end date is earlier than the start date, the DAYS function will return a negative value.
The 4th shows that even if a leap year is present, the DAYS function will ignore it if February is not within the range of the dates.
The 5th explains that the DAYS function can find the difference between two dates in the same year.
The 6th shows that the DAYS function can also calculate the difference between two dates within the same month. The 1st explains that the DAYS function automatically checks whether the given dates include a leap year. If so, it adds one extra day for the month of February. DAYS leap year one extra day February The 2nd shows that the DAYS function can calculate the difference in days between dates that fall in different years. DAYS difference in days different years The 3rd explains that if the end date is earlier than the start date, the DAYS function will return a negative value. end date is earlier start date DAYS negative value The 4th shows that even if a leap year is present, the DAYS function will ignore it if February is not within the range of the dates. leap year DAYS ignore it February not within the range The 5th explains that the DAYS function can find the difference between two dates in the same year. DAYS difference same year The 6th shows that the DAYS function can also calculate the difference between two dates within the same month. DAYS same month In the above example, I’ve tried to cover all the necessary possibilities of using DAYS function in Excel. Using Other Functions Nested with DAYS Function: In the below example, I’ve listed the available combinations of using DAYS functions with other Excel functions. DAYS functions Label

Formula Used

Output

Explanation



Days Between

=DAYS(B7,B4)

546

How many days from start to end



Days Remaining

=DAYS(B7,B10)

-275

Days left from today until end



Status (Overdue)

=IF(DAYS(B10,B7)<0,"Overdue","On Time")

On Time

Checks if task is overdue



Duration Text

=TEXT(DAYS(B7,B4),"0") & " days between start and end"

546 days between start and end

Human-friendly sentence showing total days



Subscription Status

=IF(DAYS(B10,B4)>365,"Expired","Active")

Expired

Active or Expired based on 365 days check



Months Duration

=DATEDIF(B4,B7,"m")

17

Calculates full months between dates



Fancy Duration

=DATEDIF(B4,B7,"y") & " years, " & DATEDIF(B4,B7,"ym") & " months, " & DATEDIF(B4,B7,"md") & " days"

1 years, 5 months, 29 days

Shows months and leftover days Label

Formula Used

Output

Explanation



Days Between

=DAYS(B7,B4)

546

How many days from start to end



Days Remaining

=DAYS(B7,B10)

-275

Days left from today until end



Status (Overdue)

=IF(DAYS(B10,B7)<0,"Overdue","On Time")

On Time

Checks if task is overdue



Duration Text

=TEXT(DAYS(B7,B4),"0") & " days between start and end"

546 days between start and end

Human-friendly sentence showing total days



Subscription Status

=IF(DAYS(B10,B4)>365,"Expired","Active")

Expired

Active or Expired based on 365 days check



Months Duration

=DATEDIF(B4,B7,"m")

17

Calculates full months between dates



Fancy Duration

=DATEDIF(B4,B7,"y") & " years, " & DATEDIF(B4,B7,"ym") & " months, " & DATEDIF(B4,B7,"md") & " days"

1 years, 5 months, 29 days

Shows months and leftover days Label

Formula Used

Output

Explanation Label Label Formula Used Formula Used Output Output Explanation Explanation Days Between

=DAYS(B7,B4)

546

How many days from start to end Days Between Days Between =DAYS(B7,B4) =DAYS(B7,B4) 546 546 How many days from start to end How many days from start to end Days Remaining

=DAYS(B7,B10)

-275

Days left from today until end Days Remaining Days Remaining =DAYS(B7,B10) =DAYS(B7,B10) -275 -275 Days left from today until end Days left from today until end Status (Overdue)

=IF(DAYS(B10,B7)<0,"Overdue","On Time")

On Time

Checks if task is overdue Status (Overdue) Status (Overdue) =IF(DAYS(B10,B7)<0,"Overdue","On Time") =IF(DAYS(B10,B7)<0,"Overdue","On Time") On Time On Time Checks if task is overdue Checks if task is overdue Duration Text

=TEXT(DAYS(B7,B4),"0") & " days between start and end"

546 days between start and end

Human-friendly sentence showing total days Duration Text Duration Text =TEXT(DAYS(B7,B4),"0") & " days between start and end" =TEXT(DAYS(B7,B4),"0") & " days between start and end" 546 days between start and end 546 days between start and end Human-friendly sentence showing total days Human-friendly sentence showing total days Subscription Status

=IF(DAYS(B10,B4)>365,"Expired","Active")

Expired

Active or Expired based on 365 days check Subscription Status Subscription Status =IF(DAYS(B10,B4)>365,"Expired","Active") =IF(DAYS(B10,B4)>365,"Expired","Active") Expired Expired Active or Expired based on 365 days check Active or Expired based on 365 days check Months Duration

=DATEDIF(B4,B7,"m")

17

Calculates full months between dates Months Duration Months Duration =DATEDIF(B4,B7,"m") =DATEDIF(B4,B7,"m") 17 17 Calculates full months between dates Calculates full months between dates Fancy Duration

=DATEDIF(B4,B7,"y") & " years, " & DATEDIF(B4,B7,"ym") & " months, " & DATEDIF(B4,B7,"md") & " days"

1 years, 5 months, 29 days

Shows months and leftover days Fancy Duration Fancy Duration =DATEDIF(B4,B7,"y") & " years, " & DATEDIF(B4,B7,"ym") & " months, " & DATEDIF(B4,B7,"md") & " days" =DATEDIF(B4,B7,"y") & " years, " & DATEDIF(B4,B7,"ym") & " months, " & DATEDIF(B4,B7,"md") & " days" 1 years, 5 months, 29 days 1 years, 5 months, 29 days Shows months and leftover days Shows months and leftover days That’s it. This tutorial is originally published on How to Use DAYS Function in Excel? How to Use DAYS Function in Excel

Walkthroughs, tutorials, guides, and tips. This story will teach you how to do something new or how to do something better.

Microsoft

The Best Way to Use the Excel NETWORKDAYS.INTL Function

Learn How to Use the SECOND Function to Retrieve Second Values from Input Time?

View Website Here

Read My Stories

Too Long; Didn't Read

Make resilience your competitive advantage

Calculate Date Differences Easily Using Excel DAYS Function!

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

Untitled Story

Using the Excel DAYS360 Function for Financial Analysis- A Guide

4 Data Transformations Made Spreadsheet-Easy

5 Excel Features That Can Grow Your Net Worth in No Time

5 Ways to Add Security to Excel & PDF with .NET Document APIs

7 Essential Things in Excel That You Definitely Need to Master

Using the Excel DAYS360 Function for Financial Analysis- A Guide

4 Data Transformations Made Spreadsheet-Easy

5 Excel Features That Can Grow Your Net Worth in No Time

5 Ways to Add Security to Excel & PDF with .NET Document APIs

7 Essential Things in Excel That You Definitely Need to Master

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps