Automate Weekly Date Updates in Excel

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

Skill Level: Beginner

Video Tutorial

Watch on YouTube & Subscribe to our Channel


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.

Write a formula that returns a certain day of the week and updates 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.
TODAY Function
  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.
Use the WEEKDAY function, using TODAY as the first argument and 2 for the second argument
(2 representing Monday).
  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.
Calculate the desired weekly date (Monday's) by subtracting cell C3 from B3 and adding 1.

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.

Combine TODAY and WEEKDAY formulas.

Here is the formula:


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.


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.


Your email address will not be published. Required fields are marked *

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

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


Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

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

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter