Microsoft Excel 2007

Hi My name is Tyrone I would like to enter a date then use only the day and month to start the code in a new cell , i also want a function that will automatically generate a value to be part of that code...e.g if i enter 25/01/2010 then the code in the new cell should become 2501-01 ...if i enter 25/01/2010 later that day the code should become 2501-02 and so forth...PLEASE HELP!!!!

Hi, Here is one way to do this.

In this example, dates are entered in column A, starting at cell A2

(There must be one row above the first date entry that does not contain a date, for this to work)I have split the creation of the 'Date-Serial' number into two columns, to make it a bit easier to follow, but you could combine it into one formula in one column.

In cell B2 enter this formula:

=TEXT(DAY(A2),"00")&TEXT(MONTH(A2),"00")&"-"

This creates the base of your date-Serial number e.g., 2501-

The TEXT() functions ensure that the day and month always display as two characters, so that the base is always the same length.In cell C2 enter this formula:

=IF(A2<>A1,B2&"01",B2&TEXT(VALUE(RIGHT(C1,2)+1),"00"))This looks to see if the date in the row above is the same as the one entered on this row. If it is different it is the first one for this day so -01

If it is the same date it takes the last two characters which are the serial number, converts them to a value, adds 1 and then converts it back to text, always as two characters.If you were likely to go over 99 entries in a day, you would have to format the serial part as "000" and test the right three characters instead of the two ...(RIGHT(C1,3)...

Drag the formulas in B2 and C2 down as required.

If you want this in place

beforeyou enter dates, the above formula returns 0001-01 when column A is empty.

To avoid this, wrap the formulas in a test for an empty cell in column A and return "" instead:

=IF(A2="","",TEXT(DAY(A2),"00")&TEXT(MONTH(A2),"00")&"-")

=IF(A2="","",IF(A2<>A1,B2&"01",B2&TEXT(VALUE(RIGHT(C1,2)+1),"00")))Here is my example:

A B C 1 Date Base Day-Serial 2 22-Jan-10 2201- 2201-01 3 22-Jan-10 2201- 2201-02 4 22-Jan-10 2201- 2201-03 5 22-Jan-10 2201- 2201-04 6 22-Jan-10 2201- 2201-05 7 23-Jan-10 2301- 2301-01 8 23-Jan-10 2301- 2301-02 9 23-Jan-10 2301- 2301-03 10 25-Jan-10 2501- 2501-01Regards

Here's another possible solution. With your first date in A1, place this in B1 and drag it down.

As with Humar's suggestion, you would wrap this in an IF statement to prevent 0001-01 when Ax is empty.

(This is one formula that should be on one line)

=TEXT(DAY(A1),"00")&TEXT(MONTH(A1),"00")&" - "&TEXT(COUNTIF(INDIRECT("A1:A"&ROW()),A1),"00")

The INDIRECT("A1:A"&ROW()) builds the range for the COUNTIF function by increasing the range by one row at a time.

Row 1 -> =COUNTIF(A1:A1,A1)

Row 2 -> =COUNTIF(A1:A2,A2)

Row 3 -> =COUNTIF(A1:A3,A3)

Hi, Here is another modification

In cell B2 enter:

=TEXT(DAY(A2),"00")&TEXT(MONTH(A2),"00")&"-"&TEXT(COUNTIF(A1:$A$1,A2)+1,"00")This avoids the need for the INDIRECT function.

Drag down as required and wrap in IF as before.

Regards

you guys rock this completely sorted out my problem...THANK YOU SOOOO MUCH...and i was told by a "genius" this cannot be done...woo hoo

Hi Humar your formula worked for what i asked..but the problem that arises is that when a certain project is logged for more than on day ,the "project number" or code changes as per the day , which it obviously would do, so now i need to create a means to secure that code for by using the customers order number...only when a new order number is entered will that particular code change...can you please help again

Hi, This one is a bit more difficult !

I have had a couple of goes at it, but not right yet.

Regards

Hi, Have a look at this. It isn't quite right as it skips numbers if there are two project codes the same on the same day.

It requires an extra column, but the column can be moved out of the way later - it is a combined date and order number (using Excel's date numbers)

A B C D 1 Date O/N Date-ON Day-Serial 2 22-Jan-10 47 4020047 2201-01 3 22-Jan-10 47 4020047 2201-01 4 22-Jan-10 50 4020050 2201-03 5 22-Jan-10 51 4020051 2201-04 6 22-Jan-10 47 4020047 2201-01 7 23-Jan-10 47 4020147 2301-01 8 23-Jan-10 48 4020148 2301-02 9 23-Jan-10 49 4020149 2301-03 10 25-Jan-10 53 4020353 2501-01The formula in C2 is:

=A2&B2

and in D2 is:

=TEXT(DAY(A2),"00")&TEXT(MONTH(A2),"00")&"-"&

IF(COUNTIF($C$1:C1,C2)>0,RIGHT(VLOOKUP(C2,$C$1:D1,2,FALSE),2),

IF(COUNTIF($C$1:C1,C2)>0,TEXT(VLOOKUP(C2,$C$1:D1,2,FALSE)+1,"00"),

TEXT(COUNTIF($A$1:A2,A2),"00")))This is all one line - I just split it up for ease of viewing.

Regards

Ask Your Question

Weekly Poll

Have you played Jackbox during the pandemic?

Discuss in The Lounge

Poll History