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