ExcelBanter - Converting dates

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Converting dates (https://www.excelbanter.com/excel-programming/454296-converting-dates.html)

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

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

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?

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

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

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!

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