• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 38
  • Last Modified:

Adapt Excel Date Code For Access

I use the following code in Excel to format today's date like this .......
11th April     3rd April     1st March    14th May       31st July

EXCEL CELL Contains this (No VBA Used)
=TEXT(TODAY(),"d") & IF(OR(MOD(TEXT(TODAY(),"d"),100)={11,12,13}),"th",CHOOSE(1+MIN(4,MOD(TEXT(TODAY(),"d"),10)),"th","st","nd","rd","th")) & " " & TEXT(TODAY(),"mmmm")

Open in new window

Can it be adapted to work in Access 2013 ?
0
Eirman
Asked:
Eirman
  • 3
  • 2
1 Solution
 
NorieVBA ExpertCommented:
Perhaps.
OrdinalDate:Format([DateField], "d") & Switch(Day([DateField]) <> 11 And Day([DateField]) Mod 10 = 1, "st", Day([DateField]) <> 12 And Day([DateField]) Mod 10 = 2, "nd", Day([DateField]) <> 13 And Day([DateField]) Mod 10 = 3, "rd", True, "th") & " " & Format([DateField], "mmmm")

Open in new window

0
 
EirmanChief Operations ManagerAuthor Commented:
@Norrie - I need the code in a text box
At the moment I have =Date()    Format = dddd - dd mmmm yyyy

It your code a VBA function?
0
 
NorieVBA ExpertCommented:
Try this.
=Format(Date(), "d") & Switch(Day(Date()) <> 11 And Day(Date()) Mod 10 = 1, "st", Day(Date()) <> 12 And Day(Date()) Mod 10 = 2, "nd", Day(Date()) <> 13 And Day(Date()) Mod 10 = 3, "rd", True, "th") & " " & Format(Date(), "mmmm")

Open in new window

0
 
EirmanChief Operations ManagerAuthor Commented:
That appears to be working fine Norie
I'll check it with a few different dates before closing the question.
0
 
EirmanChief Operations ManagerAuthor Commented:
I changed the computer clock a few times and everything is okay.
Thank You.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now