What would be the quickest way to Format "Mar 24 2005" => "03/24/2005"

Thanks
AlexF777Asked:
Who is Participating?
 
Erick37Connect With a Mentor Commented:
MsgBox Format(CDate("Mar 24 2005"), "mm/dd/yyyy")
0
 
sudheeshthegreatCommented:
to be quicker ;-)

Format("Mar 24 2005", "mm/dd/yyyy")
0
 
ShauliCommented:
MsgBox CDate("Mar 24 2005")

S
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
cjardCommented:
define quick:

time it takes to type
time it takes the CPU to do

?
0
 
cjardCommented:
Option Explicit

Private Sub Form_Load()
  Dim start As Double
  Dim iter As Long
  Dim s As String
 
  start = DateTime.Timer
  For iter = 1 To 100000
    s = CDate("24 Mar 2004")
  Next iter
  Debug.Print "CDATE method took " & DateTime.Timer - start & " sec"
 
  start = DateTime.Timer
  For iter = 1 To 100000
    s = Format("24 Mar 2004", "mm/dd/yyyy")
  Next iter
  Debug.Print "FORMAT method took " & DateTime.Timer - start & " sec"
 
  start = DateTime.Timer
  For iter = 1 To 100000
    s = Format(CDate("Mar 24 2005"), "mm/dd/yyyy")
  Next iter
  Debug.Print "FORMATCDATE method took " & DateTime.Timer - start & " sec"
 
  start = DateTime.Timer
  For iter = 1 To 100000
    s = Format("24 Mar 2004", "mm/dd/yyyy")
  Next iter
  Debug.Print "FORMAT$ method took " & DateTime.Timer - start & " sec"
 
End Sub



my laptop's results are:
CDATE method took 0.657281250001688 sec
FORMAT method took 0.797156250002445 sec
FORMATCDATE method took 0.703281249996508 sec
FORMAT$ method took 0.797156250002445 sec


about 3 seconds to convert 400,000 dates... i think youll have other issues in your data prep than this
0
 
cjardCommented:
ps, i made an error, not putting Format$ on the last bit of code, only the string printout.. but it makes no difference, infact my Format$ took longer
0
 
Erick37Commented:
Agreed that any of the above works well enough with the exception of:
s = CDate("24 Mar 2004")

Without using the Format function, the returned format will be the system default short date format.  This may not be in the form "mm/dd/yyyy"
0
 
ShauliCommented:
<<< This may not be in the form "mm/dd/yyyy">>>
That is true of course, if the locale is not in the format of "mm/dd/yyyy". However, if the locale IS in that format, then this would be the fastest way.

S
0
 
sudheeshthegreatCommented:
assuming the locale having a format of mm/dd/yyyy would, i believe, be a big assumption. why would you want to convert dates from one format to another then?
0
All Courses

From novice to tech pro — start learning today.