Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting dates
Does VBA have any functions that convert date strings such as M/D/YY, MM/D/YY, or M/DD/YY to MM/DD/YY format?
For example, if sDate == "1/9/19", then the output should be sDate = "01/09/19" The key is that the output date should be in 2-digit month, day and year format, separated by forward slashes. i.e "MM/DD/YY" - Robert |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting dates
Hi Robert,
Am Fri, 10 May 2019 03:12:00 -0700 (PDT) schrieb RG III: Does VBA have any functions that convert date strings such as M/D/YY, MM/D/YY, or M/DD/YY to MM/DD/YY format? For example, if sDate == "1/9/19", then the output should be sDate = "01/09/19" if your date is a string then try for A1: With Range("A1") .Value = CDate(sdate) .NumberFormat = "mm/dd/yy" End With Regards Claus B. -- Windows10 Office 2016 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting dates
Does VBA have any functions that convert date strings such as M/D/YY,
MM/D/YY, or M/DD/YY to MM/DD/YY format? For example, if sDate == "1/9/19", then the output should be sDate = "01/09/19" The key is that the output date should be in 2-digit month, day and year format, separated by forward slashes. i.e "MM/DD/YY" - Robert Converting a string date to a date will always result the default system date format unless you specify its NumberFormat as Claus has shown. This has needed to be the practice since the default date format changed in Vista. My practice with date cells has been to set the format when designing the worksheet template so the month portion ALWAYS displays its short format name "Mmm" so there's no ambiguity; - and this is how string dates are stored as well! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting dates
My value is not in cell A1. It's actually in a string variable.
For example: sDate = "1/9/19" How do you use the NumberFormat in this situation? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting dates
My value is not in cell A1. It's actually in a string variable.
For example: sDate = "1/9/19" How do you use the NumberFormat in this situation? There's an easier way! Here's some examples of what I was trying to describe for using non-ambiguous date formats... sDate = Format$(Date, "dd/Mmm/yyyy") sDate = Format$(Date, "Mmm/dd/yyyy") sDate = Format$(Date, "yyyy/Mmm/dd") ...which returns the following strings for today's date: "11/May/2019" *this is a built-in format* To load this into a cell with default NumberFormat (Win7Pro): Cells(1, 1) = sDate 'returns/: 5/11/2019 displayed as 11-May-19 Cells(1, 1) = CDate(sDate) 'returns: 5/11/2019 displayed as 11-May-19 "May/11/2019" *this is a custom format* To load this into a cell with default NumberFormat (Win7Pro): Cells(1, 1) = sDate 'returns/displays: "May/11/2019" *as text* Cells(1, 1) = CDate(sDate) 'returns/displays: 5/11/2019 "2019/May/11" *this is a custom format* To load this into a cell with default NumberFormat (Win7Pro): Cells(1, 1) = sDate 'returns/displays: "2019/May/11" *as text* Cells(1, 1) = CDate(sDate) 'returns/displays: 5/11/2019 Using the immediate window: ActiveCell=Format$(Date,"dd/Mmm/yyyy") 'returns: 5/11/2019 displayed as 11-May-19 ActiveCell=CDate(Format$(Date,"dd/Mmm/yyyy")) 'returns/displays: 5/11/2019 ActiveCell=Format$(Date,"Mmm/dd/yyyy") 'returns/displays: "May/11/2019" *as text* ActiveCell=CDate(Format$(Date,"Mmm/dd/yyyy")) 'returns/displays: 5/11/2019 ActiveCell=Format$(Date,"yyyy/Mmm/dd") 'returns/displays: "2019/May/11" *as text* ActiveCell=CDate(Format$(Date,"yyyy/Mmm/dd")) 'returns/displays: 5/11/2019 You can play with this code to learn how VBA and/or Excel Date NumberFormats work by selecting a different DateFormat for the target cells *AFTER* running this sub. Sub Format_DateTime() Dim sDate$ With Range("A1").Resize(3, 2) sDate = Format$(Date, "dd/Mmm/yyyy"): .Cells(1) = sDate: .Cells(2) = CDate(sDate) sDate = Format$(Date, "Mmm/dd/yyyy"): .Cells(3) = sDate: .Cells(4) = CDate(sDate) sDate = Format$(Date, "yyyy/Mmm/dd"): .Cells(5) = sDate: .Cells(6) = CDate(sDate) End With With Range("C1").Resize(3, 2) .Cells(1) = Format$(Date, "dd/Mmm/yyyy"): .Cells(2) = CDate(Format$(Date, "dd/Mmm/yyyy")) .Cells(3) = Format$(Date, "Mmm/dd/yyyy"): .Cells(4) = CDate(Format$(Date, "Mmm/dd/yyyy")) .Cells(5) = Format$(Date, "yyyy/Mmm/dd"): .Cells(6) = CDate(Format$(Date, "yyyy/Mmm/dd")) End With End Sub -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting dates
Watch out for line wraps in the sub; - if you get red text in the VBE after
pasting just Backspace it until it fits the preceding line and auto-corrects. -- 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting dates
That Format$() function is what I really needed the most. Thanks.
It even works when the input date string has hyphens, such as: "1-3-2019" ' This gets converted to "01/03/19" "1-3" ' This also gets converted to "01/03/19" Nice! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting dates
It even works when the input date string has hyphens, such as: "1-3-2019" ' This gets converted to "01/03/19" "1-3" ' This also gets converted to "01/03/19" I forgot to ask, how can I test if the input string does NOT resemble a date string (or even a date string with hyphens like above)? |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting dates
It even works when the input date string has hyphens, such as: "1-3-2019" ' This gets converted to "01/03/19" "1-3" ' This also gets converted to "01/03/19" I forgot to ask, how can I test if the input string does NOT resemble a date string (or even a date string with hyphens like above)? This is why I suggested you play with the Date NumberFormats AFTER running the code! If you start with a clean sheet and select A1:D3 as a group, then run the code, THEN try different date formats for the selection. Note that the left-aligned content is text AND the right-aligned content is a date. Observe the FormulaBar contents while navigating the selection via the Enter key to see the actual cell values versus what displays. As you can see, hyphens are a built-in format in dates and so is interpreted correctly; - the only other character that is correctly interpreted is the forward slash. Let your curiosity run wild and enjoy the exercise!!<g -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting Julian Dates to regular dates | Excel Worksheet Functions | |||
Converting Dates | Excel Worksheet Functions | |||
Converting Text dates into dates | Excel Programming | |||
Converting Dates | Excel Programming | |||
converting dates | Excel Programming |