Bhubon Costa
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.
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
Step 2:
- Then, press Enter to get the first result.
Step 3:
- Finally, use the AutoFill handle tool to find the other results.
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)
Step 2:
- To see the first value, press Enter.
Step 3:
- Then, to get the other values, use the AutoFill handle tool.
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.
Step 2:
- To see the result, press the Enter.
Step 3:
- To make changes to all cells, AutoFill the formulas.
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
- [Fixed!] VALUE Error (#VALUE!) When Subtracting Time in Excel
- How to Create a Day Countdown in Excel (2 Examples)
- Add 30 Days to a Date in Excel (7 Quick Methods)
- How to Add 7 Days to a Date in Excel (3 Methods)
- Calculate Years in Excel from Today (4 Ways)
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)
Step 2:
- Then, press the Enter.
Step 3:
- To get the full results in the column, use the AutoFill handle tool.
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.
Step 1:
- At first, press Alt + F11 to open the VBA Macro.
- Click on the Insert.
- Choose the Module.
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
Step 3:
- Then, save the program and press F5 to run it.
- Therefore, you will get the difference of days in cell D5.
Step 4:
- Repeat the previous steps for the rest cells, and your final result will look as in the image below.
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.
Step 1:
- To open the VBA Macro, press Alt + F11
- Then, click on the Insert.
- After that, select the Module.
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
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.
Step 4:
- Simply, drag down the Autofill handle tool to get the results in full.
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.
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.
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.
You can see the outcome below.
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
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