• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

quarter year in excel userform textbox

Belleke

Well-Known Member
I have
Textbox1.Value = Format(Date, "dd/mm/yyyy")
and
Textbox2.Value = Format(DatePart("q", Textbox1.Value))
The result in textbox 2 is wrong, it uses the American date notation
example 02/05/2019 gives as result 1(uses 02 as month) but it should be 2 (5th month, second quarter)
How can I change that?
Thanks
 

Marc L

Excel Ninja
As well explained in the VBA inner help the DatePart VBA function expects a date and not a text !​
So use Date like for Textbox1 or just transform the text of your textbox as a date via a VBA function like CDate, DateSerial or DateValue …​
 

Belleke

Well-Known Member
Nope, tried that before I posted the question.
Can you give me an example, maybe I did something wrong
 

shrivallabha

Excel Ninja
If I use below code lines.
Code:
Me.TextBox1.Value = Format(Date, "dd/mm/yyyy")
Me.TextBox2.Value = Format(DatePart("q", TextBox1.Value))
Then I get below results respectively as per today's date i.e. 02-Jan-2020 :
TextBox1 = 02/01/2020
TextBox2 = 1

They are correct!

You need to check the underlying date value that is being passed to TextBox1 and then compare it again post formatting i.e. before passing on to TextBox2.
 

Chihiro

Excel Ninja
As Marc alluded to... This really will depend on your system date setting (or rather, what date string represents, mm/dd/yyyy, dd/mm/yyyy etc).

In general, vba forces use of US date type. Complicating coding in Userform etc when you don't use US format.

Date string is evaluated using US date type (as Textbox stores value as string...) and can give undesired result.

Best practice is to split date string by delimiter (ex: "/") and using DateSerial to convert it to date type.

Ex:
Code:
Sub Demo()
Dim x, y
Dim datestr As String
datestr = "02/01/2020"

x = Split(datestr, "/")

y = DateSerial(x(2), x(1), x(0))
Debug.Print y
y = DateSerial(x(2), x(0), x(1))
Debug.Print y
End Sub
EDIT: Better example below. 1st y will return 1, 2nd will return 2. As month/date is flipped. If datestr is directly used in DatePart function, it will return 2 as it's interpreted as May 1st, 2020.
Code:
Sub Demo()
Dim x
Dim datestr As String
datestr = "05/01/2020"

x = Split(datestr, "/")

y = DatePart("q", DateSerial(x(2), x(1), x(0)))
Debug.Print y
y = DatePart("q", DateSerial(x(2), x(0), x(1)))
Debug.Print y
End Sub
 
Last edited:

Belleke

Well-Known Member
Somewhere out of the blue, my Dutch excel is using US date notations,when i want change a cell to a date, the first choice is us date notation, it used to be the Dutch notation, what can be the reason and how can I change it back. I reinstalled Office but no luck.
 

Chihiro

Excel Ninja
File -> Options -> Language. Make sure "Match Microsoft WIndows" is selected.

However, this won't impact VBA as code will always interpret "##/##/####" string pattern as "mm/dd/yyyy" using US date type. If you use Range().Value2 you can obtain underlying date value as is and is recommended when dealing with datetime value.
 

Marc L

Excel Ninja
Nope, tried that before I posted the question.
So show at least your attempts as no issue on my local non US version …​
(As I yet wrote just replacing Textbox1.Value by Date !)
A reminder whatever the Excel version :​
VBA only understand 'text date' as US and only the point as decimal separator for any 'text value' …​
 

Belleke

Well-Known Member
I have solved it by useing a calendar form.
I thank everyone for contributing ideas.
 
Top