How to Create a Schedule in Excel That Updates Automatically

Creating a schedule is a part and parcel of many of our lives. Creating the schedule automatically will be immensely helpful for many people. If you are curious to know how you can create a schedule in Excel that updates automatically, then this article may come in handy for you. In this article, we will discuss how you can create a schedule in Excel that updates automatically.

Watch Video – Create a Schedule that Updates Automatically in Excel

How to Create a Schedule That Updates Automatically in Excel: Step-by-Step Procedure

Below we are going to present four step-by-step procedures following which you can create a dynamic schedule with a calendar. Which will update automatically as the date changes.

For better functionality, try to use the Microsoft 365 version for this method.

Step 1: Prepare Layout of Calendar

Before we delve into creating the schedule, it is imperative that we create the outline of our calendar first, in which we are going to implement the formulas.


  • To begin, we need to prepare the layout of the outline of the calendar.
  • To make it dynamic, we need to place the date and month on the sheet.
  • The date and the month should be dynamic to today’s date.
  • Our calendar will follow the weekdays starting from the Monday format.

how to create a schedule in excel that updates automatically

Step 2: Formulize Calendar Outline

After we get the outline, it is time to formulate the calendar to make it dynamic. The functions like TODAY, MONTH, DATEand WEEKDAY to achieve this.


  • As we have the structure of the calendar plotted in the worksheet, we formulate the calendar to make it more dynamic.
  • To extract today’s date in the dataset, select cell D4, and enter the following formula:


use of functions to create a schedule that updates automatically in Excel

  • Then select cell H4 and enter the following formula:


using month function to automatically updates the schedule

  • Then select cell B7 and enter the following formula:


Formula Breakdown

  • DATE(2022,$I$4,1)

⮚ This will return the date in the proper date format. The month is from cell I4, and the year mentioned is 2022. And the date is 1.

  • DATE(2022,$I$4,1)-WEEKDAY(DATE(2022,$I$4,1),2)+1

This will subtract the weekday from the date value. It will make sure that Monday stays at the front of the calendar.

  • Then select cell C7 and enter the following formula:


  • Then drag the Fill Handle to the cell H7 from the cell B7.
  • Doing this will fill the range of cells with dates starting from the 29th of October to the 4th of September.

  • Then again, select cell C8 and enter the following formula:


  • And the Fill Handle to cell B12.

  • Repeat the same process for the rest of the cells.
  • Finally, we got the dates for all of the weekdays in a month.

  • The right clicks on them and, from the context menu, click on Format Cells.

format calendar as to have only day numbers.

  • In the Format Cells window, go to the Number tab.
  • Then in the Custom options, select the Type field.
  • In the Type field, enter dd only.
  • This will allow the user to see only the day portion of the date.
  • Click OK after this.

  • After this, our calendar will now have the dates in a double-digit format.

  • We have the dates from the previous and next month.
  • But we want to avoid that.
  • To avoid that, we need to conditionally format the values.
  • For this, select the whole calendar and then go to the Home tab > Conditional Formatting > New Rule.

conditional formatting to blur date from other month.

  • In the Edit Formatting Rule window, select Use a Formula to Determine Which Cells to Format.
  • Then enter the following formula:


  • After entering the formula, click on the Format button.

conditional formatting formula to format values.

  • In the Format Cells dialog box, click on the Fill tab.
  • In the Fill tab, choose the color white as the fill color.

  • Then switch to the Font tab, and choose the Color white as the font color.
  • Click OK after this.

choose font color to hide cell value that out of month value

  • The dates from the other months are now omitted from the view.

unformatted calendar to create schedule

Step 3: Enlist All Scheduled Programs

We created the calendar and made it dynamic. Now we are going to fill out the monthly tasks or program schedules for each day. The date format should be maintained properly.


  • The list of the programs in September month is enlisted with their scheduled date.

event listing to create automatically updates schedule in Excel

  • And at the same time, we need to modify the existing table.
  • We should add an extra cell just on the right side of each date.
  • These cells will be used to store important event information.

calendar created to create schedule that update automatically.

Step 4: Link Programs with Calendar

We have both a calendar and a list of programs or events. We can now link the events with the calendar for each day. Functions like TEXTJOIN and FILTER are going to be used here.


  • Now we are going to link the event list with the date in the calendar.
  • To do this, select cell C7 and enter the following formula:


This formula will add the event associated with that date into the cell.

Linking events with calendar to create a schedule that updates

Formula Breakdown

  • FILTER(plan[Program],plan[Date]=B7,””):

⮚ This formula will look for the value in cell B7 in the table plan’s program table header column. If the match is found, then it will present the whole row of information. Otherwise, it will extract a blank cell.

  • TEXTJOIN(CHAR(10),TRUE,FILTER(plan[Program],plan[Date]=B7,””))

⮚Here, the TEXTJOIN function will join a separate line from the output of the FILTER function. The CHAR(10) [New line] here is acting as the delimiter between two or more text lines.

  • Then drag the Fill Handle to cell C12.
  • The cells are currently in a blank state because there is no event associated with the dates mentioned here.

  • Repeat the same process for the subsequent cell.
  • Select cell G7 and enter the following formula:


enter formula to link calendar with the event to create automatically updated schedule.

  • And drag the Fill Handle to cell G12.
  • Again, repeat the same process for the other weeks.
  • By doing this, we will complete the calendar with the schedule.

how to create a schedule in excel that updates automatically

Read More: How to Make a Daily Schedule in Excel

Download Practice Workbook

Download this practice workbook below.


To sum it up, the issue of how we can create a schedule in Excel that updates automatically by 4 different steps. For this problem, a workbook is available to download where you can practice these methods. Feel free to ask any questions or provide feedback through the comment section.

Related Articles

<< Go Back to Make Schedule in Excel | Excel for Business | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!
Rubayed Razib Suprov
Rubayed Razib Suprov

Rubayed Razib, holding a BSC degree in Naval Architecture & Engineering from Bangladesh University of Engineering and Technology, serves as a devoted member of the ExcelDemy project. He has contributed significantly by authoring numerous articles and showcasing proficiency in VBA. Razib efficiently automates Excel challenges using VBA macros and actively participates in the ExcelDemy forum, providing valuable solutions for user interface challenges. Apart from creating Excel tutorials, he is interested in Data Analysis with MS Excel,... Read Full Bio

  1. This was super helpful for what I was trying to do! Thank you so muchn

  2. Hi,

    I want to create a monthly excel attendance working schedule with formulate to calculate hours worked based on time in time out everyday for the whole month with daily hours totals excluding 1 hour lunch, total for the week and total for the month for each of the 10 employees. I have an example of one that was created with option of choosing time or off or on leave from the drop down arrow without typing. I like that but want to know how I can create mine from scratch.

    • Reply Avatar photo
      Rubayed Razib Suprov Apr 10, 2023 at 1:13 AM

      I understand your query. You asked about attendence sheet for both monthly and weekly basis. But my suggestion will be to go on with the monthly basis assesment. In this way your job will be much smoother. Secondly I provided a demo sheet that actually contains the attendence with time picker drop down, and summation of total hour of work in a month.The time picker need to have a fixed time and they are specified below the sheet.You just need to enter the month and year in the beginning and then you can enter the in time and out time using the time picker.
      The download file can be accessed from the link below,

Leave a reply

Advanced Excel Exercises with Solutions PDF