# Date Function

1. ## Date Function

ok so my co-worker has a spreadsheet that someone made for her with a date function in a format she needs.

=IF((((VALUE(TEXT(Worksheet!D3,"dd"))/10))-(FIXED(VALUE(TEXT(Worksheet!D3,"dd"))/10,0)))*10=1,+(TEXT(Worksheet!D3,"d")&"st day of "&(+TEXT(+Worksheet!D3,"mmmm"))&", "&TEXT(+Worksheet!D3,"yyyy")),IF((((VALUE(TEXT(Worksheet!D3,"dd"))/10))-(FIXED(VALUE(TEXT(Worksheet!D3,"dd"))/10,0)))*10=2,+(TEXT(Worksheet!D3,"d")&"nd day of "&(+TEXT(+Worksheet!D3,"mmmm"))&", "&TEXT(+Worksheet!D3,"yyyy")),IF((((VALUE(TEXT(Worksheet!D3,"dd"))/10))-(FIXED(VALUE(TEXT(Worksheet!D3,"dd"))/10,0)))*10=3,+(TEXT(Worksheet!D3,"d")&"rd day of "&(+TEXT(+Worksheet!D3,"mmmm"))&", "&TEXT(+Worksheet!D3,"yyyy")),+(TEXT(Worksheet!D3,"d")&"th day of "&(+TEXT(+Worksheet!D3,"mmmm"))&", "&TEXT(+Worksheet!D3,"yyyy")))))

it works but there is a bug in it and the guy that did it for her can't figure it out and neither can I. On the 11th of each month the date in the cell will show up as "11st day of April, 2007". Every other date works fine. Its just wrong for the 11th. Any help would be appreciated whether it be fixing this function or a completely easier one. Thanks

2. Would this be easier

=DAY(D3)&IF(OR(DAY(D3)={1,2,3,21,22,23,31}),CHOOSE(1*RIGHT(DAY(D3),1),"st","nd ","rd "),"th")&" day "&TEXT(D3,"mmmm, yyyy")

VBA Noob

3. Originally Posted by VBA Noob
What this be easier

=DAY(D3)&IF(OR(DAY(D3)={1,2,3,21,22,23,31}),CHOOSE(1*RIGHT(DAY(D3),1),"st","nd ","rd "),"th")&" day "&TEXT(D3,"mmmm, yyyy")

VBA Noob
dude...you are awesome. Thanks =P

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1