How to Count Days from Date to Today Automatically Using Excel Formula

Get FREE Advanced Excel Exercises with Solutions!

You may need to automate your results when examining financial data. Because updating the data on a daily basis is extremely time intensive. As a result, you might need to count the number of days between today and another date. In this tutorial, we will show you how to count days from date to today automatically using excel formula.


Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.


6 Best Ways to Count Days from Date to Today Automatically Using Excel Formula

In the sections below, we’ll show you the six most effective techniques to calculate days from a given date to the present day. We’ll use Excel’s built-in formulas as well as VBA formulas. An example data set is provided in the image below to help you complete the assignment.

Sample Data

1. Apply the TODAY Function to Count Days from Date to Today Automatically in Excel

The easiest way to count the difference in days from today is to use the TODAY function.

Syntax of the formula is,

=TODAY()-Cell(another date)

To apply the TODAY function, follow the steps below.

Step 1:

  • Firstly, type the following formula.
=TODAY()-B5

Count Days from Date to Today Automatically Using Excel Formula

Step 2:

  • Then, press Enter to get the first result.

Count Days from Date to Today Automatically Using Excel Formula

Step 3:

  • Finally, use the AutoFill handle tool to find the other results.

Count Days from Date to Today Automatically Using Excel Formula

Read More: Excel Formula to Calculate Number of Days Between Today & Another Date (6 Quick Ways)


2. Use the DAYS Function to Count Days from Date to Today Automatically in Excel

The syntax of the DAYS function is:

=DAYS(end_date, start_date)

To apply the DAYS function, follow the instruction below.

Step 1:

  • Type the following formula in cell C5. Here, end_date refers to today, and the B5 cell value is the start date.
=DAYS(TODAY(), B5)

Count Days from Date to Today Automatically Using Excel Formula

Step 2:

  • To see the first value, press Enter.

Count Days from Date to Today Automatically Using Excel Formula

Step 3:

  • Then, to get the other values, use the AutoFill handle tool.

Count Days from Date to Today Automatically Using Excel Formula

Read More: Calculate Number of Days between Two Dates with VBA in Excel


3. Utilize the DATEDIF Function to Count Days from Date to Today

The syntax of the DATEDIF function:

=DATEDIF(start_date,end_date, holidays)

To utilize the DATEDIF function follow the outlined steps below.

Step 1:

  • Enter the following formula in cell C5.
=DATEDIF(B5, TODAY(),"D")
  • B5 is the start_date, today is the end_date. “D” means full days.

Count Days from Date to Today Automatically Using Excel Formula

Step 2:

  • To see the result, press the Enter.

Count Days from Date to Today Automatically Using Excel Formula

Step 3:

  • To make changes to all cells, AutoFill the formulas.

Count Days from Date to Today Automatically Using Excel Formula

Notes.  The DATEDIF function is not very accurate. Excel doesn’t recommend to use of the function.

Read More: How to Use DateDiff Function in Excel VBA (5 Examples)


Similar Readings


4. Perform the ABS Function to Count Negative Days from Date to Today

Sometimes, you will not get value for negative results if we subtract today’s date from a future date. To get that, just nest the TODAY function inside the ABS function. To complete the steps, follow the instructions.

Step 1:

  • Firstly, write the following formula for the ABS.
=ABS(TODAY()-B5)

Count Days from Date to Today Automatically Using Excel Formula

Step 2:

  • Then, press the Enter.

Count Days from Date to Today Automatically Using Excel Formula

Step 3:

  • To get the full results in the column, use the AutoFill handle tool.

Count Days from Date to Today Automatically Using Excel Formula

Read More: How to Use COUNTIFS with Date Range in Excel (6 Easy Ways)


5. Apply a VBA Code for a Single Cell to Count Days Automatically

In addition to the previous methods, we can use VBA to do the counting. Simply, follow the steps below to have done it.

Count Days from Date to Today Automatically Using Excel Formula

Step 1:

  • At first, press  Alt  F11   to open the VBA Macro.
  • Click on the Insert.
  • Choose the Module.

Count Days from Date to Today Automatically Using Excel Formula

Step 2:

  • Paste the following VBA codes to get the result in D5.
Sub Count_days_from_today_for_a_cell()
'At first declare a variable
Dim ws As Worksheet
'Set your current worksheet Name
Set ws = Worksheets("Single cell VBA")
'set reference to the dates
Set Previous_Date = ws.Range("B5")
Set Todays_Date = ws.Range("C5")
'Apply formula to count days between two dates
ws.Range("D5") = Todays_Date - Previous_Date
End Sub

Count Days from Date to Today Automatically Using Excel Formula

Step 3:

  • Then, save the program and press  F5  to run it.
  • Therefore, you will get the difference of days in cell D5.

Use Formula

Step 4:

  • Repeat the previous steps for the rest cells, and your final result will look as in the image below.

Use Formula

Related Content: How to Calculate Remaining Days in Excel (5 Methods)


6. Run a VBA Code for a Range to Count Days from Date to Today

Apart from a single cell, we can also apply the VBA for a range to count the difference of days automatically. We will create a formula through VBA and apply it to our worksheet. Simply follow the outlines instructions.

Use Formula

Step 1:

  • To open the VBA Macro, press  Alt  F11 
  • Then, click on the Insert.
  • After that, select the Module.

Use Formula

Step 2:

  • Then, paste the following VBA code for entering the formula in cell E5.
Sub Count_days_from_today_in_a_range()
'At first declare a variable
Dim ws As Worksheet
'Set reference to your current worksheet Name
Set ws = Worksheets("Range VBA")
'Apply formula to count days between two dates
ws.Range("E5").Formula = "=DATEDIF(B5,C5,D5)"
End Sub

Use Formula

Step 3:

  • Save the program and press  F5  to run it.
  • As a result, you will see the result in cell E5 containing with formula. Now, we will apply the same formula to the rest of the cells.

Use Formula

Step 4:

  • Simply, drag down the Autofill handle tool to get the results in full.

Use Formula

Related Content: 3 Suitable Excel Formula to Count Days from Date


Conclusion

To recap, I hope you now understand how to automatically count days from a given date to today. All of these methods should be used to instruct and practice with your data. Look over the practice book and put what you’ve learned to use. Because of your vital support, we are motivated to continue delivering seminars like this.

If you have any questions, please do not hesitate to contact us. Please let us know what you think in the comments section below.

The Exceldemy staff will respond to your inquiries as quickly as possible.

Stay with us and keep learning.


Related Articles

Bhubon Costa
Bhubon Costa

Myself, Bhubon Costa, an engineer, is currently employed with Exceldemy as an Excel & VBA Content Developer. I appreciate solving analytical difficulties, finding practical answers, and, for the most part, I enjoy taking on new tasks. I write articles on Exceldemy about various ways to get out of Microsoft Excel's stuck conditions. My passion is to leave my mark on the world through my work and to have an impact on the community who benefit from it.

2 Comments
  1. Hi Costa,

    i am looking to have number of days counter in a cell, based on the text in another cell.
    For example if text in A1 Cell is Normal, than the counter (in A2) shall keep counting the days like 1, 2, 3 automatically depending on the date change.
    If text in A1 is anyother than “Normal” counter should show “0” and than again start counting if the text is again “Normal” in A1.

    • Reply Avatar photo
      Musiha Mahfuza Mukta Mar 27, 2023 at 11:41 AM

      Hi ATIF, Thanks for your comment. Here, I made a dataset keeping the Text value in A column, Date value in B column and Counter will be in C column. If you provide your Excel file, then it will be more useful. As per my understanding, I am providing the following VBA code.
      From Developer tab >> go to Visual Basic >> Insert a Module >> copy the code in that >> from Macros >> Run the code.
      code for days counter

      Sub Days_Counter()
      Count = 0
      For i = 1 To 11
      If Range("B1").Cells(i) <> Range("B1").Cells(i + 1) _
      And Range("A1").Cells(i) = "Normal" Then
          Count = Count + 1
          Range("C1").Cells(i) = Count
      Else: Range("C1").Cells(i) = 0
      End If
      Next i
      End Sub

      You can see the outcome below.
      output
      Where the counter counts single value for same date. I have highlighted the same date. Also, for “Abnormal” text the counter didn’t count.
      In the code, you must mention total Row number of your dataset in For Next loop.
      If you want only the text part, you can remove this portion

      Range("B1").Cells(i) <> Range("B1").Cells(i + 1)

      from code.
      Hopefully this will work. If you are still facing problem, then please comment with more details or the sample dataset.
      Regards
      Musiha Mahfuza Mukta| Team Exceldemy

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo