Home 
Search 
Today's Posts 
#1




date format in excel 2002 on win 7
on this forum a helpful expert posted
With a value in A1: =A1&IF(AND(A110,A1<20),"th",CHOOSE(MOD(A1,10)+1," th","st","nd","rd","th","th","th","th","th","th" )) with my version i get 10 th not 10th same with 20. so i changed the formula 10 and 20 to 9 and 19. worked for 10th but not 20 and 30. what do i do? and can i change a date say january 20 2006 and using this formula to get January 20th 2006, all in one cell? at the moment i am using a separate column for, month,day and year. i need to copy paste them into a text file. thanks all Last edited by anon125 : May 4th 20 at 02:04 AM 
#2




date format in excel 2002 on win 7
on this forum a helpful expert posted
With a value in A1: =A1&IF(AND(A110,A1<20),"th",CHOOSE(MOD(A1,10)+1," th","st","nd","rd","th","th","th","th","th","th" )) with my version i get 10 th not 10th same with 20. so i changed the formula 10 and 20 to 9 and 19. worked but 30th is still 30 th! what do i do? and can i change a date say january 20 2006 and using this formula to get January 20th 2006, all in one cell? i need to copy paste them into a text file. thanks all Assuming A1 contains only the day value... =A1&IF(AND(RIGHT(A1,1)="1",RIGHT(A1,2)<"11"),"st" ,IF(AND(RIGHT(A1,1)="3",OR(A1<10,A120)),"rd",IF(A ND(RIGHT(A1,1)="2",OR(A1<3,A120)),"nd",IF(AND(A1 9,A1<21),"th","th"))))  Garry Free usenet access at http://www.eternalseptember.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion 
#3




date format in excel 2002 on win 7
Hi
Am Sun, 3 May 2020 23:34:41 +0100 schrieb anon125: on this forum a helpful expert posted With a value in A1: =A1&IF(AND(A110,A1<20),"th",CHOOSE(MOD(A1,10)+1," th","st","nd","rd","th","th","th","th","th","th" )) with my version i get 10 th not 10th same with 20. so i changed the formula 10 and 20 to 9 and 19. worked but 30th is still 30 th! a little bit shorter: =A1&IF(AND(RIGHT(A1,1)="1",A1<11),"st",IF(AND(RIG HT(A1,1)="2",A1<12),"nd",IF(AND(RIGHT(A1,1)="3",A 1<13),"rd","th"))) Regards Claus B.  Windows10 Office 2016 
#4




Garry, thanks but i got 12nd and what is the a120 for?
thanks 
#5




Thanks Claus B
I removed the space between A and 1 near the end of the formula and the spelling of right. get 31th etc errors thanks Last edited by anon125 : May 4th 20 at 04:37 PM 
#6




date format in excel 2002 on win 7
Hi
Am Sun, 3 May 2020 23:34:41 +0100 schrieb anon125: on this forum a helpful expert posted With a value in A1: =A1&IF(AND(A110,A1<20),"th",CHOOSE(MOD(A1,10)+1," th","st","nd","rd","th","th","th","th","th","th" )) with my version i get 10 th not 10th same with 20. so i changed the formula 10 and 20 to 9 and 19. worked but 30th is still 30 th! a little bit shorter: =A1&IF(AND(RIGHT(A1,1)="1",A1<11),"st",IF(AND(RIG HT(A1,1)="2",A1<12),"nd",IF(AND(RIGHT(A1,1)="3",A 1<13),"rd","th"))) Regards Claus B. Exactly what I was hoping for you FormulaWizard you! Thanks for stepping in!  Garry Free usenet access at http://www.eternalseptember.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion 
#7




(A ND(RIGHT(A1,1) i gues that should be AND.
I am confused now but the latest formula looks like an earlier one! wish i could post the excel file! thanks all 
#8




date format in excel 2002 on win 7
Hi,
Am Mon, 4 May 2020 18:42:33 +0100 schrieb anon125: (A ND(RIGHT(A1,1) i gues that should be AND. I am confused now but the latest formula looks like an earlier one! have a look: https://1drv.ms/x/s!AqMiGBK2qniTgety...94iGQ?e=j4dyfi Regards Claus B.  Windows10 Office 2016 
#9




date format in excel 2002 on win 7
(A ND(RIGHT(A1,1) i gues that should be AND.
I am confused now but the latest formula looks like an earlier one! wish i could post the excel file! thanks all Claus' version has 1 less nested if, and is where I would've ended up after further study, but was too tired to go there. Regardless, I'm always happy when Claus offers a more efficient version that accomplishes the task!  Garry Free usenet access at http://www.eternalseptember.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion 
#10




This 1drv.ms page can’t be foundNo web page was found for the web address: https://1drv.ms/x/s!AqMiGBK2qniTgety...94iGQ?e=j4dyfi
HTTP ERROR 404 
Reply 

Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Excel 2002: How to make the date format uniform ?  Excel Discussion (Misc queries)  
using a date format not in Excel 2002  Excel Discussion (Misc queries)  
Date format in Excel 2002  Excel Discussion (Misc queries)  
Excel 2002  Date format keeps changing  Excel Discussion (Misc queries)  
Converting a date in Excel 2002 to a Year/Quarter format  Excel Worksheet Functions 