UDF Date Format

[jstevens]

I have written a function that utilizes a start and end date. 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.

EL_77.png

Your suggestions are appreciated.

[HansV]

In VBA, you can use Date(). This function returns the current date without the time component.
The equivalent in an Excel formula is TODAY().

[Doc.AElstein]

Another way to get the date would be to use Now() but with a variable Dimed as Long

Code: Select all

 Sub Dts()
Dim Dte As Long
 Let Dte = Now()
End Sub 

I am slightly puzzled at your date format with the back slashes. Possibly that may be effected by some date setting in your Excel?
In my Excels ( German and English ) , I can only get that sort of format if I Dim as String and then build the thing up

Code: Select all

 Sub DtsInBkSlash()
Dim Dts As String
 Let Dts = Format(Now(), "mm") & "/" & Format(Now(), "dd") & "/" & Format(Now(), "yyyy")
End Sub 

( Dates in VBA almost always cause me problems, as they seem to get messed up somewhere when they get converted or miss read due to language convention variations etc…
I try to hold and to do all my calculations of dates as far as possible using Long variables, and then only do any formating at the end, and if possible in the final formatting I use a word for the month, just to be sure I can see that I have the correct one

Code: Select all

Sub DteCalcs()
Dim Dte1 As Long, Dte2 As Long
 Let Dte1 = Now(): Let Dte2 = Now() + 1
 MsgBox prompt:="Day difference is " & Dte2 - Dte1 & " days."
 MsgBox prompt:="Today is  " & Format(Dte1, "dd mmm, yyyy")
 MsgBox prompt:="Tomorrow is  " & Format(Dte2, "dd mmm, yyyy")
End Sub

)

Alan

[HansV]

I am slightly puzzled at your date format with the back slashes

They are slashes, Alan, not backslashes. That's a very common date separator.

[Doc.AElstein]

Thanks, I meant slashes, I am not sure why I said backslash. ... . I have seen that format before, but I don't ever see it in my VBA. If i give it that format, then it always seems to convert the slashes to points

[HansV]

VBA itself uses US settings; the default US date format is m/d/yyyy. Germany uses dd.mm.yyyy I think.

[Doc.AElstein]

That probably sort of explains it.. maybe, I am not sure...
If I do this for example.._

Code: Select all

Sub DateSlash()
Dim Dte As Date
 Let Dte = "05/05/2020"
 MsgBox Prompt:=Dte
 MsgBox Prompt:=Format(Dte, "dd/mm/yyyy")
End Sub

_.. then the message box chucks up always
O5.05.2020

I am puzzled then that Format(Now(), "mm/dd/yyyy") does not do what John wants. For me it gives 04.22.2020, so I would expect it to give him 04/22/2020
Or maybe he gets what I do.
Dates are always awkward things in Excel IMO, probably if I had alot more experience with them then they would be less of a problem for me, maybe..

[LisaGreen]

Hello...

For me...

Sub DateSlash()
Dim Dte As Date
Let Dte = "05/05/2020"
MsgBox Prompt:=Dte
MsgBox Prompt:=Format(Dte, "dd/mm/yyyy")
End Sub

seems to always give... 23-04-2020

So I think it may be connected to some local/country setting.

Lisa

[LisaGreen]

Hmmmm... It seems to be connected to the short date format.

Lisa

[LisaGreen]

this should allow you to format the date as you like by tweaking the literals and delimiter strings...

Code: Select all

MsgBox Format(Now(), "ddd" & "/" & "mmm" & "/" & "yyyy")

HTH
Lisa

[HansV]

VBA will translate a / (the USA date separator) in a date to your local date separator (usually - in The Netherlands, . in Germany, / in many other countries).
So the US date 4/23/2020 will be displayed as 23-04-2020 in The Netherlands, as 23.04.2020 in Germany, and as 23/04/2020 in the UK.
Unless you enclose the / in quotes of course. Then it becomes a literal character.

Similarly, VBA will translate . (the USA decimal separator) in a number to your local decimal separator.
So the US number 3.14 will be displayed as 3,14 in most continental European countries.

[Doc.AElstein]

Hi Lisa, Hans
Lisa, your last suggestion does not give me slashes –, in my Excel VBA it changes the slashes to points….
_._____________

I was in the middle of having a quick fiddle as Hans posted. What I have seen so far seems to more or less tie up with what Hans is saying…. it seems that my VBA will try to recognise something as a date, ( assuming it is looking for a date ) , and if it is successful then it will change it to the short date format with a . as the separator , and in the order typically used in Germany.
So for example if I chuck at VBA anything that it can recognise as a date of today, ( assuming it is looking for a date ) , then for today it will convert it to
23.04.2020
_.____

Even if VBA allows me to choose a combination ot literal and numbers for months and days in dates, it will still change the delimeter to a point .
All of these give me an output where the delimiter is changed to a point . – So I never see a slash in any of the Message box outputs

Code: Select all

 Sub DateSlashToPointOhP()
Dim Dte As Date
 Let Dte = "05/05/2020"
 MsgBox prompt:=Dte                                                 ' 05.05.2020
 MsgBox prompt:=Format(Dte, "dd/mm/yyyy")                           ' 04.23.2020
 MsgBox prompt:=Format(Now(), "mm/dd/yyyy")                         ' 04.23.2020
 MsgBox prompt:=Format(Now(), "mmm/dd/yyyy")                        ' Apr.23.2020
 MsgBox prompt:=Format(Now(), "mmm/ddd/yyyy")                       ' Apr.Do.2020
 MsgBox prompt:=Format(Now(), "ddd" & "/" & "mmm" & "/" & "yyyy")   ' Do.Apr.2020
 MsgBox prompt:=Format(Now(), "ddd" & "'/" & "mmm" & "'/" & "yyyy") ' Do'.Apr'.2020
End Sub 

The following "trick" seems to “work” to give me slashes, and it gives me the 05/05/2020 format, but it is returning a string, - this ties up again with what Hans said, and with what I posted before. Somehow this is fooling the thing... probably anybodies geuss exactly how...

Code: Select all

 Sub Slashit()
 MsgBox prompt:=Format(Now(), "mm""/""dd""/""yyyy")
End Sub 

SlashitStringMsgBox.JPG : https://imgur.com/Nd1b0wa" onclick="window.open(this.href);return false;

SlashitStringMsgBox.JPG

Any attempt to convert that or use that , or variations of it, as a date will once again result in VBA ( in my Excel ) changing the / to a point format 23.04.2020

Code: Select all

 Sub SlashitWeg()
Dim Va As Variant
 Let Va = Format(Now(), "mm""/""dd""/""yyyy")  '  04/23/2020
 Let Va = CDate(Va)  '                            23.04.2020
 Let Va = Format(Now(), "dd""/""mm""/""yyyy")  '  23/04/2020
 Let Va = CDate(Va)  '                            23.04.2020
 Let Va = Format(Now(), "mmm""/""dd""/""yyyy") '  Apr/23/2020
 Let Va = CDate(Va)  '                            23.04.2020
 Let Va = "23" & "/" & "Apr" & "/" & "2020"    '  23/Apr/2020
 Let Va = CDate(Va)  '                            23.04.2020
 Let Va = "Apr" & "/" & "23" & "/" & "2020"    '  Apr/23/2020
 Let Va = CDate(Va)  '                            23.04.2020
End Sub 

_._______________

There seem to be a quirk that VBA can’t guess what a Day is if given in literal form, even though it seems good at guessing a lot of formats.. But I expect there are quite a few other quirks and characteristics to find where dates and date conversions are concerned…

In the following examples, you see that VBA is good at guessing correctly what a date is when you give it a lot of different forms: The exception seems to be any attempt at a day in literal form, which strangely it doesn't seem to recognise...

Code: Select all

 Sub VBACantReadADay()
Dim Va As Variant
 Let Va = Format(Now(), "mmm""/""ddd""/""yyyy") ' Apr/Do/2020
' Let Va = CDate(Va)  '     Error Type mismatch
 Let Va = "Do/Apr/2020"
' Let Va = CDate(Va)  '     Error Type mismatch
 Let Va = "Apr/Do/2020"
' Let Va = CDate(Va)  '     Error Type mismatch
 Let Va = "Apr/23/2020"
 Let Va = CDate(Va)    '                          23.04.2020
 Let Va = "April/23/2020"
 Let Va = CDate(Va)    '                          23.04.2020
 Let Va = "Apri/23/2020"
 Let Va = CDate(Va)    '                          23.04.2020
 Let Va = "Ap/23/2020"
' Let Va = CDate(Va)  '     Error Type mismatch
 Let Va = "Donnerstag/April/2020"
' Let Va = CDate(Va)  '     Error Type mismatch
 Let Va = "Thursday/April/2020"
' Let Va = CDate(Va)  '     Error Type mismatch
 Let Va = "Thursday.April.2020"
' Let Va = CDate(Va)  '     Error Type mismatch
 Let Va = "23.April.2020"
 Let Va = CDate(Va)    '                          23.04.2020
 Let Va = "23.April/2020"
 Let Va = CDate(Va)    '                          23.04.2020
 Let Va = "23.April      2020"
 Let Va = CDate(Va)    '                          23.04.2020
 Let Va = "23 of April      2020"
' Let Va = CDate(Va)  '     Error Type mismatch
 Let Va = "23                     -    April      2020"
 Let Va = CDate(Va)    '                          23.04.2020
 Let Va = "23                     ,    April      2020"
 Let Va = CDate(Va)    '                          23.04.2020
 Let Va = "Th.April.2020"
' Let Va = CDate(Va)  '     Error Type mismatch
 Let Va = "    Apri                     ,    23      2020"
 Let Va = CDate(Va)    '                          23.04.2020

End Sub 

[LisaGreen]

Great post Alan!!

For information... the data stuff is held in..

Computer\HKEY_CURRENT_USER\Control Panel\International

.. of the register. It's worth having a look at I think!

Lisa

[LisaGreen]

Alan,

Yes... my previous example doesn't work well... it does give slashes here but then my delimiter is slashes as well!! Soooo of course it returned slashes for me!

This however... does work.

Code: Select all

Debug.Print Day(Now()) & "@" & Format(Now(), "mmm") & "@" & Year(Now())

It returns ...
23@Apr@2020

.. in the immediate window.

Code: Select all

Debug.Print Format(Now(), "ddd") & "@" & Format(Now(), "mmm") & "@" & Year(Now())

returns..
Thu@Apr@2020

It's only one line but it could be made into a function with a delimiter as a parameter easily.

Lisa

[HansV]

You could also use

Code: Select all

Debug.Print Format(Date, "d@mmm@yyyy")

If your separator is a character such as / that might be converted to its local equivalent, you can prefix it with \.

Code: Select all

Debug.Print Format(Date, "dd\/mm\/yyyy")

will return something like 23/04/2020 whether your system date separator is / or . or -

[Doc.AElstein]

Great post Alan!!...

Hi Lisa
Thx for the compliment, :) But my post couldn’t of been that good as I probably messed up getting some points across… ( My coding looks a bit more readable in the normal VB Editor code window with the scroll bar )

Those last things of yours are all different ways of building up a text string. …
It is convenient to use date functions as you did to get , for example, the current day , month or year, but it all amounts to the same thing: Building up a text string
If you are building up a text string, then you can have a @ or a / or anything you like and you get reproduced in the text string whatever you built up
Whether those strings are recognised as a date is a different story.
So for example, looking at some variations of your strings, almost all these will error at the attempt to do a CDate() since they are text strings which VBA cannot recognise as a date.
The only one it can recognise as a date is the last one. But then , as before, in my Excel, it converts the format to number format and separator of my language Excel:
23.04.2020

Code: Select all

 Sub LisasStrings()
 Debug.Print Day(Now()) & "@" & Format(Now(), "mmm") & "@" & Year(Now())                                       ' 23@Apr@2020
 'Debug.Print CDate(Day(Now()) & "@" & Format(Now(), "mmm") & "@" & Year(Now()))                               ' error type mismatch
 Debug.Print Format(Now(), "ddd") & "@" & Format(Now(), "mmm") & "@" & Year(Now())                             ' Do@Apr@2020
 'Debug.Print CDate(Format(Now(), "ddd") & "@" & Format(Now(), "mmm") & "@" & Year(Now()))                     ' error type mismatch
 Debug.Print Format(Now(), "ddd") & "@" & Format(Now(), "mmm") & "@" & Format(Now(), "yyyy")                   ' Do@Apr@2020
 'Debug.Print CDate(Format(Now(), "ddd") & "@" & Format(Now(), "mmm") & "@" & Format(Now(), "yyyy"))           ' error type mismatch
 Debug.Print Format(Now(), "ddd") & "/" & Format(Now(), "mmm") & "/" & Format(Now(), "yyyy")                   ' Do\Apr\2020
 'Debug.Print CDate(Format(Now(), "ddd") & "/" & Format(Now(), "mmm") & "/" & Format(Now(), "yyyy"))           ' error type mismatch ( due to quirk that VBA does not like word days )
 Debug.Print Format(Now(), "ddd") & "/" & "April   " & "." & Mid(Now(), InStrRev(Now(), ".") + 1, 4)           ' Do\April   .2020
 'Debug.Print CDate(Format(Now(), "ddd") & "/" & "April   " & "." & Mid(Now(), InStrRev(Now(), ".") + 1, 4))   ' error type mismatch ( due to quirk that VBA does not like word days )
 Debug.Print Format(Now(), "dd") & "/" & "April   " & "." & Mid(Now(), InStrRev(Now(), ".") + 1, 4)            ' 23\April   .2020
 Debug.Print CDate(Format(Now(), "dd") & "/" & "April      " & "." & Mid(Now(), InStrRev(Now(), ".") + 1, 4))  ' 23.04.2020
End Sub 

So either you have a string text which can not be a date, or if it can be a date, then we are back to the characteristic that , if you try to use it as a date, then it will be converted to the number date format with the separator corresponding to your Excel language version.

Saying the same thing in a different way: If you were to make the function you suggested, then it would need to be dimensioned as a String function. It would be a function to build a text string.
If it were to be dimensioned as a Date function then it would either error, or return the modified form converted to the number date format with the separator corresponding to your Excel language version

Code: Select all

 Sub FunckyTests()
 Debug.Print LisasSeperatingStr("@")   ' 23@Apr@2020
 Debug.Print LisasSeperatingStr("/")   ' 23/Apr/2020
 'Debug.Print LisasSeperatingDate("@") ' Error type mismatch
 Debug.Print LisasSeperatingDate("/")  ' 23.04.2020
End Sub
Function LisasSeperatingStr(ByVal Sep As String) As String
 Let LisasSeperatingStr = Day(Now()) & Sep & Format(Now(), "mmm") & Sep & Year(Now())
End Function
Function LisasSeperatingDate(ByVal Sep As String) As Date
 Let LisasSeperatingDate = Day(Now()) & Sep & Format(Now(), "mmm") & Sep & Year(Now())
End Function 

_.__________________-

.... the data stuff is held in..Computer\HKEY_CURRENT_USER\Control Panel\International
.. of the register. It's worth having a look at I think!...

I' used to shiver at the thought of anything to do with the registry, but nowadays I am game for anything.... the worst thing I can do is trash my windows, ( which I think in the meantime is not a bad idea anyway ) … Have you any suggestions on what and how I could try and change something here, to see , for example, what it does to the . / - or whatever.. ?
RegEditInt.JPG : https://imgur.com/lJnGmZk" onclick="window.open(this.href);return false;

Not important, just out of passing interest...

Alan

[Doc.AElstein]

You could also use....Format(Date, "d@mmm@yyyy")...

It seems to me at first glance, that in the Format( , ) thing , if you use variations of only d , m and y , then using Date or Now() will always give the same results.. Does that sound correct?

_.__________________________-

...
If your separator is a character such as / that might be converted to its local equivalent, you can prefix it with \.

Code: Select all

Debug.Print Format(Date, "dd\/mm\/yyyy")

will return something like 23/04/2020 whether your system date separator is / or . or -

I will take a guess that all these are basically doing the same.._

Code: Select all

 Sub SlashitWithFormat()
 Debug.Print Format(Now(), "mm""/""dd""/""yyyy")
 Debug.Print Format(Now(), "mm\/dd\/yyyy")
 Debug.Print Format(Now(), "mm") & "/" & Format(Now(), "dd") & "/" & Format(Now(), "yyyy")
End Sub 

_.. they are all using the Format( , ) in such a way that it builds up a text string which has 3 formatted Now() bits and two similar string bits which are the character /
In this case it is a string that will be recognised as a date, and converted to the number date format ( if you try to convert it to a date or use it where a date is expected ) , with the separator corresponding to your Excel language version
They all produce in my Excel 04/23/2020 , and if I do a CDate( ) on them then I get , as expected in my Excel,
23.04.2020

These also produce a text string, in this case 04@23@2020

Code: Select all

 Sub TrashitWithFormat()
 Debug.Print Format(Now(), "mm""@""dd""@""yyyy")
 Debug.Print Format(Now(), "mm\@dd\@yyyy")
 Debug.Print Format(Now(), "mm") & "@" & Format(Now(), "dd") & "@" & Format(Now(), "yyyy")
End Sub 

As expected , they all error on attempting to do a CDate( ) on them.

_.____

I find all the Date, now() format etc. things a bit messy. I personally think they are more trouble then they are worth. I would prefer to just do a bit of maths and string manipulation, and make my own functions, so that I could always be sure what was going on. Just my opinion.

Alan

[HansV]

The Date function returns the current date, e.g. the 23rd of April, 2020.
The Now function returns the current date and time, e.g. the 23rd of April, 2020 at 22:47:35.

If you apply a format that uses d, m and y, you don't use the time, so the result for Date and Now will be the same. You might as well use Date.

If you apply a format that include h, m (or n) and s, you'll use the time, so the result for Date will always display 00:00:00, while that for Now will display the current time. So if the time is important, use Now.

[Doc.AElstein]

... So if the time is important....

I would say it is, its the stuff of life :)

[snb]

Windows is totally US-centered.
Since MS found out that some people live outside the US (and for very good reasons) they introduced the possiblility to adapt Windows to local/regional settings in the Windows register.
In the case of Excel, MS-programmers introduced an 'input interpreter': if you enter 3/6 it is interpreted as the third day of the sixth month of the actual year and it will be written into Excel based on the regional settings in Windows. So even Excel is based on the US convention of the use of slashes to separate days from months and years.

Check for yourself:

enter 3/6
enter 3-6
enter 3.6

and look at the results.
As we all know the import of textfiles introdues a lot of complications for Excel how to interpret textstrings that look like dates.
Filter in fora the number of questions regarding import, texttocolumns, importwizard, etc.
It would be nice if the US would confirm to the (decimal) ISO-standards.