CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
googlesheet test
googlesheet test

Reputation: 247

Populating row of dates depending on month picked

I have a sheet where the user is supposed to pick a Month-Year from a dropdown in one cell A2. I have a formula I found that populates the row depending on the year and month picked but I am wondering if it's possible to dynamically delete the columns when a day(specifically the 31st, or 29th-31st this february) doesnt exist for a specific month. For example, April 31 - Column AF exists but the cell for date is blank because that date doesnt exist. I am asking this because I have a Total Column that I want as my last column after the last day for each month.

The Formula I used to populate the rows is:

=TRANSPOSE(ARRAY_CONSTRAIN(ArrayFormula(date($A$1,month(A2&1),1)+transpose(COLUMN($C$1:$AV$1)-3)),day(EOMONTH(date($A$1,month(A2&1),1),0)),1))

Here is a shared google sheet of my sample sheet: https://docs.google.com/spreadsheets/d/1m2vWg37PeMFTZj8QeWL0oIMBc4WT1FjFuapLTERMjGQ/edit?usp=sharing

Upvotes: 2

Views: 1499

Answers (3)

player0
player0

Reputation: 1

delete range B2:AJ3 and use in B2:

=INDEX({TEXT(SEQUENCE(1, DAY(EOMONTH(A2&"/"&A1, 0)), A2&"/"&A1, 1), 
 {"d"; "ddd"}), {"Total"; ""}})

enter image description here

Upvotes: 0

Kristkun
Kristkun

Reputation: 5953

You can use this to remove empty spaces in your dates:

=transpose({ARRAYFORMULA(DATE(A1,MONTH(A2&1),ROW(INDIRECT("A1:A" & DAY(EOMONTH(DATE(A1, MONTH(A2&1), 1), 0))))));"Total"})

It appends "Total" at the end of the range returned by the arrayformula(). Then you can combine this with @marikamitsos formula

Sample:

enter image description here

Upvotes: 0

marikamitsos
marikamitsos

Reputation: 10573

Just clear cells $B2:$AF2 and in cell C3 place

=ArrayFormula(IF($B2:$AF2="",,TEXT($B2:$AF2,"ddd")))

Functions used:

Upvotes: 1

Related Questions