How to Create Automatic Day Counter in Excel [8 Methods]

To create automatic day counter in Excel, follow the instructions below:

1. Select a cell.
2. Type the formula: =TODAY()-B2
3. Use the Fill Handle to copy the formula down.

The formula calculates the difference between the current date from the date value stored in cell B2. Here, the TODAY function will return the current date.

Method 1: Create an Automatic Day Counter in Excel Using Subtraction Formula

We have a dataset of name lists with their starting and today’s dates. Now, I have to count each employee’s exact number of days from the starting date. To solve this, I will create an automatic day counter in Excel.

Here, I will subtract the starting date from today’s date to calculate the total days. First, we will apply the formula in cell D2. Then, copy the formula from cell D2 to cell D10.

Syntax

=cell_reference- cell_reference

Formula

=C2-B2

To create an automatic day counter in Excel using simple subtraction formula:

1. Select cell D2.
2. Type the formula: =C2-B2
3. Drag the formula with the Fill Handle.
   

Here we have calculated total days by subtracting the starting date from today’s date. In the range D2:D10, we have stored the counted days.

Method 2: Create an Automatic Day Counter in Excel Using DAYS Function

The DAYS function will calculate the total number of days of an individual by counting the days between the ending date and the starting date.

Syntax

=DAYS(end_date, start_date)

Formula

=DAYS(C2,B2)

Formula Explanation

The DAYS function subtracts the earlier date (in B2) from the later date (in C2) and returns the difference in days. The result is a numerical value representing the number of days between the two dates.

Now follow these steps to create an automatic day counter in Excel:

1. Select a blank cell.
2. Copy the formula: =DAYS(C2, B2)
3. Paste it to the selected cell.
4. Double-click on Fill Handle to copy the formula down.
   

Here, I have counted the number of days from the starting date to today of each employee. The formula subtracts the date of the starting date from the present date. Then it generates the result in column D.

Method 3: Create an Automatic Day Counter in Excel with TODAY Function

TODAY function in Excel returns today’s date. So, you can calculate the total days not even entering the today’s date manually. You can subtract the starting date from today’s date by using the TODAY function.

Syntax

=TODAY()-cell_reference

Formula

=TODAY()-B2

Formula Explanation

The subtraction TODAY()-B2 calculates the difference between the current date and the date in cell B2. The result is a numerical value representing the number of days between the current date and the date in B2.

To create an automatic day counter using TODAY function:

1. Select cell C2.
2. Write the formula: =TODAY()-B2
3. Use the Fill Handle to copy the formula from cell B2 to B10.
   

The formula first finds out the present date, and then subtracts the starting date to count the number of days. Then we stored the result in the range D2:D10.

Method 4: Create an Automatic Day Counter in Excel Combining DATE, MONTH, & DAY Functions

DATE function takes the input in three segments, year, month, and day. If you want to subtract the starting date from today’s, you can simply use this formula. It helps to count the exact number of days.

Syntax

=DATE(YEAR(cell_reference), MONTH(cell_reference), DAY(cell_reference)) - DATE(YEAR(cell_reference), MONTH(cell_reference), DAY(cell_reference))

Formula

=DATE(YEAR(C2), MONTH(C2), DAY(C2)) - DATE(YEAR(B2),MONTH(B2),DAY(B2))

Formula Explanation

DATE(YEAR(C2), MONTH(C2), DAY(C2)) constructs a new date using the year, month, and day components of the date in cell C2, and similarly, DATE(YEAR(B2), MONTH(B2), DAY(B2)) constructs a date using the components of the date in cell B2. The subtraction of these two constructed dates results in a numerical value representing the difference in days between the two original dates.

Here’re the steps to create an automatic day counter in Excel using DATE, MONTH, & DAY functions:

1. Select cell D2.
2. Write the formula: =DATE(YEAR(C2), MONTH(C2), DAY(C2)) – DATE(YEAR(B2),MONTH(B2),DAY(B2))
3. Double-click on the Fill Handle to copy the formula down.
   

The result shows the total number of days by counting the difference of the dates. It stores the result in cells D2:D10.

Method 5: Automatic Day Counter in Excel with DATEDIF Function

DATEDIF Function counts the difference between two dates in different units, such as days, months, or years. However, the end date must be greater than the start date. Otherwise, it will show an error.

Here, I will use to count days between two dates and create an automatic day counter by using the DATEDIF function.

Syntax

=DATEDIF(start_date,end_date,unit)

Formula

=DATEDIF(B2,C2,"d")

Follow these steps to create an automatic day counter in Excel using DATEIF function:

1. Select cell D2.
2. Copy the formula: =DATEDIF(B2,C2,”d”)
3. Paste it to cell D2.
4. Drag the formula using the Fill Handle.
   

The formula calculated the difference of the date value of cells B2 and C2 returns the results in days. Similarly, the formula worked for the other cells. I stored the result in the range D2:D10.

Method 6: Create an Automatic Working Day Counter in Excel Using NETWORKDAYS Function

The NETWORKDAYS function will calculate the total working days between two dates. It eliminates the weekends by default, Saturday and Sunday. Other than that, you can also input the holidays manually.

Here we have the starting date and today’s date. Then there is a list of holidays. Now, I will calculate the total working days between two dates for each individual.

Syntax

=NETWORKDAYS(start_date, end_date, holiday_range)

Formula

=NETWORKDAYS(B2,C2,$E$2:$E$10)

Formula Explanation

The NETWORKDAYS function calculates the working days between the dates of cells B2 and C2 excluding weekends, Saturdays, and Sundays. It also excludes the other holidays by this range $E$2:$E$10. By adding dollar signs, it fixes the range while copying down the formula.

Follow these steps to create an automatic day counter in Excel using NETWORKDAYS function:

1. Select a blank cell.
2. Type the formula: =NETWORKDAYS(B2,C2,$E$2:$E$10)
3. Copy the formula from D2 to D10 with the Fill Handle.
   

The formula calculates the duration between two dates stored in B2:B10 and C2:C10 excluding the standard holidays along with the dates stored in E2:E10. We have stored working days in G2:G10.

Method 7: Create an Automatic Working Day Counter in Excel Using NETWORKDAYS.INTL Function

While using NETWORKDAYS, it only takes weekends, Saturdays, and Sundays by default. You can customize the weekends, by using NETWORKDAYS.INTL function. This function calculates the duration between two specified dates and excludes the weekend and the holiday date lists.

Syntax

=NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays])

Formula

=NETWORKDAYS.INTL(B2,C2,11,$E$2:$E$10)

Formula Explanation

The NETWORKDAYS.INTL function calculates the number of whole workdays between the start date (B2) and end date (C2), excluding weekends and holidays. The weekends are defined by the parameter “11” (Sunday and Monday). The specified holidays in the range $E$2:$E$10 are also excluded from the count.

Now follow the steps:

1. Select cell G2.
2. Type the formula: =NETWORKDAYS.INTL(B2,C2,11,$E$2:$E$10)
3. Use the Fill Handle to copy the formula down.
   

The formula returns the number of weekdays within the specified dates and excludes days of this range $E$2:$E$10 and weekends (only Sunday). It stores the result in cells G2:G10.

Method 8: Automatic Day Counter by Power Query

Power Query is an Excel tool that connects to the data source and applies transformation steps to calculate the number of working days between two selected dates. Now, go through the steps below:

1. Go to the Data tab.
2. Select the range $A$1:$D$10.
3. Click on From Table/Range under the Get & Transform Data group.
4. Click OK on the Create Table dialog box.
   It will open the Power Query Editor.
5. Go to the Add Column tab in the Power Query Editor.
6. Select the columns of Starting Date and Today by pressing CTRL and clicking on these two columns.
7. Click the drop-down arrow of Date.
8. Choose Subtract Days.
   

By following the steps, it adds a column of subtraction at the right. It stores the result in the new column by subtracting the starting date from today’s date.

Conclusion

To sum up, I have added 8 methods to create an automatic day counter in Excel. Now, you can calculate the exact days between two specific dates. Rather than that, if you want to compute the weekdays, I have discussed two methods based on the working days. You will get to customize the weekends and holidays as well. Here, to create an automatic day counter, you will also have a guided outline using Power Query in Excel rather than the formulas. So, I hope you can create an automatic day counter by following this article and making your day-to-day work easier.

Frequently Asked Questions

What is the formula for date in Excel?

To work with dates in Excel, use the DATE function with the syntax =DATE(year, month, day). For example, =DATE(2023, 11, 10) creates a date for November 10, 2023. Additionally, functions like TODAY() and NOW() provide the current date and time, while YEAR(), MONTH(), and DAY() extract components from a date.

How do I add 30 days to a date in Excel?

To add 30 days to a date in Excel, follow these steps:

1. Input the date you want to start within a cell. For example, if your date is in cell A1, enter the date (e.g., “2023-01-01”).
2. In a different cell, use the following formula to add 30 days to the initial date: =A1 + 30
3. This formula adds 30 days to the date in cell A1.
4. Format the cell containing the result to display the date in the desired format.
5. Right-click on the cell, choose Format Cells, and select the appropriate date format.

This process allows you to easily add a specific number of days to a given date in Excel.