ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)
-   -   date format in excel 2002 on win 7 (https://www.excelbanter.com/excel-discussion-misc-queries/454676-date-format-excel-2002-win-7-a.html)

anon125

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

GS[_6_]

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

Claus Busch

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

anon125

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

anon125

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

GS[_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.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

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!

wish i could post the excel file!


thanks all

Claus Busch

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

GS[_6_]

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

anon125

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

anon125

i shall give up now.
thanks for trying


All times are GMT +1. The time now is 01:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com