目次
Make a Monthly Calendar using ArrayFormula in 3 Steps
Imagine dates are stored in a single column. The rolling date sheet, where the total days starting from the first day of a given month are shown, seems versatile for working with dates like an expense tracking and daily sales report. Fortunately, it can be done manually, entering the first day in the top and scrolling a small blue box in the lower right corner down to the bottom of a column where you want them to end.
Yes, it’s easy, but also a little bit frustrating as you have to check the last day of the month looking at calendar each time. You might wonder if there is a way to have the month’s days update automatically based off of the entered value in the A1 cell… just like this (↓)
You can do this using the functions with the ArrayFormula!
1. What is ARRAY-FORMULA?
If you want to auto-fill some across a column, it is best done using ArrayFormula that automatically expands into subsequent cells from a single cell. Before having ArrayFormula, you’ve probably typed a formula in the first cell then copied it all the way down the column. However, ArrayFormula powerfully fixes this problem, making changes in just one place. For a starting point, let’s see what that is with a simple example.
1-1. Using ArrayFormula with Row function
- Enter “2019/01/01” into the A1 cell
- Enter “=A1+ROW(A1)” into the A2 cell to understand what ROW() is
- Enter “=ARRAYFORMULA(A1+ROW(A1:A30))” into the A2 cell instead
An array of sequential number is best produced using the function ROW(), which literally returns the ROW number of a given cell. In primary use, Row(A1) returns “1” and Row(A33) returns “33”. But, as A1 is a date that you entered above, the returned values by default will also be formatted as date (“2019/01/01” + “2019/01/01” = “2019/01/02”). You can apply the ROW function to create a date sequence that increments by day just like a row does by one.
To make ArrayFormula applied for every row that you aim for, you MUST tell Sheets a RANGE of cells. In this case, let’s make it from A1 to A30 (A1:A30) as January has 31 days. It certainly works out if the month’s days consists of 31 days. But, it doesn’t in February and April both end earlier than 31 days.
2. How to know the last day of a month
ArrayFormula is absolutely powerful but instead, you need to tell Sheets your order as specific as possible. In the above situation, redundant few days loomed out for the months that finish earlier than 31 days. Then, what you want Sheets to understand next is if it’s like the case of January that exactly ends on 31th, it’s okay(TRUE) to use “A1+ROW(A1:A30)” but if it’s the case of such as February that ends on 28th, it’s NOT okay (FALSE) to present redundant few days that overlap with the next months. Then, it gets important to know the last day of a given month but fortunately, Sheets has a way to figure it out. As follows, you will learn how to use the EOMONTH(End of Month) formula.
2-1. How to use EOMONTH formula
- Enter “=EOMONTH(A1, 0)” into the B1 cell
- You will get the last date of a specified month
The syntax for the function is:
② Months: that move either in forward or backward in time, i.e., -2 means the last day of 2 months earlier while 0 means the last day of this month. As you need the last day of this month in this case, you select 0.
3. Let ArrayFormula know TRUE or FALSE using IF formula
Now that you can calculate the last date of a given month, all that is left to do is letting ArrayFormula understand when it’s wrong to show redundant few days and when it’s right to use “A1+ROW(A1:A30)” directly. When it comes to TRUE or FALSE, IF formula most certainly solves it. Let’s set a simple question as follows to use IF formula,
(The question can be written as = A1+ROW(A1:A30)<=EOMONTH(A1,0) in IF formula.)
→ YES(TRUE): let it be (show the month’s dates for 31 days)
→ NO(FALSE): make it blank (“_”)
3-1. Combine the multiple Functions in an Array Formula
- Enter “= A1+ROW(A1:A30)<=EOMONTH(A1,0)" into ① of the syntax below
- Enter “= A1+ROW(A1:A30)” into ② of the syntax
- Enter = “_” into ③ of the syntax
- Combine the formula that you’ve created above with Arrayformula and put it into the A2 cell.
If dates are not correctly shown, click here.
