CODEWITHSUNDEEP
exceldateexcel-formuladate-range
user3147365
user3147365

Reputation: 1

Insert in a new sheet all First January possible between to dates

I'm looking to insert in a new Sheet all First January between two dates: We suppose in my first Sheet I Have in A2:2024-01-01 and in B2:2027-01-01, what I'm looking is to have in Sheet 2 in column A all this values A1:2024-01-01 // A2:2025-01-01 // A3:2026-01-01 // A4:2027-01-01!! Thanks for all!

If I have Start Date 2023-10-20 and finish date 2027-12-31 I want to have in a column this values A1:2024-01-01 // A2:2025-01-01 // A3:2026-01-01 // A4:2027-01-01

Upvotes: 0

Views: 95

Answers (2)

Mayukh Bhattacharya
Mayukh Bhattacharya

Reputation: 27438

Try using the following formula:

enter image description here

• Formula used in cell D2

=LET(x, SEQUENCE(B2+1-A2,,A2), FILTER(x,TEXT(x,"m/d")="1/1"))

When Start Date --> 10/20/2023 & End Date --> 12/31/2027

enter image description here

For Non-MS365 Users or those using Older versions may use the following, the following formula has an advantage over volatile functions viz. OFFSET() or INDIRECT(), which means the

Volatile functions recalculates whenever the excel workbook is open or whenever a calculation is triggered in the worksheet. This adds to the processing time and slows down your workbook. And as suggested by experts to be avoided as far as possible!

enter image description here

=AGGREGATE(15,7,(A3+ROW($ZZ$1:INDEX($Z:$Z,$B$3+1-$A$3))-1)/
 (TEXT(A3+ROW($ZZ$1:INDEX($Z:$Z,$B$3+1-$A$3))-1,"m/d")="1/1"),{1;2;3;4})

Another option:

=LET(x, SEQUENCE(B3+1-A3,,A3), TOCOL(x/(TEXT(x,"d/m")="1/1"),2))

Upvotes: 0

Black cat
Black cat

Reputation: 6271

This formula works on non O365 versions also in Sheet2 cell A1 This counts the start date year's also .

=DATE(ROW(INDIRECT(YEAR(Sheet1!A2)&":"&YEAR(Sheet1!B2))),1,1)

If not whole start year not required use this formula:

=IF(AND(MONTH(Sheet1!A2)=1,DAY(Sheet1!A2)=1),DATE(ROW(INDIRECT(YEAR(Sheet1!A2)&":"&YEAR(Sheet1!B2))),1,1),DATE(ROW(INDIRECT(YEAR(Sheet1!A2)+1&":"&YEAR(Sheet1!B2))),1,1))

Upvotes: 1

Related Questions