Formula adjustment


Hi Everyone,

My serial number which increases by 1 on every workday is not resetting. Here’s what I got:


in A3



in A4

=NETWORKDAYS.INTL(DATE(2022,6,23),A3,1,$A$7:$A$18) +22119


Last year I was helped with the above formula on this great website which performed well until Dec 31, 2022 but on Jan 1st, 2023 it should be reset which I did not realize back then. Here’s how it is supposed to work:

It excludes the weekend (Sat, Sun) and the holidays that defined in A7 to A18.

It then provides the yy001 on the first workday of the year by adding 1 until the end of the year, 250 approx. workdays. Today (4/10/2023) it should look like this 23067

Any help in this regard will be greatly appreciated and thank you in advance.

2 Replies
best response confirmed by A2Z CF (Contributor)
if I understand correctly that formula isn't going to do that (and apparently it isn't)
you need something like:
as this will change each day I assume you either just use this to copy and paste or maybe a macro uses it?
That was it! Thank you so much for this great formula. It works really great, and I appreciate it very much. Thank you