Excel Formula Help

[NeoValence]

Hi All,

How would a function/formula look that would create/verify a serial number that looks like this: AYYMMXXXX where A is constant, YY is the current 2 digit year, MM is the current month, and XXXX is a whole number that gets incremented with each additional product?

Thanks,

Neo

 

[straybullet]

="A"&TEXT(NOW(),"yy")&TEXT(NOW(),"mm")&TEXT(A1+1,"0000") would get what you're looking for. In the above, A1=5 and the result is A08110006

Not sure about the verification though - I'd think you'd need to use some VBA for that.

Let them hate - so long as they fear... Lucius Accius

 

[NeoValence]

straybullet,

I tried the fuction you suggested and had very little luck getting it to work. Do you suppose this could be due to my being on Office 2007?

Thanks Anyway,

Neo

 

[SkipVought]

Do you suppose this could be due to my being on Office 2007?"

It will work in 2007.

How did you implement this formula (not a function). Please explain in detail.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[straybullet]

Well, once again, taking a shortcut doesn't pay off... Sorry, my goof!

Incrementing from 5 to A08110006 works fine with the above but the following allows it to increment from A08110005 to A08110006. Also, combining the yymm portion shortens it a bit.

="A"&TEXT(NOW(),"yymm")&TEXT(VALUE(RIGHT(A1,4))+1,"0000")



Let them hate - so long as they fear... Lucius Accius