How to Create a Schedule in Excel That Updates Automatically

 

Watch Video – Create a Schedule that Updates Automatically in Excel


Step 1 – Prepare The Calendar Layout

Before we delve into creating the schedule, You must first create the outline of the calendar first in which you’ll implement your formulas.

Steps

  • Place the date and month on the sheet.
  • Set to date and month to 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 – Add Formulas to the Calendar Outline

Steps

  • Select cell D4 and enter the following formula to extract today’s date:

=TODAY()

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

  • Select cell H4 and enter the following formula:

=MONTH(D4)

using month function to automatically updates the schedule

  • Highlight cell B7 and enter the following formula:

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


Formula Breakdown

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

⮚ Returns the date in the proper date format. The month is from cell I4, the year mentioned is 2022, and the date is 1.

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

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

  • Select cell C7 and enter the following formula:

=B7+1

  • Drag the Fill Handle from B7 to H7 to fill the cell range with dates starting from October 29 to September 4.

  • Choose cell C8 and enter the following formula:

=B7+7

  • Drag the Fill Handle to cell B12.

  • Repeat the same process for the rest of the cells to fill all of your cells with the weekdays in a month.

  • Right-click the cells and select Format Cells from the menu.

format calendar as to have only day numbers.

  • Select the Number tab.
  • Enter Custom options and select the Type field.
  • Enter dd only so the user only sees the date’s day portion in the table.
  • Click OK.

  • Your calendar now has the dates in a double-digit format.

  • We have the dates from the previous and next month, which we want to avoid. So, you need to conditionally format the values in your table.
  • Select the whole calendar.
  • 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.
  • Enter the following formula:

=MONTH(B7)<>$H$4

  • After entering the formula, click 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 white as your fill color.

  • Switch to the Font tab and choose white as your font color.
  • Click OK.

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

Steps

  • The list of the programs in September is list with their associated scheduled dates.

event listing to create automatically updates schedule in Excel

  • Modify the existing table by adding an extra cell on the right side of each date. You’ll use these cells to store event information.

calendar created to create schedule that update automatically.


Step 4 – Link Programs with Calendar

Using functions like TEXTJOIN and FILTER you can now link the calendar’s dates to specific events.

Steps

  • Select cell C7 and enter the following formula:

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

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 a match is found, 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,””))

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

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

=TEXTJOIN(CHAR(10),TRUE,FILTER(plan[Program],plan[Date]=F7,""))

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

  • Drag the Fill Handle to cell G12.
  • Repeat the same process for the other weeks to complete your calendar with its 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.


Related Articles


<< Go Back to 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

4 Comments
  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

      Greetings,
      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,
      https://www.exceldemy.com/wp-content/uploads/2023/04/Attendence-SheetMonthly.xlsx

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo