# 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  Register To Reply

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  Register To Reply

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  Register To Reply

#### Thread Information

##### Users Browsing this Thread

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