Stop a Formula from Recalculating the Next Day It's Opened

Hi All,

Spliced together a formula that creates a unique, 17-digit serial number that includes a four-digit julian date and checks to see if the last number used is the same date: 1) If so, generate the next serial number (0002 if the line before was 0001) or 2) If the dates aren't the same, use 0001.

IF(ISBLANK(B3),"","N39040"&RIGHT(TEXT(TODAY(),"y"))&TEXT((TODAY()-DATEVALUE("1/1/"&TEXT(TODAY(),"y"))+1),"000")&IF(MID(A2,7,4)=RIGHT(TEXT(TODAY(),"yyyy"))&TEXT((TODAY()-DATEVALUE("1/1/"&TEXT(TODAY(),"y"))+1),"000"),"X"&TEXT((MID(A2,12,3)+1),"000"),"X001")&"XXX")

Using today's date and my first line for today it would produce: N390403296X001XXX

N39040 = Static text for all lines.
3296 = 3 (Last digit of this year) and 296 (Julian Date)
X = Static Text for all lines.
001 = First line of the day.
XXX = Static text for all lines.

Formula works as designed; however, when I open the file tomorrow, the julian date will change to 3297. When I open the file on a "new" day, how can I keep the julian dates on the "old" lines from changing? How do I keep the unique serial number once it's assigned?

Paul

I would designate 1 cell anywhere in your book to hold the desired date..
Say Sheet1!Z1 (or whatever)
Manually enter today's date in that cell.

Replace ALL instances of TODAY() in your formula with Sheet1!$Z$1

Now it will only update when you manually enter a new date in that designated cell.

my suggestion to you would be
you need to save unique serial to Copy - paste special as value, so as effect of formula will no more recalculate..
hope this helps


BhaVesH

my suggestion to you would be
you need to save unique serial to Copy - paste special as value, so as effect of formula will no more recalculate..
hope this helps


BhaVesH

Thanks to those that replied; however, I took the date out of the equation. Made the user provide the current date and used their input in the formula.