Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Format
Hi Greg,
I don't believe you can do this using Format, however you can use a simple workaround. One thing to mention though is that once you use the ordinal version of the number then the date will not be recognised as a date by Excel. So if you are only using your routine to display a date then it is fine, if you wish to use this date in Excel then you're better off keeping in the format you already have. OK, to do what you are asking you need to test the final digit of the day and then append the characters as necessary. I have used the Format function twice (either side of the added characters) because otherwise these characters will be used in the formatting - 4th will become 4t and the h will display the hour, st, nd and rd also use characters from the Format function, so the double Format usage is necessary for all. Select Case Right(Format(Now, "dd"), 1) Case 1 MAIN.Caption = Format(Now, "hh:mm:ss dddd dd") & "st " & Format(Now, "mmmm yyyy") Case 2 MAIN.Caption = Format(Now, "hh:mm:ss dddd dd") & "nd " & Format(Now, "mmmm yyyy") Case 3 MAIN.Caption = Format(Now, "hh:mm:ss dddd dd") & "rd " & Format(Now, "mmmm yyyy") Case Else MAIN.Caption = Format(Now, "hh:mm:ss dddd dd") & "th " & Format(Now, "mmmm yyyy") End Select I hope this helps. Sean. -- (please remember to click yes if replies you receive are helpful to you) "Greg B" wrote: I am wondering how I can make the vba to add the extensions to the date i.e. 24 to go to the 24th 23 to 23rd etc Thanks here is the code I am using MAIN.Caption = Format(Now, "hh:mm:ss" & " " & "dddd dd mmmm yyyy") |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Format
Thanks SeanC
Absolutely perfect for what i needed. Thanks Once Again Greg "SeanC UK" wrote in message ... Hi Greg, I don't believe you can do this using Format, however you can use a simple workaround. One thing to mention though is that once you use the ordinal version of the number then the date will not be recognised as a date by Excel. So if you are only using your routine to display a date then it is fine, if you wish to use this date in Excel then you're better off keeping in the format you already have. OK, to do what you are asking you need to test the final digit of the day and then append the characters as necessary. I have used the Format function twice (either side of the added characters) because otherwise these characters will be used in the formatting - 4th will become 4t and the h will display the hour, st, nd and rd also use characters from the Format function, so the double Format usage is necessary for all. Select Case Right(Format(Now, "dd"), 1) Case 1 MAIN.Caption = Format(Now, "hh:mm:ss dddd dd") & "st " & Format(Now, "mmmm yyyy") Case 2 MAIN.Caption = Format(Now, "hh:mm:ss dddd dd") & "nd " & Format(Now, "mmmm yyyy") Case 3 MAIN.Caption = Format(Now, "hh:mm:ss dddd dd") & "rd " & Format(Now, "mmmm yyyy") Case Else MAIN.Caption = Format(Now, "hh:mm:ss dddd dd") & "th " & Format(Now, "mmmm yyyy") End Select I hope this helps. Sean. -- (please remember to click yes if replies you receive are helpful to you) "Greg B" wrote: I am wondering how I can make the vba to add the extensions to the date i.e. 24 to go to the 24th 23 to 23rd etc Thanks here is the code I am using MAIN.Caption = Format(Now, "hh:mm:ss" & " " & "dddd dd mmmm yyyy") |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
permanent conversion of 1904 date format to 1900 date format | Excel Worksheet Functions | |||
change date format dd/mm/yyyy to Julian date format? | Excel Worksheet Functions | |||
convert serial date format to normal date format | Excel Discussion (Misc queries) | |||
code to convert date from TEXT format (03-02) to DATE format (200203) | Excel Programming | |||
Change a date in text format xx.xx.20xx to a recognised date format | Excel Programming |