FORMAT string date

User avatar
sal21
PlatinumLounger
Posts: 4402
Joined: 26 Apr 2010, 17:36

FORMAT string date

Post by sal21 »

Peraphs stupid question...

I just have this string DT="1866-11-19"

I need to transform DT in date MYDT="19/11/1866"

User avatar
StuartR
Administrator
Posts: 12650
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: FORMAT string date

Post by StuartR »

Doesn't the very simple

Code: Select all

Format(DT, "dd/mm/yyyy")
work in VB?
StuartR


User avatar
DocAElstein
5StarLounger
Posts: 639
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: FORMAT string date

Post by DocAElstein »

Hallo,
( I am talking Excel VBA, I know sal21 is in VB world, but maybe it could help…… at least for anyone googling FORMAT string date )
StuartR wrote:
17 Mar 2024, 11:30
Doesn't the very simple

Code: Select all

Format(DT, "dd/mm/yyyy")
work in VB?
Perhaps that will be OK. But once in a while the forward slash can cause problems. It usually does with me
The problem is that the forward slash is one character that may have a specific meaning when used in certain date format expressions, in Excel, depending on your settings. If that situation occurs, then Excel will interpret the date format expression you give as it thinks it should, and you will get what it thinks it should give you, which may then not include a forward slash

I would try what Stuart says first. Maybe what I am talking about is not relevant for non Excel stuff, but If you get some quirky results, then this is worth a quick try. This works for me to make sure I get forward slashes

Format(DT, "dd\/mm\/yyyy")

( I think I am messing there with some "escape character stuff technic" )

Alan

Ref
https://eileenslounge.com/viewtopic.php ... 29#p290229
https://eileenslounge.com/viewtopic.php?f=30&t=37384
https://excelfox.com/forum/showthread.p ... -and-Tests
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

User avatar
SpeakEasy
4StarLounger
Posts: 593
Joined: 27 Jun 2021, 10:46

Re: FORMAT string date

Post by SpeakEasy »

>to make sure I get forward slashes

You could always use one of the named formats, such as

Format(DT, "short date")

User avatar
DocAElstein
5StarLounger
Posts: 639
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: FORMAT string date

Post by DocAElstein »

That is an interesting thing, a permutation I did not know about. My brain is not in fighting date problems modus just now. But it almost certainly will be again sometime. That will be another useful thing to have in my tool box. Thanks.
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

User avatar
HansV
Administrator
Posts: 78783
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: FORMAT string date

Post by HansV »

VBA, and I assume VB6 too, will replace / in date formats with the system date separator. So if your system date separator is different from /, you need \/ to force a date format to use /.
This a a good tip for Gemany which often uses . and for The Netherlands which uses - as date separator.
The standard date format in Italy is dd/mm/yyyy, so it's not strictly necessary for Sal21 to use \/
Best wishes,
Hans

User avatar
DocAElstein
5StarLounger
Posts: 639
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: FORMAT string date

Post by DocAElstein »

Yes that makes sense. The - is another notorious trouble maker for me in formats. ( Although I should say that in answering forum questions involving date format problems, I have messed around so much with the internal settings on different computers that I long since lost track of what default settings I may have had, Lol)
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(