Description
In this video I'll show you how to set Monday as the first day of the week in Microsoft Excel. By default, Excel's WEEKDAY and WEEKNUM functions treat Sunday as the first day of the week, which can throw off your weekly reports if your business week starts on Monday. There isn't one global setting for this in Excel. Instead, you tell each function to start the week on Monday.
Timestamps:
Introduction: 0:00
Number Weekdays Starting Monday: 0:26
Count Week Numbers Starting Monday: 1:32
Use ISO Week Numbers: 2:25
Conclusion: 2:59
Check out my website for other great tutorials about all kinds of technology: https://maxdalton.how/
Support this channel by becoming a member: https://www.youtube.com/channel/UCBbDWMccTJCL0WmbMHNLZIw/join
#excel #microsoftexcel #exceltutorial #techtips #tutorial
Video Transcript:
By default, Excel's WEEKDAY and WEEKNUM functions treat Sunday as the first day of the week, which can throw off your weekly reports if your business week starts on Monday. There isn't one global setting for this in Excel. Instead, you tell each function to start the week on Monday.
Step 1. Open your Excel spreadsheet. For this example, I'm working with a daily sales sheet for a comic book shop that has dates in column A, and empty Weekday Number and Week Number columns next to them.
Step 2. Click in cell B2, the first empty cell in the Weekday Number column, and start typing the formula =WEEKDAY(A2, followed by a comma. Excel shows a list of return types. The one we want is 2, which numbers the days from 1 for Monday through 7 for Sunday.
Step 3. Type 2 and a closing parenthesis to finish the formula.
=WEEKDAY(A2,2)
Step 4. Press "Enter" on your keyboard. Our first date, October 4th, is a Sunday, so it returns 7 instead of the 1 you'd get with Excel's default setting.
Step 5. Click cell B2, and then double-click the fill handle in the bottom right corner of the cell to copy the formula down the rest of the column.
Now let's make the week numbers start on Monday too, so every Monday begins a new week.
Step 1. Click in cell C2, the first empty cell in the Week Number column, and start typing =WEEKNUM(A2, followed by a comma. In the list that appears, 2 tells WEEKNUM that weeks begin on Monday.
Step 2. Type 2 and a closing parenthesis to finish the formula.
=WEEKNUM(A2,2)
Step 3. Press "Enter" on your keyboard. October 4th lands in week 40.
Step 4. Click cell C2, and then double-click the fill handle in the bottom right corner of the cell to copy the formula down the column. October 5th, the Monday right after October 4th, starts week 41.
If you work with international teams or anyone who uses the ISO week date standard, use the ISOWEEKNUM function instead. ISO weeks always start on Monday, and week 1 is the week that contains the first Thursday of the year.
Step 1. Click in an empty cell, like F2, and type this formula.
=ISOWEEKNUM(A2)
Step 2. Press "Enter" on your keyboard. October 4th is in ISO week 40, the same as WEEKNUM here, but the numbers can differ near the start and end of the year.