Welcome to Excel Jet consult blog post. In this post, we are going to learn how to write Excel formula to generate weekdays and Month. Let’s get started
Generate Weekdays Starting on Sunday
The first demo we want to cover is how to write the Excel formula to generate weekdays with Sunday is the first day of the week. To do that:
- Execute this formula:
=TEXT(SEQUENCE(7),"dddd")
- Click Enter
Formula Explanation
- SEQUENCE(7): function generates a sequence of numbers from 1 to 7, resulting in an array of 7 consecutive numbers. By default, it created a vertical array from 1 to 7.
- TEXT(…, “dddd”): this part of the formula takes the array generated by SEQUENCE(7) and converts each number into a corresponding day of the week. The “dddd” format code is used to specify that you want the full name of the day of the week (e.g., “Monday,” “Tuesday,” etc.).
In the screenshot below, we have the weekdays with Sunday as the first day of the week.
Generate Weekdays Starting on Monday
To generate the weekdays with the first day of the week starting on Monday:
- Execute this formula:
=TEXT(SEQUENCE(7)+1,"dddd")
- Click Enter
Formula Explanation
- SEQUENCE(7): generates a sequence of numbers from 1 to 7, creating an array of 7 consecutive numbers.
- SEQUENCE(7)+1: This part adds 1 to each number in the array generated by SEQUENCE(7). So, instead of starting with 1, it starts with 2 and goes up to 8.
- TEXT(…, “dddd”): function takes the modified array (with 1 added to each number) and converts each number into a corresponding day of the week using the “dddd” format code for full day names.
The result of the above formula return a list of the full names of the days of the week starting from Monday (1+1) and ending on Sunday (7+1) as seen in the screenshot below:
Generate List of Month
Finally, we are going to explore how to generate list of month. To do that:
- Execute this formula:
=TEXT(SEQUENCE(12)*28+1,"mmmm ")
- Click Enter
Formula Explanation
- SEQUENCE(12): generates a sequence of numbers from 1 to 12, creating an array of 12 consecutive numbers representing the months of the year.
- SEQUENCE(12)*28+1: This portion of the formula multiplies each number in the array by 28 and then adds 1. This is used to generate a list of sequential dates in the year. Assuming that the first date is 1 (which is common for the start of a year), this formula generates dates spaced 28 days apart.
- TEXT(…, “mmmm”): function takes the array of dates and formats them as full month names using the “mmmm” format code.
The result of this formula is a list of full month names starting from the date January 1st and continuing with each subsequent month, spaced 28 days apart.
See you in the next tutorial.