DocAElstein
11-23-2021, 10:40 PM
Some date notes related to this thread
https://eileenslounge.com/viewtopic.php?f=30&t=37384
These notes here are also in preparation for a possible detailed Blog on VBA dates
Sub MessinMitdates() ( see here : )
A macro messing about with date functions, including to get at the implied system short date format, sShortDate
Rem 1 sShortDate ( https://i.postimg.cc/x8mr56rZ/System-short-date-format.jpg https://i.postimg.cc/bYSjqwGR/System-short-date-format.jpg )
Use of DateSerial( year , month , day ) function
Crude implifiedcation technique
The arguments that this takes are quite clear. It is less obvious is that returns a date in the system short date. The first thing I do in the macro is give the function these arguments , DateSerial(9, 3, 4)
I am hoping that the given output can be similarly interpreted in different excel versions. The number I chose are based on some experimenting.
There are ' explaining notes in the macro and some more here in particular to document important variables which can be used to build the string up again later.
I don’t expect the macro to work in all circumstances. For example its assuming you use sensible numbers in a typical short date format.
The string that is given back by DateSerial(9, 3, 4) , ( in variable TestDateSerial ) , is stepped through. The position of changes in type is used to determine lengths of the numbers so we can represent them in the typical notation, - for example dd represents that a day may be shown from 01 to 31 , whereas d would mean you want to show days from 1 to 31 , so that you show double number characters only when you have to from 10 to 31
The month numbers will be handled in a similar way. The macro would catch if you use y yy yyy or yyyy , but I am expecting yy or yyyy - I have not experimented thoroughly what may result from a y or yyy
Purpose of the macro is to determine the number of characters that have been chosen for the three quantities, day , month and year , given in numbers, and also to give the actual characters chosen for the two separation bits. Typically these separation bits would be the same and would be something like a single one of these . , : / - In other words I am looking to give an output of this type of form dd/mm/yyyy or mm-d-yyyy etc.. etc… , but my macro would also catch a form like yyyyCrapddSwillmm
( You cannot use any possible character combination for the separators as there may be conflicts with character used for specific things, such as the d m and y , but it seems you can use quite a few – that yyyyCrapddSwillmm for example is perfectly valid )
Summary of the final string defining variables
Cnt1 – character count in the first returned number bit
Sep1 – the actual character string of the first separator
Pos2 – The position of the start of the middle number bit
Cnt2 – the character count in the middle number bit
Sep2 – the actual character string of the second separator
Pos3 – The position of the start of the last number bit
Cnt3 - the character count of the last number section
Some ideas and conclusions so far:
DateSerial( year , month , day ) function
Its fairly clear what this takes as arguments in terms of whether it’s a day , month or year. If you use numbers for those arguments , for example, like ( 2021, 1, 2 ) then there is never any ambiguity and it sees the date of 2nd January, 2021. The output within VBA is also fairly predictable. It appears to come out in exactly the form that you specified in your sShortdate in the registry. What gets put in a cell is a different story. The macro gives some examples. It appears to be very unpredictable
/ and similar single characters
The / and possibly to a lesser extent some other characters of the sort often used to separate things, may encourage Excel to think about making something look different in a cell to the form you give it. You need to be careful if you use this in anything related to dates, and using the “escape” character, \ , may help get you out of difficulty ( http://www.eileenslounge.com/viewtopic.php?p=289607#p289607 )
System short date , sShortDate
Possibly by design, or possibly by accident due to the last point, you will always get returned by a macro recording the code line like .._
.NumberFormat = "m/d/yyyy"
-… regardless of what your specified system short date is.
This suggests that .NumberFormat = "m/d/yyyy" is really saying/ meaning use system short date format. The results you get partially support this. But not fully: once again you may gat inconsistent results using it.
Using enclosing hash pair # # , in place of the usual 2 quote , “ “ , pair when giving a value in VBA
Usually in VBA we supply a text string in quotes like, = "My text"
However you can also supply a text string in a pair of #s , but not in an un restricted form. I don’t know yet what exactly all the accepted variations are, but initially it appears that should be in some predominantly number form that Excel may be able to recognise as a date or time. The date that Excel tries to see, would appear to be the English American. So for example, if I try to give this , that is to say I type exactly this .._
= #12___- __ 1_.2021 #
… then VBA will not error but the compiler will immediately change that, as I hit Enter , to
#12/1/2021#
It would appear that this is then taken as 1st December, 2021
https://eileenslounge.com/viewtopic.php?f=30&t=37384
These notes here are also in preparation for a possible detailed Blog on VBA dates
Sub MessinMitdates() ( see here : )
A macro messing about with date functions, including to get at the implied system short date format, sShortDate
Rem 1 sShortDate ( https://i.postimg.cc/x8mr56rZ/System-short-date-format.jpg https://i.postimg.cc/bYSjqwGR/System-short-date-format.jpg )
Use of DateSerial( year , month , day ) function
Crude implifiedcation technique
The arguments that this takes are quite clear. It is less obvious is that returns a date in the system short date. The first thing I do in the macro is give the function these arguments , DateSerial(9, 3, 4)
I am hoping that the given output can be similarly interpreted in different excel versions. The number I chose are based on some experimenting.
There are ' explaining notes in the macro and some more here in particular to document important variables which can be used to build the string up again later.
I don’t expect the macro to work in all circumstances. For example its assuming you use sensible numbers in a typical short date format.
The string that is given back by DateSerial(9, 3, 4) , ( in variable TestDateSerial ) , is stepped through. The position of changes in type is used to determine lengths of the numbers so we can represent them in the typical notation, - for example dd represents that a day may be shown from 01 to 31 , whereas d would mean you want to show days from 1 to 31 , so that you show double number characters only when you have to from 10 to 31
The month numbers will be handled in a similar way. The macro would catch if you use y yy yyy or yyyy , but I am expecting yy or yyyy - I have not experimented thoroughly what may result from a y or yyy
Purpose of the macro is to determine the number of characters that have been chosen for the three quantities, day , month and year , given in numbers, and also to give the actual characters chosen for the two separation bits. Typically these separation bits would be the same and would be something like a single one of these . , : / - In other words I am looking to give an output of this type of form dd/mm/yyyy or mm-d-yyyy etc.. etc… , but my macro would also catch a form like yyyyCrapddSwillmm
( You cannot use any possible character combination for the separators as there may be conflicts with character used for specific things, such as the d m and y , but it seems you can use quite a few – that yyyyCrapddSwillmm for example is perfectly valid )
Summary of the final string defining variables
Cnt1 – character count in the first returned number bit
Sep1 – the actual character string of the first separator
Pos2 – The position of the start of the middle number bit
Cnt2 – the character count in the middle number bit
Sep2 – the actual character string of the second separator
Pos3 – The position of the start of the last number bit
Cnt3 - the character count of the last number section
Some ideas and conclusions so far:
DateSerial( year , month , day ) function
Its fairly clear what this takes as arguments in terms of whether it’s a day , month or year. If you use numbers for those arguments , for example, like ( 2021, 1, 2 ) then there is never any ambiguity and it sees the date of 2nd January, 2021. The output within VBA is also fairly predictable. It appears to come out in exactly the form that you specified in your sShortdate in the registry. What gets put in a cell is a different story. The macro gives some examples. It appears to be very unpredictable
/ and similar single characters
The / and possibly to a lesser extent some other characters of the sort often used to separate things, may encourage Excel to think about making something look different in a cell to the form you give it. You need to be careful if you use this in anything related to dates, and using the “escape” character, \ , may help get you out of difficulty ( http://www.eileenslounge.com/viewtopic.php?p=289607#p289607 )
System short date , sShortDate
Possibly by design, or possibly by accident due to the last point, you will always get returned by a macro recording the code line like .._
.NumberFormat = "m/d/yyyy"
-… regardless of what your specified system short date is.
This suggests that .NumberFormat = "m/d/yyyy" is really saying/ meaning use system short date format. The results you get partially support this. But not fully: once again you may gat inconsistent results using it.
Using enclosing hash pair # # , in place of the usual 2 quote , “ “ , pair when giving a value in VBA
Usually in VBA we supply a text string in quotes like, = "My text"
However you can also supply a text string in a pair of #s , but not in an un restricted form. I don’t know yet what exactly all the accepted variations are, but initially it appears that should be in some predominantly number form that Excel may be able to recognise as a date or time. The date that Excel tries to see, would appear to be the English American. So for example, if I try to give this , that is to say I type exactly this .._
= #12___- __ 1_.2021 #
… then VBA will not error but the compiler will immediately change that, as I hit Enter , to
#12/1/2021#
It would appear that this is then taken as 1st December, 2021