CODEWITHSUNDEEP
excelgoogle-sheetsexcel-formula
Raja
Raja

Reputation: 442

Google Excel Date to Span two columns

2023                    
Jul                 
Tasks   Sat Sun Mon Tue Wed
ABC     01  02  03  04  05
                    
Reading                 
Music                   
Drawing

The above excel generates the monthly dates. Now for each of tasks in column 1 I need to have 2 columns under date as two people need to update the status. (ABC, DEF) types.

So each date has to span across two columns. The formula I am using for date generation is:

=LET(dt,DATE(A1,MONTH(A2&1),1),IF(LEN(A1),SEQUENCE(1,EOMONTH(dt,0)-dt+1,dt),))

Upvotes: 0

Views: 50

Answers (1)

Martín
Martín

Reputation: 10117

There are many ways of thinking it. One is to create a sequence of twice the amount of days, but adding "half a day" each time. Since you'll then format the cell as needed, the 12 hs will be despicable:

=LET(dt,DATE(A1,MONTH(A2&1),1),IF(LEN(A1),SEQUENCE(1,2*(EOMONTH(dt,0)-dt+1),dt,0.5),))

Other is to duplicate and get the array as a row:

=LET(dt,DATE(A1,MONTH(A2&1),1),
days,SEQUENCE(1,EOMONTH(dt,0)-dt+1,dt),
IF(LEN(A1),TOROW({days;days},,1),))

You could duplicate and sort, and other ways too!

Upvotes: 2

Related Questions