Forum Discussion
Solved
Formula adjustment
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.
- 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?
- 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?- That was it! Thank you so much for this great formula. It works really great, and I appreciate it very much. Thank you
