Method 1 – Using TODAY Function and Subtracting Another Date to Calculate Number of Days in Excel
Steps:
- Select cell C6 => Insert the formula:
=TODAY()- Press Enter. It’ll return today’s date.
- Select cell C9 => Write the formula:
=$C$6-B9- Press Enter.
- Use the Fill Handle to copy the formula in other cells below. It returns a negative number for those cases where the corresponding “Another Date” is in the future to today’s date.
- To get rid of the minus signs and return just the numbers alone, use the following formula with the ABS function:
=ABS($C$6-B9)
We want to add “days left” if the other date is in the future to today’s date. And we want to add “days passed” if the date is in the past to today’s date. The formula will be:
=IF(B10>$C$6, B10-$C$6 &" days left", $C$6-B10 &" days passed")
Method 2 – Applying DAYS Function to Determine Number of Days Between Today and Another Date
Insert the DAYS function to get the number of days between today and another date in Excel.
The syntax of the function is:
=DAYS(end_date, start_date)If the end date is smaller than the start date, we’ll get a negative output. Use the ABS function to get only the number value. The formula will be:
=ABS(DAYS(TODAY(),B7))
Method 3 – Using DATE Function to Get Number of Days Between Today and Another Date
Use the Excel DATE function to determine the number of days between today and another date.
The syntax of the function is:
=DATE(year, month, day)Subtract two dates. The formula to be used is:
=ABS(DATE(YEAR($C$6),MONTH($C$6),DAY($C$6))-DATE(YEAR(B9),MONTH(B9),DAY(B9)))
Method 4 – Calculating Number of Days with Excel DATEDIF Function
Apply the DATEDIF function to calculate the number of days between today and another date in Excel.
The syntax of the function is:
=DATEDIF(start_date, end_date, “d”)The “d” argument is to get the difference in days unit.
The end date must be greater than the start date. Otherwise, the formula will result in an error. To avoid such incidents, we’ll show two separate cases where we deal with future and past dates compared to today’s date.
4.1 Past Date
Use the DATEDIF function to calculate the number of days between today and past dates.
The formula will be:
=DATEDIF(B7,TODAY(),"d")
4.2 Future Date
Use the DATEDIF function to calculate the number of days between today and future dates.
The formula will be:
=DATEDIF(TODAY(),B7,"d")
Method 5 – Calculating Number of Working Days Between Today and Another Date in Excel
Calculate the number of working days between today and another date using the NETWORKDAYS function in Excel. Consider holidays along with the weekends while determining the working days.
The syntax of this function is:
=NETWORKDAYS(start_date, end_date, [holidays])In the following dataset, we have some dates and holidays. Calculate the number of working days between those dates and today’s date.
The formula will be:
=ABS(NETWORKDAYS(B7,TODAY(),$B$16:$B$19))
Download Practice Workbook
Further Readings
- How to Calculate Expiration Date with Excel Formula
- How to Calculate Number of Months Between Two Dates in Excel
- How to Calculate Years from Today in Excel
- How to Calculate Years Between Two Dates in Excel
- Calculate Years and Months Between Two Dates in Excel
<< Go Back to Days Between Dates | Date-Time in Excel | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
