Date formula, but need to add the day to this - can anyone help?

**JSLampard** · 06-21-2012, 11:06 AM

Hi - I have the following formula:

=(IF(INT(DAY(C9)/10)=1,DAY(C9)&"th ",IF(MOD((DAY(C9)),10)=1,DAY(C9)& "st ",IF(MOD((DAY(C9)),10)=2,DAY(C9)&"nd ",IF(MOD((DAY(C9)),10)=3,DAY(C9)&"rd ",DAY(C9)&"th ")))))&TEXT(C9,"mmmm ")&YEAR(C9)

to change date in my spreadsheet to the format, say '2nd June 2012' - however, I would like to add the day to this too, so it would read 'Saturday 2nd June 2012'...

Can anyone help??

Many thanks & regards

Jim

**galvinpaddy** · 06-21-2012, 11:18 AM

Format the cell to custom - dd-dddd-mmm-yy

**galvinpaddy** · 06-21-2012, 11:19 AM

if you are unsure on how, right click the cell ->format->Custom-> and type -> dd-dddd-mmm-mm

**martindwilson** · 06-21-2012, 11:19 AM

just add=text(c9,"dddd ")& in front
or you could use the slightly shorter
=TEXT(C9,"dddd d")&IF(AND(DAY(C9)>=10,DAY(C9)<=14),"th",CHOOSE(MOD(DAY(C9),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))&TEXT(C9," mmmm yyyy")