DAYS function

DAYS returns the number of calendar days between two dates, with positive or negative result depending on date order.

=DAYS(end_date, start_date)

Generate a DAYS formula

Describe what you need. The generator will reach for DAYS where DAYS is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How DAYS reads its arguments
end_daterequiredstart_daterequiredDAYS →
ArgumentRequiredDescription
end_dateRequiredRequired. A date value or reference representing the later date. If end_date is before start_date, DAYS returns a negative number.
start_dateRequiredRequired. A date value or reference representing the earlier date. Must be a valid date; text dates are converted if recognized.

Returns

An integer representing the number of days elapsed between start_date and end_date.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Calculate how many days a design task took

TaskOwnerStart DateDue DateHours Logged
Landing Page DesignSarah2026-09-012026-09-0816
API IntegrationMarcus2026-09-052026-09-1524
Testing & QAJamie2026-09-102026-09-1712
=DAYS(D2, C2)

Result: 7

The Landing Page Design task ran from September 1 to September 8. DAYS subtracts the start date from the due date, returning 7 calendar days. This tells you the task duration regardless of how hours were logged.

2. Find days remaining before a task deadline

TaskOwnerStart DateDue DateHours Logged
Landing Page DesignSarah2026-09-012026-09-0816
API IntegrationMarcus2026-09-052026-09-1524
Testing & QAJamie2026-09-102026-09-1712
=DAYS(D3, TODAY())

Result: -1

Assuming today is 2026-09-16, the API Integration task was due on 2026-09-15. DAYS returns -1, indicating the deadline passed 1 day ago. Negative results signal overdue tasks and reversed date order.

3. Measure elapsed time since a task started

TaskOwnerStart DateDue DateHours Logged
Landing Page DesignSarah2026-09-012026-09-0816
API IntegrationMarcus2026-09-052026-09-1524
Testing & QAJamie2026-09-102026-09-1712
=DAYS(TODAY(), C4)

Result: 6

The Testing & QA task started on 2026-09-10. On 2026-09-16, DAYS calculates 6 calendar days have elapsed since start. This tracks actual progress against planned duration.

Common errors

Which DAYS error are you seeing?
DAYS returned an error#VALUE!
Use DATE() to create unambiguous dates: =DAYS(DATE(2026,9,1), DATE(2026,9,5)) or ensure text dates use standard formats your locale recognizes.
#NUM!
Verify DATE() arguments are valid: months 1–12, days within the month's range, and years within your spreadsheet's supported span.
#VALUE!
Reference a single cell containing one date per formula call, or wrap the formula in an array-processing function like MAP or BYROW if calculating DAYS for many rows.
ErrorWhy it happensHow to fix it
#VALUE!One or both arguments contain text that cannot be interpreted as a date, such as =DAYS('Sept 1, 2026', 'September 5') when the format is ambiguous or unrecognized by your spreadsheet.Use DATE() to create unambiguous dates: =DAYS(DATE(2026,9,1), DATE(2026,9,5)) or ensure text dates use standard formats your locale recognizes.
#NUM!One argument resolves to a date serial number outside the valid range, such as =DAYS(DATE(9999,13,1), D2) where DATE attempts an invalid month (13).Verify DATE() arguments are valid: months 1–12, days within the month's range, and years within your spreadsheet's supported span.
#VALUE!Passing a range or array of cells instead of a single date value, such as =DAYS(D2:D5, C2) when D2:D5 contains multiple dates.Reference a single cell containing one date per formula call, or wrap the formula in an array-processing function like MAP or BYROW if calculating DAYS for many rows.

Tips and when to use something else

  • DAYS counts all calendar days including weekends and holidays. Use NETWORKDAYS() to count only business days and skip holidays.
  • A negative result means end_date is before start_date—this is valid, not an error. It signals reversed order or an overdue deadline.
  • For splitting results into months or years, use DATEDIF(); DAYS always returns a single total day count.
  • Combine DAYS with TODAY() and IF() to track deadlines: =IF(DAYS(due_date, TODAY())<0, 'Overdue', DAYS(due_date, TODAY())&' days left').

Related functions

TODAYTODAY returns the current date as a date value, automatically updating each time the spreadsheet opens or recalculates.DATEDATE returns a date serial number from year, month, and day arguments, enabling date construction from separate numeric components.DATEDIFReturns the number of time units (days, months, years) between two dates, useful for calculating durations, aging, or time-to-deadline.NETWORKDAYSNETWORKDAYS counts working days between two dates, excluding weekends and optional holidays—essential for project scheduling and availability planning.DATEVALUEDATEVALUE converts text strings representing dates into serial numbers Excel and Sheets recognize, enabling date calculations and sorting.

Frequently asked questions

How do I calculate how many days remain until a project deadline?
Use =DAYS(deadline_date, TODAY()). Positive results show days remaining; negative results indicate the deadline has passed. Wrap it in IF() to display 'Overdue' when negative.
Why is my DAYS result negative?
DAYS returns negative when end_date is earlier than start_date. Check your argument order—it should be DAYS(end_date, start_date). If measuring elapsed time, swap the arguments.
Can DAYS exclude weekends or holidays from the count?
No—DAYS always counts every calendar day. To count only business days (Monday–Friday) and skip holidays, use NETWORKDAYS() instead, which accepts an optional holiday range.
What date formats does DAYS accept?
DAYS accepts date serial numbers and any text your spreadsheet recognizes as a date. For compatibility, use DATE() to construct dates programmatically, or use dates formatted as '2026-09-01' or 'Sep 1, 2026' matching your system locale.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17