Excel formulas for Dates

Copper Contributor

I wanted to know if there was a function or formula I could use for the following:

In a row, I have 3 of the same dates following consecutively down a row.

11-1-2023 x3

11-2-2023 x3

and so on for the whole month, Including weekends. Is there a way my spreadsheet can follow that pattern without having to type throughout or drag to the next 2 rows? 

5 Replies

@LMIDDLEBROOK 

="11-"&ROW(A1)&"-2023 x3"

 

Does this return the intended result? However the results are recognized as texts because of the x3 in the end.

date with x3.png

Thank You I was saying I needed the dates 3x .
11-1-2023
11-1-2023
11-1-2023
and so on for the whole month

@LMIDDLEBROOK 

=INDEX($A$1:$A$6,QUOTIENT(ROW(A3),3))

 

You are welcome. Unfortunately i didn't understand the question when i first replied. I'd reate a reference table as shown in range A1:A6 in the example and use the INDEX formula. The format of the dates is different in my sheet because of the regional date settings in german Excel.

dates.png

 

 

The second formula you provided didn't work for me. The first one did.
Do you know how I can duplicate this template but keep the formulas, but change the information I enter?

@LMIDDLEBROOK 

Try this Lambda:

 

'ExtendDates
=LAMBDA(start,days,LET(
    seq, SEQUENCE(days, , start),
    resize, SEQUENCE(, 3, 1, 0),
    TOCOL(MMULT(seq, resize))
))

 

All you have to do is provide the start date and number of days. Each day will show 3x:

Patrick2788_0-1699468866229.png