In cell A1 is =Today() In cell A2 is =365-(DATE(YEAR(A1),12,31)-A1) This returns the number of days in the year so far.
I want to add the last two digits of the CURRENT year to this for example
If using 11/17/2010 as an example A1 displays 11/17/2010 A2 displays 321 I want A2 to display 32110 or if I do two batches that day then 32110A
everything I try gives me #VALUE?
Assume that your date's are in Column-A and the expected result is Column-B and the first row is having the column headers (Column-A : Date; Column - B : Result) like the below.
Column-A Date 17-Nov-10 5-Nov-10 16-Nov-10 17-Nov-10 16-Nov-10 15-Oct-10 10-Oct-10 15-Nov-10 15-Oct-10 17-Nov-10 16-Nov-10 17-Nov-12
Copy and paste the below formula in B2 cell =IF(COUNTIF(A:A,A2)=1,IF((YEAR(A2)/4)-INT(YEAR(A2)/4)=0,366,365)-(DATE(YEAR(A2),12,31)-A2)&TEXT(A2,"YY"),SUBSTITUTE(IF((YEAR(A2)/4)-INT(YEAR(A2)/4)=0,366,365)-(DATE(YEAR(A2),12,31)-A2)&TEXT(A2,"YY")&"-","000-","")&IF(ISNUMBER(CHAR(MAX(CODE(MID($B$1:B1,FIND("-",$B$1:B1)+1,255))))),CHAR(MAX(CODE(MID($B$1:B1,FIND("-",$B$1:B1)+1,255)))),IF(ISNUMBER(A2),CHAR(64+COUNTIF($A$1:A2,A2)),"")))
Drag the B2 cell formula to the remaining cells of B column based on the Column-A data.
For your easy reference I have created a sample file for you, Click or Copy and paste the below link in web browser to download the Example file.
Scroll the mouse button to the bottom of the website and click Download
The File Name is: Number & Text In Same Cell.xls
Hope it's Clear to you!