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 wrote:...if you enter 3/6 it is interpreted as the third day of the sixth month...
UDF Date Format
-
- Administrator
- Posts: 12619
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: UDF Date Format
StuartR
-
- 4StarLounger
- Posts: 587
- Joined: 14 Nov 2012, 16:06
Re: UDF Date Format
Not because you live in the UK, but because you have told Windows to interpret your input in UK-fashion.
-
- 5StarLounger
- Posts: 964
- Joined: 08 Nov 2012, 17:54
Re: UDF Date Format
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
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
-
- 5StarLounger
- Posts: 964
- Joined: 08 Nov 2012, 17:54
Re: UDF Date Format
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
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
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: UDF Date Format
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.
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.
Best wishes,
Hans
Hans
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
IBF ( International Bat Format ) 23 ...... 04 \ -_- / 2020
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 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 \ -_- /
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 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 \ -_- /
You do not have the required permissions to view the files attached to this post.
Last edited by Doc.AElstein on 06 Nov 2021, 08:49, edited 2 times in total.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- 4StarLounger
- Posts: 587
- Joined: 14 Nov 2012, 16:06
Re: UDF Date Format
@DocE
Windowsbutton/ settings/ configuration /international
Windowsbutton/ settings/ configuration /international
Last edited by snb on 24 Apr 2020, 20:42, edited 1 time in total.
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: UDF Date Format
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 \ -_- /
Balman \ -_- /
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- Administrator
- Posts: 12619
- Joined: 16 Jan 2010, 15:49
- Location: London, Europe
Re: UDF Date Format
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
You do not have the required permissions to view the files attached to this post.
StuartR
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: UDF Date Format
Hi Johnjstevens wrote: 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. .
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;
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
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 \ -_- /
You do not have the required permissions to view the files attached to this post.
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- 5StarLounger
- Posts: 964
- Joined: 08 Nov 2012, 17:54
Re: UDF Date Format
Never thought of that!... Thank You Stuart!StuartR wrote: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
Lisa
-
- 5StarLounger
- Posts: 964
- Joined: 08 Nov 2012, 17:54
Re: UDF Date Format
hello hans...HansV wrote: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.
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
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: UDF Date Format
"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.
That leaves "apr 2020", which is interpreted as (the first of) the month of April. In US notation 4/1/2020.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 964
- Joined: 08 Nov 2012, 17:54
Re: UDF Date Format
That's totally new for me!!
Thank you Hans... again!!
Lisa
Thank you Hans... again!!
Lisa
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: UDF Date Format
I guess the moral of the story is that CDate may return unexpected results if you feed it unusual formats.
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 964
- Joined: 08 Nov 2012, 17:54
Re: UDF Date Format
I think your guess is correct!
I'm tempted to say that dates are a can of worms.
Lisa
I'm tempted to say that dates are a can of worms.
Lisa
-
- Administrator
- Posts: 78586
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: UDF Date Format
But I wonder - why would you want to feed "23 p apr 2020" to CDate at all?
Best wishes,
Hans
Hans
-
- 5StarLounger
- Posts: 964
- Joined: 08 Nov 2012, 17:54
Re: UDF Date Format
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
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
-
- GoldLounger
- Posts: 2631
- Joined: 26 Jan 2010, 16:31
- Location: Southern California
Re: UDF Date Format
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.
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.
Regards,
John
John
-
- 5StarLounger
- Posts: 964
- Joined: 08 Nov 2012, 17:54
Re: UDF Date Format
A lot of the info is a bit dated though..
Lisa
Lisa