Thread: Keeping 0 in Day

Please advise on what to add to the below string to retain the 0 in the dd field:

=CONCATENATE(AA2,"GT-H,T2," ",(DAY(AC2&UPPER(TEXT(AC2,"mmm"))&YEAR(AC2)),".pdf"

Where AC2 is the date (eg. 01JUN2010). The concatenated cells will not retain the 0 in the dd value of AC2.

James

I need the date to return with the zero, if the day contains one. So, I need all days to be double digit. And, I need the month to be capitalized (Jun = JUN). When I replace AC2 with TEXT(AC2,"00") I get numbers in the value, for example I get 40330 from 1JUN2010.

Originally Posted by ansardoha

Go to format cell & select customs and type dd/mm/yyyy or mm/dd/yyyy

That doesn't work in the target cell.

Originally Posted by Ron Coderre

If you want cell AC2 to display as ddmmmyyyy, that's easy enough using that as a custom number format. However, you won't get AC2 to display as upper case.

Be aware, though, that changing the format of a referenced cell will have no impact on cells that refer to that cell. For instance, if AC2 contains: =TODAY() formatted as ddmmmyyyy...it will display as: 17Jul2011
But, this formula: =LEFT(AC2,2) will display 40...not 17, because the underlying value of the cell is the date serial number of 40741.


If you want your formula to display the date section in upper case,
just change that section to this:
UPPER(TEXT(AC2,"ddmmmyyyy"))

So the entire formula would be something like this:
=CONCATENATE(AA2,"GT-H",T2," ",UPPER(TEXT(AC2,"ddmmmyyyy")),".pdf")

or...since CONCATENATE is too much typing for me:
=AA2&"GT-H"&T2&" "&UPPER(TEXT(AC2,"ddmmmyyyy"))&".pdf"

Does that help?

I tried that before and it didn't work. The UPPER formula only applies to letters so when you try to apply it to a string that includes numbers an error occurs.

I stand corrected, that appears to have worked. Thank you!!