# Automate Weekly Date Updates in Excel

Bottom Line: Learn to create a date field that automatically updates weekly.

Skill Level: Beginner

## Video Tutorial

I received an inquiry from Rhonda asking how to automate the regular update of a date field weekly.

The solution lies in leveraging Excel's built-in functions, TODAY and WEEKDAY, to dynamically calculate the date for each week.

If you're like Rhonda and would like to know how to avoid manually updating a date field each week, let me walk you through writing a formula to make it automatic, saving you time and effort.

## How to Create a Date Field That Automatically Updates Weekly

Let's break down the process step by step:

1. TODAY Function: First, we begin by utilizing the TODAY function, which always returns the current date. This function is essential as it ensures that our date field updates every time the spreadsheet is opened or recalculated.
1. WEEKDAY Function: Next, we incorporate the WEEKDAY function. This assigns a number from one to seven to each day of the week. By specifying TODAY as the serial_number argument and 2 for the return_type to represent Monday, we obtain the numeric value corresponding to the current day of the week.
1. Mathematical Calculation: Now comes the math part. We subtract the numeric value obtained from the WEEKDAY function (representing the current day of the week) from the current date. Adding 1 to the result ensures that we obtain the date for Monday of the current week. For instance, if today is Friday (resulting in a numeric value of 5 for WEEKDAY), subtracting 5 from the current date and adding 1 would yield Monday's date for the current week.

## Combine Into a Formula

To streamline the process further, all these steps can be combined into a single formula. Basically, this formula dynamically calculates the date for the desired day of the current week.

Here is the formula:

``=TODAY()-WEEKDAY(TODAY(),2)+1``

If you want the formula to return a day other than Monday, just change the 2 in the WEEKDAY formula to the appropriate day designation. So, whether it's Monday, Wednesday, or Friday that you need, Excel has you covered.

## Conclusion

Automating the update of date fields in Excel not only saves a little time but also minimizes the risk of errors associated with manual entry. As usual, by tapping into the power of Excel's functions, you can create efficient workflows that enhance productivity.

Questions or comments? We'd love to hear them. You can write them here.

• I enjoy these formulas as they are very useful. One more step to make it even easier to utilize is for you to type the formula as text in the conclusion of your article so that we can copy and paste the final formula into our Excel workbook without us having to type it out. Yes, I know, I am lazy and could type it since it is short. However, there is always the chance of a typo when doing so. Just the little extra step would be helpful.
Thanks for considering this idea.
Tim

• Great idea, Tim! We added the formula text to the post. It’s in the Combine Into a Formula section.

Thanks! 🙂

• Mats Grönvall says:

You may simplify the math by using return type 3 with the WEEKDAY function, returning 0 (Monday) through 6 (Sunday), and omit the +1.
In general I find return type 3 more convenient when doing math on weekdays.

/Mats

• Great point Mats! Thanks for sharing. That will work for Monday, but unfortunately the other days of the week don’t have an option to start at zero.

Generic filters
Exact matches only

#### Excel Shortcuts List

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List