1

I want a column with date only, like this

DATE
11/05/2022
11/05/2022
11/05/2022
11/06/2022
11/06/2022
11/06/2022
11/07/2022
11/07/2022
11/07/2022

And list goes on. I just want to know how to automate it like dragging from corner or some trick to make it faster without doing the manual work.

1 Answer 1

1

You can use a formula like this:

=BYROW(A:A;LAMBDA(each;EDATE("11/4/2022";ROUNDUP(ROW(each)/3))))

You can change A:A to the amount of rows you need. IMPORTANT: do not use the same column than the one you want the series. If it's going to be in A1, just change =BYROW(B:B.... or else, it will create a circular dependency

And, if you start in row 3, for example, change it to B3:B, or something like that

Here, you can see it working enter image description here

EDIT: another option, a little more direct and that doesn't depend on another column:

=ARRAYFORMULA (EDATE("11/4/2022";ROUNDUP(SEQUENCE(1000)/3)))

You can change that 1000 with any amount you want

EDIT 2: If it's needed to update by one day per three rows then:

=BYROW(A:A,LAMBDA(each, Date(2022,11,4)+ROUNDUP(row(each)/3)))

3
  • day needs to be increased by one, not the month. Commented Nov 7, 2022 at 5:40
  • Ohhh, it was worth clarifying then 😅 Try: =BYROW(A:A,LAMBDA(each, Date(2022,11,4)+ROUNDUP(row(each)/3)))
    – Martín
    Commented Nov 7, 2022 at 8:51
  • Hello! I've seen my answer didn't fit you. You can change A:A with the rows you need. You can change it to A3:A if it starts in row 3, or limit until a certain row: A1:A100. Let me know either way what's that you need
    – Martín
    Commented Nov 7, 2022 at 10:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.