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.


Download Practice Workbook

Download this practice workbook below.


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

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

Steps

  • 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

Read More: How to Make a Work Schedule in Excel (3 Handy Examples)


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.

Steps

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

=TODAY()

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

  • Then select cell H4 and enter the following formula:

=MONTH(D4)

using month function to automatically updates the schedule

  • Then select 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)

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

=B7+1

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

=B7+7

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

=MONTH(B7)<>$H$4

  • 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

Read More: How to Make a Daily Schedule in Excel (6 Practical Examples)


Similar Readings


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.

Steps

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

Read More: How to Create a Monthly Schedule in Excel (3 Simple Ways)


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.

Steps

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

=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 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 here 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:

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

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 Create a Weekly Schedule in Excel (2 Suitable Methods)


Conclusion

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 macro-enabled workbook is available to download where you can practice these methods.

Feel free to ask any questions or provide feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.


Related Articles

Rubayed Razib Suprov

Rubayed Razib Suprov

Hi, I am Rubayed Razib Suprov. Graduated from the Bangladesh University of Engineering Technology in Naval Architecture and Marine Engineering department. I joined Exceldemy on March 2022. I have a keen interest in both fluid dynamics and data analysis. I try to teach people what I learned so far about the Excel tool to date, and hope I can make some positive impacts.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo