Formula adjustment

[A2Z CF]

Hi Everyone,

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

in A3

=TODAY()

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 · Accepted solution]

if I understand correctly that formula isn't going to do that (and apparently it isn't)
you need something like:
=(year(A3)-2000)*1000+NETWORKDAYS.INTL(DATE(year(A3),1,1),A3,1,$A$7:$A$18)
as this will change each day I assume you either just use this to copy and paste or maybe a macro uses it?

[#3]

That was it! Thank you so much for this great formula. It works really great, and I appreciate it very much. Thank you