Lower case date

[AmitojC]

Hi,
I would like the user to input the date, but in this format:
ddmmmyy

right now i have it through Data/Data validation and it works out, bu
it keeps the month first letter in upper case like:

01Mar13


I would like to have it like:

01mar13

Thanks

 

[CellShocked]

Hi,
I would like the user to input the date, but in this format:
ddmmmyy

right now i have it through Data/Data validation and it works out, but
it keeps the month first letter in upper case like:

01Mar13


I would like to have it like:

01mar13

Thanks!

12 IF mmm = "Jan" then d,"jan",yyyy
etc for each month

type swapout statements.

 

[CellShocked]

12 IF mmm = "Jan" then d,"jan",yyyy
etc for each month

type swapout statements.

Note, you will NOT generally be able to use your "assembled" date string
as a "valid" "date string" elsewhere in the workbook.



I used cell C4, but you can change as needed.

In cell c4 I had a date formatted cell and a date entered. In the case
of an 'error', it simply refers to the date in c2 anyway.

In C4, I put this formula:
=CONCATENATE(DAY(C2),C3,YEAR(C2))


In C3 I put this IF statement series...

=IF(MONTH(C2)=1,",jan,",IF(MONTH(C2)=2,",feb,",
IF(MONTH(C2)=3,",mar,",IF(MONTH(C2)=4,",apr,",
IF(MONTH(C2)=5,",may,",IF(MONTH(C2)=6,",jun,",
IF(MONTH(C2)=7,",jul,",IF(MONTH(C2)=8,",aug,",
IF(MONTH(C2)=9,",sep,",IF(MONTH(C2)=10,",oct,",
IF(MONTH(C2)=11,",nov,",IF(MONTH(C2)=12,",dec,",
C2))))))))))))


NOTE;
(You have to re-assemble the 73 character line lengths into one single
statement)

 

[CellShocked]

Note, you will NOT generally be able to use your "assembled" date string
as a "valid" "date string" elsewhere in the workbook.



I used cell C4, but you can change as needed.

C2 dangit... cell C2 !!!

In cell c4 I had a date formatted cell and a date entered. In the case
of an 'error', it simply refers to the date in c2 anyway.

C2 DANGIT!

In C4, I put this formula:
=CONCATENATE(DAY(C2),C3,YEAR(C2))


In C3 I put this IF statement series...

=IF(MONTH(C2)=1,",jan,",IF(MONTH(C2)=2,",feb,",
IF(MONTH(C2)=3,",mar,",IF(MONTH(C2)=4,",apr,",
IF(MONTH(C2)=5,",may,",IF(MONTH(C2)=6,",jun,",
IF(MONTH(C2)=7,",jul,",IF(MONTH(C2)=8,",aug,",
IF(MONTH(C2)=9,",sep,",IF(MONTH(C2)=10,",oct,",
IF(MONTH(C2)=11,",nov,",IF(MONTH(C2)=12,",dec,",
C2))))))))))))


NOTE;
(You have to re-assemble the 73 character line lengths into one single
statement)

sorry bout that

 

[CellShocked]

sorry bout that

http://www.mediafire.com/view/?m7514zxwz9kdbkm