Sytaxis with the dates

Buskopan

Board Regular
Joined
Aug 4, 2014
Messages
54
Hello, I'm still new to vba

Question.

On user form I got textboxes

tb_date_from = Indicates date person starts overtime in format mm/dd/yyyy
tb_date_till = indicates date person finished work in same format
tb_remarks

How I can make texbox remarks value to look like this ?

"Worked X days overtime. Dates D1 to D2"

Where X = tb_date_till - tb_date_from

D1 date format is d
D2 date format is dd of mm yyyy

As Example

Worked 2 days overtime. Dates 12 till 13 of April 2014

I think I can do that but I will spend the ages. Please help :)
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
The syntax is:

Code:
tb_remarks.Text = "Worked " & DateValue(tb_date_till) - DateValue(tb_date_from) & " days overtime. Dates " & tb_date_from.Text & " to " & tb_date_till.Text & "."
 

Buskopan

Board Regular
Joined
Aug 4, 2014
Messages
54
Thank you for promt reply!

So code
Code:
Me.tb_remarks.Text = "Worked " & DateValue(Me.tb_tilldate) - DateValue(Me.tb_fromdate) & " days overtime. Dates " & Me.tb_fromdate.Text & " to " & Me.tb_tilldate.Text & "."

Resulst as

Worked 31 days overtime. Dates 9/3/2014 to 9/4/2014.

Why it is 31 instead of 2 days ?
And how to make look like ....Dates from 03 to 04 of September 2014 ?

The syntax is:
Sorry for my poor English :)
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Why are you using US style dates? The DateValue function respects your Regional settings, so what you entered would be 9th Mar to 9th Apr.
 

Buskopan

Board Regular
Joined
Aug 4, 2014
Messages
54

ADVERTISEMENT

well to be honest I'm using calendar class which I took today from here

and by default it gives me that date format to my from and till date texboxes , you are right i need to find way to show the proper format
 

Buskopan

Board Regular
Joined
Aug 4, 2014
Messages
54

ADVERTISEMENT

Code:
Private Sub Calendar1_DblClick()
    PAR_Create.tb_fromdate = Calendar1.Value
    Unload Me
End Sub
 

Buskopan

Board Regular
Joined
Aug 4, 2014
Messages
54
Thank you very much! I was close.

And finaly did the way I want it to look
Code:
Dim nice_date As Date
nise_Date = Format(Me.tb_tilldate, "Long Date")


    Me.tb_remarks.Text = "Worked " & DateValue(Me.tb_tilldate) - DateValue(Me.tb_fromdate) & " days overtime. Dates " & Day(Me.tb_fromdate.Text) & " till " & nise_Date & "."
 

Watch MrExcel Video

Forum statistics

Threads
1,108,810
Messages
5,525,010
Members
409,615
Latest member
papaluigi94

This Week's Hot Topics

Top