Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 6
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Junior Member
 
Posts: 6
Default

Garry, thanks but i got 12nd and what is the a120 for?
thanks
  #5   Report Post  
Junior Member
 
Posts: 6
Default

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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Junior Member
 
Posts: 6
Default

(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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
Default 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   Report Post  
Junior Member
 
Posts: 6
Default

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   Report Post  
Junior Member
 
Posts: 6
Default

i shall give up now.
thanks for trying
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel 2002: How to make the date format uniform ? Mr. Low Excel Discussion (Misc queries) 1 November 20th 08 05:56 PM
using a date format not in Excel 2002 jbclem Excel Discussion (Misc queries) 1 November 15th 07 02:48 AM
Date format in Excel 2002 TonyN Excel Discussion (Misc queries) 1 April 5th 07 06:53 PM
Excel 2002 - Date format keeps changing B. Levien Excel Discussion (Misc queries) 0 February 6th 06 10:35 PM
Converting a date in Excel 2002 to a Year/Quarter format Jim Excel Worksheet Functions 2 January 10th 05 08:49 PM


All times are GMT +1. The time now is 12:53 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"