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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.eternal-september.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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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.eternal-september.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 |
#11
|
|||
|
|||
i shall give up now.
thanks for trying |
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 |