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) =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(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) =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) =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(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(B10,C10) Days between two dates in the same month and year Days between two dates in the same month and year 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: Start Date

End Date

Today’s Date



01 January 2023

30 June 2024

01-Apr-25 Start Date

End Date

Today’s Date



01 January 2023

30 June 2024

01-Apr-25 Start Date

End Date

Today’s Date Start Date Start Date End Date End Date Today’s Date Today’s Date 01 January 2023

30 June 2024

01-Apr-25 01 January 2023 01 January 2023 30 June 2024 30 June 2024 01-Apr-25 01-Apr-25 In the below example, I’ve listed the available combinations of using DAYS functions with other Excel functions. DAYS functions Label

Formula Used



Days Between

=DAYS(B7,B4)



Days Remaining

=DAYS(B7,B10)



Status (Overdue)

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



Duration Text

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



Subscription Status

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



Months Duration

=DATEDIF(B4,B7,”m”)



Fancy Duration

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

Formula Used



Days Between

=DAYS(B7,B4)



Days Remaining

=DAYS(B7,B10)



Status (Overdue)

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



Duration Text

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



Subscription Status

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



Months Duration

=DATEDIF(B4,B7,”m”)



Fancy Duration

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

Formula Used Label Label Formula Used Formula Used Days Between

=DAYS(B7,B4) Days Between Days Between =DAYS(B7,B4) =DAYS(B7,B4) Days Remaining

=DAYS(B7,B10) Days Remaining Days Remaining =DAYS(B7,B10) =DAYS(B7,B10) Status (Overdue)

=IF(DAYS(B10,B7)<0,”Overdue”,”On Time”) Status (Overdue) Status (Overdue) =IF(DAYS(B10,B7)<0,”Overdue”,”On Time”) =IF(DAYS(B10,B7)<0,”Overdue”,”On Time”) Duration Text

=TEXT(DAYS(B7,B4),”0″) & ” days between start and end” Duration Text Duration Text =TEXT(DAYS(B7,B4),”0″) & ” days between start and end” =TEXT(DAYS(B7,B4),”0″) & ” days between start and end” Subscription Status

=IF(DAYS(B10,B4)>365,”Expired”,”Active”) Subscription Status Subscription Status =IF(DAYS(B10,B4)>365,”Expired”,”Active”) =IF(DAYS(B10,B4)>365,”Expired”,”Active”) Months Duration

=DATEDIF(B4,B7,”m”) Months Duration Months Duration =DATEDIF(B4,B7,”m”) =DATEDIF(B4,B7,”m”) Fancy Duration

=DATEDIF(B4,B7,”y”) & ” years, ” & DATEDIF(B4,B7,”ym”) & ” months, ” & DATEDIF(B4,B7,”md”) & ” 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” That’s it. This article is originally published on How to Use Excel DAYS Function? article How to Use Excel DAYS Function?

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

Microsoft

Using Minute Function to Extract Minutes From  Input Time Like a Pro

Excel DAYS Function Explained: Calculating Date Differences Between Two Dates!

About Author

Comments

TOPICS

THIS ARTICLE WAS FEATURED IN

Related Stories

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

Announcing Excel Add-in for ParallelDots AI APIs

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

Announcing Excel Add-in for ParallelDots AI APIs

Light-Mode

Classic

Newspaper

Dark-Mode

Neon Noir

Minty

HN StartUps