Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 10th 19, 11:12 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 22
Default 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   Report Post  
Old May 10th 19, 12:57 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2011
Posts: 3,696
Default 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   Report Post  
Old May 10th 19, 06:10 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,055
Default 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   Report Post  
Old May 10th 19, 11:55 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 22
Default 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   Report Post  
Old May 11th 19, 07:31 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,055
Default 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   Report Post  
Old May 11th 19, 07:35 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,055
Default 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   Report Post  
Old May 12th 19, 09:21 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 22
Default 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   Report Post  
Old May 12th 19, 09:22 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: May 2019
Posts: 22
Default 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   Report Post  
Old May 12th 19, 06:35 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 1,055
Default 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 A13 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting Julian Dates to regular dates CDTucson Excel Worksheet Functions 2 June 7th 07 04:20 AM
Converting Dates Jacq Excel Worksheet Functions 5 March 5th 07 11:10 PM
Converting Text dates into dates EAB1977 Excel Programming 2 January 20th 06 04:20 PM
Converting Dates jer101[_9_] Excel Programming 8 June 25th 04 01:01 AM
converting dates mike Excel Programming 2 February 19th 04 11:31 PM


All times are GMT +1. The time now is 12:48 PM.

Powered by vBulletin® Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
Copyright 2004-2019 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017