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 :)
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

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 & "."
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,827
Messages
5,855,877
Members
431,771
Latest member
CoryMelth

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top