Finding first and last workdays of a given year

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

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

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

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

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