The CHOOSE function is used to select arbitrary values by numeric position. To summarize, WEEKDAY will give us a number between 1-7 for each day of the week, and we can use that result to figure out how many days we need to roll back. For example, WEEKDAY returns 7 for January 1, 2022, since the first is a Saturday: =WEEKDAY(DATE(2022,1,1)) // returns 7įor January 2, 2022, WEEKDAY returns 1, since the second is a Sunday: =WEEKDAY(DATE(2022,1,2)) // returns 1 By default, WEEKDAY returns 1 for Sunday and 7 for Saturday. WEEKDAY returns a number for each day of the week. To figure out the day of week, we use the WEEKDAY function. This is where the formula gets a bit tricky, because we need to combine two functions, WEEKDAY and CHOOSE, in a way that most users won't recognize. Now we have a pretty good idea of what we need to do, we just need to implement that behavior in a formula. And if the first is already a Sunday, we need to roll back 0 days. If the first is a Friday, we need to roll back 5 days. For example, if the first is a Tuesday, we need to roll back 2 days. How many days do we need to roll back? This depends on what day of the week the first day of a month lands on. However, if the first of the month is not a Sunday, we need to "roll back" some number of days to the prior Sunday. The first of the month is our start date. If the first of a month happens to be a Sunday, we're done. How can we calculate the last Sunday of the previous month? Before we get into specific functions, let's clarify the goal. If the first day of a month is not a Sunday, we need to start the grid on the last Sunday of the previous month. To make a proper calendar, we need the first day in our grid to start on Sunday. We still don't have a usable calendar, but we're getting closer! At the end of January, the month changes to February and the day becomes 1 again: Once we format the output with the custom number format "d", we see a total of 42 numbers, beginning with January 1. Now let's see what happens if we ask for 6 x 7 grid, starting with January 1, 2022: =SEQUENCE(6,7,DATE(2022,1,1)) That will cause Excel to display just the day numbers. To do that we can use the custom number format "d". SEQUENCE automatically defaults to a step value of 1, so the result is a list of serial numbers starting with 44562. Obviously, we don't want to display serial numbers in our calendar, we want to show days. To translate: we are asking for 7 numbers, in a 7 x 1 array, starting with January 1, 2022. Note: the DATE function is a safer way to hard code dates into formulas, since dates entered as text can be misinterpreted. For example, the formula below will generate dates for the 31 days of January 2022: =SEQUENCE(7,1,DATE(2022,1,1)) SEQUENCE with datesīecause Excel dates are just large serial numbers, the SEQUENCE function can easily be used to generate arrays of dates. These are just numbers, not dates, but you can see the core concept. The screen below shows how these formulas behave on a worksheet: For example, the following formula creates the numbers 1-10 in an array with 5 rows and 2 columns: =SEQUENCE(5,2)Īnd the formula below will fill a 7 x 6 grid of cells with the numbers 1-42: =SEQUENCE(6,7) SEQUENCE can generate arrays in rows and columns. The array spills into a vertical range of ten cells. The result is an array that contains the numbers 1-10. For example, to generate the numbers 1 to 10 in ten rows, you can use SEQUENCE like this: =SEQUENCE(10) // returns The SEQUENCE function can be used to generate numeric sequences. Conditional formatting is used to highlight the current date and holidays, and lighten days in other months. This is handled with the CHOOSE and WEEKDAY functions. The main challenge with this problem is figuring out what date to start with for a given month, which is always a Sunday. This works, because Excel dates are just serial numbers. The short version is that the SEQUENCE function outputs a 6 x 7 array of 42 dates in a calendar grid, formatted to display the day only. Custom number formats in Excel - overview.WEEKDAY function - overview and examples.Dynamic array formulas in Excel - overview.SEQUENCE is one of the original dynamic array functions in Excel, and a perfect fit for this problem. The solution explained below is based on the SEQUENCE function. The final calendar should place each day of the month in a grid with each week starting on Sunday, as seen in the example. In this example, the goal is to generate a dynamic calendar for any given month, based on a start date entered in cell J6, which is named "start" We assume that start is a valid first-of-month date like, ,, etc. See below for a formula that will automatically return the first day of the current month. Note: This example assumes the start date will be provided as the first of the month.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |