Format\Formula Help

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In my spreedsheet we have to record daily test results with an id numbered
assigned to them. To start the new year we would start with 20061. 2006
represents the year and 1 represents the first sample. I start the year with
this number and then in the following cells my formula just adds one. This
elminates us typing in the id number so the next number would be 20062 etc. I
did not take into account that once I reached 20069 my next ID number turned
to 20070. I need to keep the 2006 so after 20069, the next reading would be
200610. Is there a way to do this either through formating or a different
formula.

thanks in advance
scott
 
Scott,

Try this

=VALUE(2006&MOD(A1,20060)+1)

adjust for A1 to where your value is

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
How about making column A display "2006" all the way down, the make a column
B with your numbers (ie 1-1000), then make a column c with the formula a1&b1?
You can hide columns A and B and C will display your id.

hope that helps
 
scott said:
In my spreedsheet we have to record daily test results with an i
numbered
assigned to them. To start the new year we would start with 20061
2006
represents the year and 1 represents the first sample. I start the yea
with
this number and then in the following cells my formula just adds one
This
elminates us typing in the id number so the next number would be 2006
etc. I
did not take into account that once I reached 20069 my next ID numbe
turned
to 20070. I need to keep the 2006 so after 20069, the next readin
would be
200610. Is there a way to do this either through formating or
different
formula.

thanks in advance
scott
Click to expand...

You can try overwriting your codes with:

=YEAR(TODAY())&ROW()+1

which assumes you are starting in row 2. If not, adjust the last par
(Row()+1) appropriately to coincide with the row number you begin in
 
Thanks Bob that looks like it will work perfect. Do you see any problems the
larger the number gets. Last year we ran 1944 samples would anything happen
if say this gets to 1999.
thanks again Scott
One other question. I ahve atemplate for my ss. Is there a tweak to your
formula that would replace the 2006 with the current year, for future. When
2007 comes around I would have to reset 2006 to 2007 just wondering

thanks again scott
 
Scott,

I just ran it down to row 65536, no problems. There just aren't enough rows
in Excel (yet!) for it to become a problems.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
I entered the date as 04/01/06 into a cell. someone went in and did
something because now the date shows up as 38808. How can I fix this
 
Format / Cells / Date

tom said:
I entered the date as 04/01/06 into a cell. someone went in and did
something because now the date shows up as 38808. How can I fix this
Click to expand...
 
Back
Top