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.
Step 2 – Add Formulas to the Calendar Outline
Steps
- Select cell D4 and enter the following formula to extract today’s date:
=TODAY()
- Select cell H4 and enter the following formula:
=MONTH(D4)
- 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.
- 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.
- 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.
- 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.
- The dates from the other months are now omitted from the view.
Step 3 – Enlist All Scheduled Programs
Steps
- The list of the programs in September is list with their associated scheduled dates.
- 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.
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.
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,""))
- Drag the Fill Handle to cell G12.
- Repeat the same process for the other weeks to complete your calendar with its schedule.
Read More: How to Make a Daily Schedule in Excel
Download this practice workbook below.
Related Articles
- How to Create a Weekly Schedule in Excel
- How to Create a Recurring Monthly Schedule in Excel
- How to Make an Hourly Schedule in Excel
<< Go Back to Make Schedule in Excel | Excel for Business | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
This was super helpful for what I was trying to do! Thank you so muchn
Hello, Andy W!
Thanks for your appreciation.
Regards
ExcelDemy
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.
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