Finding first and last workdays of a given year

[Excel_Arate]

Hi,

From a long column B with all the workdays since 1973, I wish to extract the first and last workday of a given year.
I have tried various index formulas with max. and min. but so far it hasn't worked out.

For the first day of a year that is entered in cell V4, I take the 31.12. of the previous year (in V3) and increase the index row by 1:
=INDEX($B$3:$B$50000;MATCH("31.12."&V3;$B$3:$B$50000;1)+1;1)

For the last day of that year in cell V4, I take the 31.12.:
=INDEX($B$3:$B$50000;MATCH("31.12."&V4;$B$3:$B$50000;1);1)

For some reason the formluas always return the first day of any year as being the 16.11.2023 and the last day the 15.11.2023. Perhaps the formula is wrong altogether and a max(if...) style of formula would help. Perhaps there is also a problem with the formatting of the dates. As far as I can tell, however, the entire colum B is in date format. But for smoe reason something has changed after 15.11.2023.

Can some explain what is going on and perhaps correct my formulas?

Best regards,
Excel_Arate

[Special-K]

Does this work?

Array formulas
in D3
=MIN(IF(YEAR(B$3:B$50000)=V3,B$3:B$50000))
in E3
=MAX(IF(YEAR(B$3:B$50000)=V3,B$3:B$50000))

and copy down

[Excel_Arate]

Hi,

Yeah, I was hoping the solution might be like the one you suggested, However, I'm afraid it doesn't work. I have attached the result.
What's strange is that I get zeros (in date format "00.01.1900") for almost all entries except for 2023 and 2024 and here 3 out of the 4 answers are correct and the fourth one is wrong. Maybe, it's the formatting after all? Can't detect anything though.


Thank you for trying to help, though.

Best regards,
Excel_Arate

[JeteMc]

Please try the following.
For 1st working day: =WORKDAY.INTL(DATE(V3,1,1)-1,1,1)
For last working day: =WORKDAY.INTL(DATE(V3,12,31)+1,-1,1)
Let us know if you have any questions.

[Excel_Arate]

Hi JeteMc,

Thanks a lot, this works better than anything I had so far! Just one question on the workdays. To my understanding, weekends defined as Saturdays and Sundays are taken out using these settings. If I wanted to take out New Years Day I could do that too but would have to deifne on wich weekday it occurred for the last 50 or so years, right? If so, is there another way of doing this?

Many thanks and best regards,
Excel_Arate

[JeteMc]

For first working day of the year try: =WORKDAY.INTL(DATE(V3,1,2)-1,1,1,DATE(V3,1,1)) or =WORKDAY.INTL(DATE(V3,1,2)-1,1,1)
Let us know if you have any questions.

[FlameRetired]

Hi JeteMc,

Thanks a lot, this works better than anything I had so far! Just one question on the workdays. To my understanding, weekends defined as Saturdays and Sundays are taken out using these settings. If I wanted to take out New Years Day I could do that too but would have to deifne on wich weekday it occurred for the last 50 or so years, right? If so, is there another way of doing this?

Many thanks and best regards,
Excel_Arate

You can create custom weekends and create a list of Holidays. Please see the attached.

Column C returns years. 1973 goes in C2 and then just add 1 to each row below until 2024.
Then I put USA holidays in F2:I2. In F2

Please Login or Register  to view this content.

in G2

Please Login or Register  to view this content.

in H2

Please Login or Register  to view this content.

and in I2 (this is a little trickier as it is USA specific for Thanksgiving Day...the 4th Thursday in November)

Please Login or Register  to view this content.

all copied down.

That should give you an idea ... then substitute your holidays.

From there on I used the first of each year as and start date to return the 1st and last workdays of each given year. In D2

Please Login or Register  to view this content.

and in E2

Please Login or Register  to view this content.

[Excel_Arate]

Hi JeteMc, apologies for the late reply. Both versions work well. Thank you very much!
Best regards,
Excel_Arate

[Excel_Arate]

Thank you, FlameRetired. This works fine and well explained! Sorry, for the late reply.
Best regards,
Excel_Arate

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'.
Note: As per the formulas in post #6, I suggest using the second formula.
I hope that you have a blessed day.

[FlameRetired]

You are welcome, and again thank you for the feedback and marking your thread Solved.