Try
=UPPER(MID(A4,5,3))&text(L12,"00")&TEXT(I4,"mmddyy ")&text(L15,"00")
Using the Now() in I4 is dangerous because it changes each time anything is
calculated and the historical invoice numbers will change everyday.
I would recommend either making sure you copy and paste special values on
the date column each day or enter the date using <control ;
"Murphey's Law" wrote:
Thanks Harald, I now have "Mr. Jones" proposal number like this: JON10531051.
Of course this new number reflects the date I have on my computer which is in
a cell =NOW().
TWO THINGS: First, I need a double digit for the "times I've done business
for Mr. Jones", (01, 02, 03, etc.), and second, a double digit for the number
of pages in this proposal. I tried the formula I got from BJ, but for what
ever reason, it didn't work 100%, but it got me in the right direction!
Here is my formula so far: =UPPER(MID(A4,5,3))&L12&TEXT(I4,"mmddyy")&L15.
I'm almost finished, and Boy is it exciting! You have my sincere appreciation!
"Harald Staff" wrote:
Nice. You can simplify the date part to
=TEXT(C3;"mmddyy")
and Pages to
=TEXT(C4;"00")
Best wishes Harald
"bj" skrev i melding
...
I assume you have other cells with the lookup information.for example
C1 Last name
C2 Num of jobs
C3 date
c4 pages
try
=Upper(left(C1,3)&text(c2,"00)&if(month(c3)<10,"0" ,"")&month(C3)&if(day(C3)<
10,"0","")&day(C3)&if(mod(year(c3),100)10,"0","") &mod(year(c3),100)&if(C4<1
0,"0","")&c4
"Murphey's Law" wrote:
|