Excel Formula to Calculate Number of Days Between Today and Another Date: 5 Methods

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.

using today function to calculate today's date

  • Select cell C9 => Write the formula:
=$C$6-B9
  • Press Enter.

subtracting to calculate number of days between today and another date

  • 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.

negative number of days after applying fill handle

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

using abs function to get positive number of days

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

using if with subtraction formula to calculate number of days

 


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

using days function to get number of days between today and another date

Note: Use the DAYS360 function instead of the DAYS function to base the calculation on a 360-day year.

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

using date function to calculate number of days


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

using datedif function with past dates to find number of days


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

using datedif function with future dates to find number of days

Note: The DATEDIF function is not available in the list of Excel functions. We have to type all the arguments manually to use this function.

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.

dataset of another date and holidays

The formula will be:

=ABS(NETWORKDAYS(B7,TODAY(),$B$16:$B$19))

using networkdays function to calculate number of working days

Note: Use NETWORKDAYS.INTL function instead of NETWORKDAYS to customize the weekends.

Download Practice Workbook


Further Readings


<< Go Back to Days Between Dates | Date-Time in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Aung Shine
Aung Shine

Aung Shine completed his bachelor’s in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. It has been almost 2 years since he joined SOFTEKO and actively working on the ExcelDemy project. Currently he works as a Team Leader where he guides his team members to create technical content. He has published 150+ articles and reviewed 50+ articles. He has also solved various user problems before. He has interests in Data Analysis, Power Query, Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo