UDF Date Format

[StuartR]

...if you enter 3/6 it is interpreted as the third day of the sixth month...

This is definitely true for me, but I live in the UK. In the US this should be interpreted as the 6th day of the 3rd month.

[snb]

Not because you live in the UK, but because you have told Windows to interpret your input in UK-fashion.

[LisaGreen]

It gets worse!!!!

You can "format" a cell in an extraordinary number of ways that will convert to a date with cdate().

I tried the custom format...
dddd =ppp mmmm $ yyyy

And entered...
23 apr 2020

The cell showed...
Friday =ppp April $ 2020

And in the immediate window...
?cdate(selection.value)

showed...
4/23/2020


Lisa

[LisaGreen]

Alan,

If you do "play" with the registry entries, make sure you back up that key first.
To do that for the international key... select the key in the left pane, right click and export. Call it whatever you want. I usually include a date and time stamp in the name :-)
I actually really do LOL!!

After experimenting... go back to that file and double click it. That will import the saved file.

Lisa

[HansV]

Lisa, the number format of a cell is merely an instruction on how to display the value. It does not change the underlying value in any way whatsoever.
If you enter 23 apr 2020, Excel will store the number 43944. It doesn't matter whether this is displayed as 23-Apr-20 or as 2020/04/23 or as Thursday =ppp April $ 2020. The value remains 43944, and if you apply CDate, you'll get the 23rd of April.

[Doc.AElstein]

Hello \ -_- /
It actually seems quite easy to change the format via the registry.. I am using now the new IBF ( International Bat Format ) now on my computer :
Bat Date Format.JPG : https://imgur.com/pYEu3Nk http://i.imgur.com/pYEu3Nk.jpg
Computer\HKEY_CURRENT_USER\Control Panel\International

Bat Date Format.JPG

On a serious note, being able to do this will make it easier to help on issues like at the original first post here, since I can easily switch to the OPs format as required…

Balman \ -_- /

[snb]

@DocE

Windowsbutton/ settings/ configuration /international

[Doc.AElstein]

I have German Windows and I don't see an obvious German equivalent of those steps. I can get as far as the Windows Button, I think, that is the one with the Microsoft flag symbol on it. I don't know what the other steps are about.
Balman \ -_- /

[StuartR]

If you want a complete freeform format for the date then you can do this by opening Settings, search for "Region", click on "Additional date, time, & regional settings", then on "Change date, time, or number formats", then on "Additional settings", finally select the "Date" tab

[Doc.AElstein]

For the end date I'm using Now() in the formula but the value has time associated with it. I have tried to format Now() in the formula with Format(Now(),"mm/dd/yyyy") but that does not work. .

Hi John
I just changed my ShortDate settings from my usual IBF format to the standard US ones, MM/dd/yyyy , which I expect you probably have.

Having done that, I get for all the following code lines 04/24/2020
US_Dates.JPG : https://imgur.com/V3YR5qc" onclick="window.open(this.href);return false;

US_Dates.JPG

Code: Select all

 Sub USdates()
Dim D1, D2, D3, D4, D5, D6, D7, D8, D9, D91 ' all will be Variant type by default
 Let D1 = CDate(CLng(Now()))
 Let D2 = Left(Now(), InStr(1, Now(), " ", vbBinaryCompare) - 1)
 Let D3 = Format(Now(), "mm/dd/yyyy")
 Let D4 = Date
 Let D5 = CDate(Format(Now(), "mm dd yyyy"))
 Let D6 = CDate(Format(Now(), "mm,dd,yyyy"))
 Let D7 = CDate(Format(Now(), "dd/mm/yyyy"))
 Let D8 = CDate(Format(Now(), "dd   , mmmm  - yyyy"))
 Let D9 = CDate(Format(Now(), "dd   , mmmm    \ yyyy"))
 Let D91 = CDate(Format(Now(), "dd   , mmmm    \- yyyy"))
Stop ' Do this so the Watch window still has stuff in it
End Sub 

So I am puzzled that your attempt at Format(Now(),"mm/dd/yyyy") did not work.
Having said that…. , if you look at my screenshot, you will see that Format(Now(),"mm/dd/yyyy") is returning a string. – So it might be worth a try to wrap it in a Cdate() like
Cdate(Format(Now(),"mm/dd/yyyy"))

Balman \ -_- /

[LisaGreen]

If you want a complete freeform format for the date then you can do this by opening Settings, search for "Region", click on "Additional date, time, & regional settings", then on "Change date, time, or number formats", then on "Additional settings", finally select the "Date" tab

Never thought of that!... Thank You Stuart!

Lisa

[LisaGreen]

Lisa, the number format of a cell is merely an instruction on how to display the value. It does not change the underlying value in any way whatsoever.
If you enter 23 apr 2020, Excel will store the number 43944. It doesn't matter whether this is displayed as 23-Apr-20 or as 2020/04/23 or as Thursday =ppp April $ 2020. The value remains 43944, and if you apply CDate, you'll get the 23rd of April.

hello hans...

Yes I totally agree. So much depends on the source that feeds CDate().

For example...
?cdate("23 p apr 2020")

returns..
4/1/2020 11:00:00 PM

And ..
?cdate("23 a apr 2020")

returns..
4/1/2020 11:00:00 PM

Exaclty the same!!
And they're both wrong! So where did that come from!?

I think the moral is... suck it and see... You have to try it.

I've forgotten now if the OP wanted to put a date in a cell or use it in a function in VBA!!!

Lisa

[HansV]

"23 p" and "23 a" are interpreted as time. We shouldn't use a/p (AM/PM) with a 24-hour clock, but VBA is fairly forgiving - it turns both into 23:00:00, or 11:00:00 PM in US notation.
That leaves "apr 2020", which is interpreted as (the first of) the month of April. In US notation 4/1/2020.

[LisaGreen]

That's totally new for me!!

Thank you Hans... again!!

Lisa

[HansV]

I guess the moral of the story is that CDate may return unexpected results if you feed it unusual formats.

[LisaGreen]

I think your guess is correct!

I'm tempted to say that dates are a can of worms.

Lisa

[HansV]

But I wonder - why would you want to feed "23 p apr 2020" to CDate at all?

[LisaGreen]

I was trying things to do with dates Hans.

Sorry if I confused you.

As I mentioned though I can't remember what the OP wanted.

I'm going to go back and read from the start of this thread.

Lisa

[jstevens]

Alan,

Thanks for the code in your post and to all who contributed to this topic.

I never knew we could spend so much "time" on one subject.

[LisaGreen]

A lot of the info is a bit dated though..

Lisa