Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Date Format
I would like Excel and Access to display dates in the following format:
Wednesday 3rd August 2005 Currently, I can only get a display of : Wednesday 3 August 2005 using dddd d mmmm yyyy Grateful for help getting 3rd instead of 3 displayed. Many thanks Stuart |
#2
|
|||
|
|||
Hi
There is no such format in Excel. And you will have to write a custom function that gives you the date in the format you want. Here is a solution but this solution will work only for your current requirement. Right click the cell where u have ur date select format and enter this as a custom format. d"rd" mmmm yyyy But this will not work for say for 2nd or 4th and you will have to change the custom format. Thanks Karthik Bhat Bangalore |
#3
|
|||
|
|||
Maybe you could use a helper cell with a formula.
Chip Pearson has one at: http://www.cpearson.com/excel/ordinal.htm If A1 contains the date: =TEXT(A1,"DDDD ") & DAY(A1) &IF(AND(MOD(DAY(A1),100)=10,MOD(DAY(A1),100)<=14) ,"th", CHOOSE(MOD(DAY(A1),10)+1,"th","st","nd","rd","th", "th","th","th","th","th")) & TEXT(A1," MMMM YYYY") All one cell. StuartTheBrit wrote: I would like Excel and Access to display dates in the following format: Wednesday 3rd August 2005 Currently, I can only get a display of : Wednesday 3 August 2005 using dddd d mmmm yyyy Grateful for help getting 3rd instead of 3 displayed. Many thanks Stuart -- Dave Peterson |
#4
|
|||
|
|||
Thanks Dave, this works great for excel.
Any ideas how I'd get Access to accept this formula? Thanks Stuart "Dave Peterson" wrote: Maybe you could use a helper cell with a formula. Chip Pearson has one at: http://www.cpearson.com/excel/ordinal.htm If A1 contains the date: =TEXT(A1,"DDDD ") & DAY(A1) &IF(AND(MOD(DAY(A1),100)=10,MOD(DAY(A1),100)<=14) ,"th", CHOOSE(MOD(DAY(A1),10)+1,"th","st","nd","rd","th", "th","th","th","th","th")) & TEXT(A1," MMMM YYYY") All one cell. StuartTheBrit wrote: I would like Excel and Access to display dates in the following format: Wednesday 3rd August 2005 Currently, I can only get a display of : Wednesday 3 August 2005 using dddd d mmmm yyyy Grateful for help getting 3rd instead of 3 displayed. Many thanks Stuart -- Dave Peterson |
#5
|
|||
|
|||
No idea at all.
You may want to post in one of the Access newsgroups. StuartTheBrit wrote: Thanks Dave, this works great for excel. Any ideas how I'd get Access to accept this formula? Thanks Stuart "Dave Peterson" wrote: Maybe you could use a helper cell with a formula. Chip Pearson has one at: http://www.cpearson.com/excel/ordinal.htm If A1 contains the date: =TEXT(A1,"DDDD ") & DAY(A1) &IF(AND(MOD(DAY(A1),100)=10,MOD(DAY(A1),100)<=14) ,"th", CHOOSE(MOD(DAY(A1),10)+1,"th","st","nd","rd","th", "th","th","th","th","th")) & TEXT(A1," MMMM YYYY") All one cell. StuartTheBrit wrote: I would like Excel and Access to display dates in the following format: Wednesday 3rd August 2005 Currently, I can only get a display of : Wednesday 3 August 2005 using dddd d mmmm yyyy Grateful for help getting 3rd instead of 3 displayed. Many thanks Stuart -- Dave Peterson -- Dave Peterson |
#6
|
|||
|
|||
Will do. Thanks again. Stuart "Dave Peterson" wrote: No idea at all. You may want to post in one of the Access newsgroups. StuartTheBrit wrote: Thanks Dave, this works great for excel. Any ideas how I'd get Access to accept this formula? Thanks Stuart "Dave Peterson" wrote: Maybe you could use a helper cell with a formula. Chip Pearson has one at: http://www.cpearson.com/excel/ordinal.htm If A1 contains the date: =TEXT(A1,"DDDD ") & DAY(A1) &IF(AND(MOD(DAY(A1),100)=10,MOD(DAY(A1),100)<=14) ,"th", CHOOSE(MOD(DAY(A1),10)+1,"th","st","nd","rd","th", "th","th","th","th","th")) & TEXT(A1," MMMM YYYY") All one cell. StuartTheBrit wrote: I would like Excel and Access to display dates in the following format: Wednesday 3rd August 2005 Currently, I can only get a display of : Wednesday 3 August 2005 using dddd d mmmm yyyy Grateful for help getting 3rd instead of 3 displayed. Many thanks Stuart -- Dave Peterson -- Dave Peterson |
#7
|
|||
|
|||
I'm trying to modify the code you helped locate to show only the ordinal
number from the date, not the month, year, day of week, etc. Any ideas how to incorporate this in? "Dave Peterson" wrote: Maybe you could use a helper cell with a formula. Chip Pearson has one at: http://www.cpearson.com/excel/ordinal.htm If A1 contains the date: =TEXT(A1,"DDDD ") & DAY(A1) &IF(AND(MOD(DAY(A1),100)=10,MOD(DAY(A1),100)<=14) ,"th", CHOOSE(MOD(DAY(A1),10)+1,"th","st","nd","rd","th", "th","th","th","th","th")) & TEXT(A1," MMMM YYYY") All one cell. StuartTheBrit wrote: I would like Excel and Access to display dates in the following format: Wednesday 3rd August 2005 Currently, I can only get a display of : Wednesday 3 August 2005 using dddd d mmmm yyyy Grateful for help getting 3rd instead of 3 displayed. Many thanks Stuart -- Dave Peterson |
#8
|
|||
|
|||
nevermind. I got it playing around deleting different parts and this worked:
=TEXT(D37,"") & DAY(D37) &IF(AND(MOD(DAY(D37),100)=10,MOD(DAY(D37),100)<=1 4),"th", CHOOSE(MOD(DAY(D37),10)+1,"th","st","nd","rd","th" ,"th","th","th","th","th")) "adodson" wrote: I'm trying to modify the code you helped locate to show only the ordinal number from the date, not the month, year, day of week, etc. Any ideas how to incorporate this in? "Dave Peterson" wrote: Maybe you could use a helper cell with a formula. Chip Pearson has one at: http://www.cpearson.com/excel/ordinal.htm If A1 contains the date: =TEXT(A1,"DDDD ") & DAY(A1) &IF(AND(MOD(DAY(A1),100)=10,MOD(DAY(A1),100)<=14) ,"th", CHOOSE(MOD(DAY(A1),10)+1,"th","st","nd","rd","th", "th","th","th","th","th")) & TEXT(A1," MMMM YYYY") All one cell. StuartTheBrit wrote: I would like Excel and Access to display dates in the following format: Wednesday 3rd August 2005 Currently, I can only get a display of : Wednesday 3 August 2005 using dddd d mmmm yyyy Grateful for help getting 3rd instead of 3 displayed. Many thanks Stuart -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compare dates (one cell not in date format) | Excel Discussion (Misc queries) | |||
How do I keep the date from changing format in a mail merge? | Excel Discussion (Misc queries) | |||
Date format | Excel Discussion (Misc queries) | |||
USING THE DATE FORMAT IN EXCEL | Excel Discussion (Misc queries) | |||
Date format collapses diagram | Excel Discussion (Misc queries) |