Reading a date from another sheet and changing the format

**cafc_fuller** · 11-13-2012, 06:16 AM

Good Morning,

On my "Factory" sheet in cell B7 i enter the date in the format 13.11.12
On the next sheet "Customer" I have a date box G17.

I need a formula to put into G17 which will read the date automatically from the Factory sheet and change to 13th November 2012.
It must include the "th" or "st" depending on the day.

Is this possible?

Thank you
Darren

**Pete_UK** · 11-13-2012, 06:58 AM

Is that a proper date format (i.e. with the full-stops), or is that a text value in cell B7 ?

Pete

**cafc_fuller** · 11-13-2012, 07:04 AM

I do enter it as text, I'm guessing this will be the problem?

**benishiryo** · 11-13-2012, 07:18 AM

hi cafc_fuller. the formula would be much simpler if they are in proper dates instead of text. you can work with this though:

Please Login or Register  to view this content.

**cafc_fuller** · 11-13-2012, 07:27 AM

This is perfect, thank you so much. You guys are great.
This is not vital but is their anyway to remove the #VALUE! (I know it appears when the date is not entered), but just looks ugly when its on the sheet?

Or is this impossible?

Thanks
Darren

**Pete_UK** · 11-13-2012, 07:28 AM

Here's another way - put this in G17:

=DAY(--SUBSTITUTE(Factory!B7,".","/"))&IF(AND(MOD(ABS(DAY(--SUBSTITUTE(Factory!B7,".","/"))),100)>=10,MOD(ABS(DAY(--SUBSTITUTE(Factory!B7,".","/"))),100)<=14),"th",CHOOSE(MOD(ABS(DAY(--SUBSTITUTE(Factory!B7,".","/"))),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))&TEXT(--SUBSTITUTE(Factory!B7,".","/")," mmmm yyyy")

Hope this helps.

Pete

**Pete_UK** · 11-13-2012, 07:32 AM

If you want a blank if Factory!B7 is blank, you can do this with mine:

=IF(Factory!B7="","",DAY(--SUBSTITUTE(Factory!B7,".","/"))&IF(AND(MOD(ABS(DAY(--SUBSTITUTE(Factory!B7,".","/"))),100)>=10,MOD(ABS(DAY(--SUBSTITUTE(Factory!B7,".","/"))),100)<=14),"th",CHOOSE(MOD(ABS(DAY(--SUBSTITUTE(Factory!B7,".","/"))),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))&TEXT(--SUBSTITUTE(Factory!B7,".","/")," mmmm yyyy"))

Hope this helps.

Pete