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 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
Thank you for this
Hello Michael,
You are most welcome. Keep learning Excel with ExcelDemy. We provided a formula to auto populate events from entry data to calendar sheet.
Regards
ExcelDemy
Hi Rabayed
Thank you so much for the help
Can I have entries of an event in the first sheet and have it automatically populated in the calendar sheets as per the event
Can a formula also combine cells according to events that are more than one days
Thank you so much
https://docs.google.com/spreadsheets/d/1bt-FUhrareuYc3lH1QuI68TOfSezkTTb/edit?usp=sharing&ouid=108748915799897862730&rtpof=true&sd=true
Hello Michael,
You are most welcome. You can auto populate events in the calendar sheet from your entry data sheet but Excel formulas has limitations over handling multiple overlapping events. Formulas will concatenate multiple events into a single cell, but each overlapping event will be separated by a line break or other delimiters.
You can use the following formula in your event cell to auto populate events.
=IFERROR(TEXTJOIN(CHAR(10), TRUE, FILTER(‘Entry Data’!$B$2:$B$100, (‘Entry Data’!$D$2:$D$100 <= B10) * ('Entry Data'!$D$2:$D$100 + 'Entry Data'!$C$2:$C$100 - 1 >= B10) * (‘Entry Data’!$E$2:$E$100 = 8) * (‘Entry Data’!$F$2:$F$100 = 2024))), “”)
Based on Month please change the Month Number and Cell reference for each month and each cell.
If an event spans multiple days, the formula will check if the current day falls within the event duration and will display the event in the cell. If multiple events occur on the same day, they will all be concatenated in the same cell.
Download the Excel file:
Monthly Event Chart
Regards
ExcelDemy
Would there be a way to look at this as a calendar quarter or year?
Hello Destiny Rivera,
Yes, you can modify the schedule to display by calendar quarter or year. You would need to adjust the date formulas and ranges to reflect quarterly or yearly data. For quarters, use a formula that groups dates into one of the four quarters of the year. For a yearly view, adjust the schedule to display the dates and events by the full year.
For Quarters:
Use the MONTH function to categorize dates into quarters:
=CHOOSE(MATCH(MONTH(A2),{1,4,7,10}),”Q1″,”Q2″,”Q3″,”Q4″)
This formula checks the month of the date in cell A2 and returns “Q1” for January to March, “Q2” for April to June, etc.
For Year:
You can simply extract the year from a date:
=YEAR(A2)
Regards
ExcelDemy