• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Applying Superscript to dates while entering

V

VDS

Member
If we enter date in any format, say 1/1/2000 in Column A2, it can be converted to 1 JAN 2000 with Text Function
=UPPER(TEXT(A2, "D MMMMMMM, YYYY")). I want to add the superscript portion like 1st, 2nd, 3rd, etc while converting date.

If we enter the supescript portion in MS word, it will automatically update. While in excel, what will be the option ?
Attach herewith a sample file. Where column A gives dummy dates and Column B gives converted format in Text.

What I want if date is entered in A1, (23/5/1998), and press the push button, it will automatically convert to 23RD MAY, 1998 in Column A1 itself without additional columns.
I tried with macro but unable to suceed. How this can be corrected or any better ways other than macro ?

Help is requested

VDS
 

Attachments

  • FORM 26 AS.xlsm
    41.8 KB · Views: 12
@Sir,

What to do when I post a new tread related to any Macro ? In the new thread tab, it does not specify anything. Nor in the VB forum. Shall I post it again or how to move this into VBA Macro?

VDS
 
It's Called ordinal number.

Code: 
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
  Dim Cell As Range
  For Each Cell In Target
    If Cell.Column = 1 And IsDate(Cell.Value) Then
        Cell = Day(Cell) & Mid$("thstndrdthththththth", 1 - 2 * (Day(Cell.Value) _
                          Mod 10) * (Abs(Day(Cell.Value) Mod 100 - 12) > 1), 2) & " " & MonthName(Month(Cell)) & "," & Year(Cell)
    End If
  Next
End Sub


Here's few more method..

=A1&MID("thstndrdthstndrdth",MATCH(IF(MOD(A1,100)>29,MOD(A1,10)+20,MOD(A1,100)),{0,1,2,3,4,21,22,23,24},1)*2-1,2)


Code: 
Function Addth(pNumber As String) As String

'Updateby20141027
Select Case CLng(VBA.Right(pNumber, 1))
    Case 1
    Addth = pNumber & "st"
    Case 2
    Addth = pNumber & "nd"
    Case 3
    Addth = pNumber & "rd"
    Case Else
    Addth = pNumber & "th"
End Select
Select Case VBA.CLng(VBA.Right(pNumber, 2))
    Case 11, 12, 13
    Addth = pNumber & "th"
End Select
End Function

ref links

http://answers.microsoft.com/en-us/...l/16b4f080-4e50-4f52-a70f-7d37b883ce8a?auth=1

http://www.extendoffice.com/documents/excel/2167-excel-convert-cardinal-to-ordinal.html

http://www.cpearson.com/excel/Ordinal.aspx
 
Dear Deepak,
Thanks for your reply and also providing 3 options.
Option No.1 : Function addth()
Option No.2 : simple function of excel without VB
Option No.3 : Private Sub Worksheet_Change(ByVal Target As Range)
By using Option 1 and Option 2, result displays in a different way. Please note the following :-
b) Dates are not specified. It may be any date. Even no of total number of dates are not unknown.
In the program line of Function addth() 1, I thnik in the first part select case statement is perfect. but "Addth = pNumber & "th" is to be further stramlined. Due to this, all the output is coming only th. Pls correct me if I am wrong.
c) The superscript portion (ST, ND, RD AND TH) comes after the year instead of month in both the options.
d) The date format is displayed in numeric rathar than in "d MMMMM,YYYY" format. This has to be corrected.
e) It would be far better if the result is coming in Colum A itself without any additional columns.
The symbol of dates should be 1st January, 2nd January, 3rd January, 21st, 22nd, 23rd, 18th, 28th and and like that. Attaching herewith the excel file using both the options as suggesed by you and the results therein. The output is required as per Column E and this is infact what I tried to point out. Hope you will understand.

What is the use of Option No.3 ? I am umable to understand this program code.
Much appreciate your reply if the program codes is given separately rather in excel as I can't acces the internet every time.

VDS
 

Attachments

  • data.xlsm
    30.5 KB · Views: 6
Hi,

Just in case if you need formula solution, for valid date 01/01/2014 to be displayed as 01st January, 2014:

=LEFT(TEXT(A2,"dd"),2)&IF(AND(MOD(RIGHT(TEXT(A2,"dd"),1),100)>10,MOD(RIGHT(TEXT(A2,"dd"),1),100)<14),"th",CHOOSE(MOD(RIGHT(TEXT(A2,"dd"),1),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))&TEXT(A2," mmmm, yyyy")

Seems to be working on posted sample.

Regards,
 
@ Khalid,

Nice and brilliant formula. It is working perfect and hats off to you. Still, few things can be corrected.

1. While converting dates from 1 to 9 it will display into 2 digits. If we enter 01/01/2015, it will disply 01st. Shall this 0 can be omitted ?.

2. If the subscript portion is put upwards ST or ND, etc upwards of date, would be far better. Not necessary, but optional. (may be possible through macro).

Anyway, once again this is Very good.


VDS
 
VDS said:
@ Khalid,

Nice and brilliant formula. It is working perfect and hats off to you. Still, few things can be corrected.

1. While converting dates from 1 to 9 it will display into 2 digits. If we enter 01/01/2015, it will disply 01st. Shall this 0 can be omitted ?.

2. If the subscript portion is put upwards ST or ND, etc upwards of date, would be far better. Not necessary, but optional. (may be possible through macro).

Anyway, once again this is Very good.


VDS
Click to expand...
Hi VDS,

I think we can make a check for leading zero with IF:

Try replacing the following portion from above formula:
=LEFT(TEXT(A2,"dd"),2)

with this:
=IF(LEFT(TEXT(A2,"dd"),1)="0",LEFT(TEXT(A2,"d"),1),LEFT(TEXT(A2,"dd"),2))

It will looks like:
=IF(LEFT(TEXT(A2,"dd"),1)="0",LEFT(TEXT(A2,"d"),1),LEFT(TEXT(A2,"dd"),2))&IF(AND(MOD(RIGHT(TEXT(A2,"dd"),2),100)>10,MOD(RIGHT(TEXT(A2,"dd"),2),100)<14),"th",CHOOSE(MOD(RIGHT(TEXT(A2,"dd"),2),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))&TEXT(A2," mmmm, yyyy")

Not fully tested.


Edit: Spell corrected.
 
Last edited:
Made little shorter by removing unnecessary LEFT formulas:

=IF(LEFT(TEXT(A2,"dd"),1)="0",TEXT(A2,"d"),TEXT(A2,"dd"))&IF(AND(MOD(RIGHT(TEXT(A2,"dd"),2),100)>10,MOD(RIGHT(TEXT(A2,"dd"),2),100)<14),"th",CHOOSE(MOD(RIGHT(TEXT(A2,"dd"),2),10)+1,"th","st","nd","rd","th","th","th","th","th","th"))&TEXT(A2," mmmm, yyyy")
 
@ khalid ,
The leading zeros are now removed. Still for 11;12 & 13 the" th" portion is different. How this can be corrected ?
 
VDS said:
@ khalid ,
The leading zeros are now removed. Still for 11;12 & 13 the" th" portion is different. How this can be corrected ?
Click to expand...
Hi VDS,
I tested both formulas (post # 10 and 12)

Ordinal Dates.PNG

I am getting correct and same output.
Can you share the dates you were testing?

Regards,
 
Mean while check this test data.
I have tested from 01/01/2015 to 31/12/2015
and seems working :)
 

Attachments

  • Ordinal Dates VDS.xlsx
    24 KB · Views: 16
@Khalid

Sorry for my belated reply. Your formula is perfect now.

I have copied the formula and corrected. Thanks a lot. Earlier what I pointed out to get the data as per the attached worksheet (Column C). I am sure, for this type of format, VB must be required. If anything is worked out to get as per the desired format, it will be well and good. IF time permits, this can be done.

Anyway,thanks once again for your brilliant answer / formula.


VDS
 

Attachments

  • New Microsoft Office Excel Worksheet.xlsx
    10.5 KB · Views: 14
VDS said:
@Khalid

Sorry for my belated reply. Your formula is perfect now.
Click to expand...
:)
No problem dear.
You are welcome.

VDS said:
Earlier what I pointed out to get the data as per the attached worksheet (Column C). I am sure, for this type of format, VB must be required. If anything is worked out to get as per the desired format, it will be well and good. IF time permits, this can be done.

Anyway,thanks once again for your brilliant answer / formula.

VDS
Click to expand...

Wait someone for the VBA.
All best.
 
One important thing I noticed, if we are doing any formatting, the output will be displayed as formatted text. But in the formula bar, it will display only as a plain text. How is this ? Is it a special feature? Can you comment on this.


VDS
 
A demonstration from last attachment :​
Code: 
Sub DateBrittOrdinalEnd(Rg As Range)
     Dim Rc As Range, E$
For Each Rc In Rg
    If IsDate(Rc.Value) Then
                    V = Day(Rc.Value)
        Select Case V
               Case 1, 21, 31:  E = "st"
               Case 2, 22:      E = "nd"
               Case 3, 23:      E = "rd"
               Case Else:       E = "th"
        End Select
         Rc.Value = V & E & Evaluate("TEXT(" & Rc.Value2 & ","" mmmm, yyyy"")")
         Rc.Characters(2 - (V > 9), 2).Font.Superscript = True
    End If
Next
End Sub

Sub Demo()
With Sheet1
                Application.ScreenUpdating = False
         .[A2].CurrentRegion.Columns(1).Copy .[E2]
    With .[E2].CurrentRegion.Columns(1)
         .Formula = .Value
          DateBrittOrdinalEnd .Cells
         .AutoFit
    End With
                Application.ScreenUpdating = True
End With
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
Last edited:
@Dear Marc,

This is classic....classic....classic

Still, can you do minor modifications.

If we enter dates in Column A Column B also filled with another format of date. This has to be avoided.

Further, all the time, it Column A may not be same. So, the code to be modified with Active cell so that data as per any column can be done with the VB code. Sample File attached.


VDS
 

Attachments

  • data 123.xlsm
    33.9 KB · Views: 21


Then you can call my so classic DateBrittOrdinalEnd procedure
in the Change worksheet event like Excel Ninja Deepak yet showed you …
​
 
Last edited:
You must log in or register to reply here.
Back
Top