problem function date add into datepicker on userform

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,429
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
hi
i have problem add years and months days is ok i have textbox1= days textbox2= months textbox3= years when i choose in datepicker1= date then add months an years to datepicker2
this is my simple code
VBA Code:
Private Sub CommandButton2_Click()
    datepicker2 = DateAdd("d", textbox1.Text, datepicker1)
     datepicker2 = DateAdd("M", textbox2.Text, datepicker1)
      datepicker2 = DateAdd("YYYY", textbox3.Text, datepicker1)
End Sub


1.JPG
 
please ignore post# 29 i would also fix datediff it doesn't show the data as in image maybe this clear the matter this is what i would
5.JPG
 
Upvote 0

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
You want to identify how many days, months and years there are between dates (fisrt date - end date).

In your example: from (first date) 01/01/2010 to (end date) 03/06/2021 they are: 5 days 2 months 11 years. That you want?

Then, in a second step, do you want to add 5 days, 2 months, 11 years to an initial date to obtain the new date?

If not and you already have a solution then forget my comment.
 
Upvote 0
yes all of say that what i would about this
from (first date) 01/01/2010 to (end date) 03/06/2021 they are: 5 days 2 months 11 years. That you want?

Then, in a second step, do you want to add 5 days, 2 months, 11 years to an initial date to obtain the new date?

with considering the datediff not gives me as what i want it gives me textbox3=440 textbox4=14 textbox5=1
it supposes extbox3=15 textbox4=2 textbox5=1
"
 
Last edited:
Upvote 0
So I still don't understand what you want.
Where does your process start and where do you want to end?
Forget the excel functions, your textboxes and your datepicker.
With simple words, what data do you have and what is the final objective?
 
Upvote 0
i will give you two image 1 it gives me wrong and 2 this is what i'm looking for when press account old gives me wrong days,months,years
and when choose date from enter date and press account date it gives me wrong in a new date
1.JPG




5.JPG

and this is my code about account old
VBA Code:
Private Sub CommandButton1_Click()
Dim strD As Date
Dim endD As Date
Dim diff, DIFF2, DIFF3 As Integer
strD = TextBox1.Value
endD = TextBox2.Value
diff = DateDiff("d", strD, endD)
diff1 = DateDiff("M", strD, endD)
DIFF2 = DateDiff("yyyy", strD, endD)
Me.TextBox3.Value = diff
Me.TextBox4.Value = diff1
Me.TextBox5.Value = DIFF2
End sub
 
Upvote 0
But I don't know where to start capturing and then what is the result you want.
Start at the beginning.
1. Userform.show
2. Your textboxes are empty.
3. What do you have in dtpicker1 and what do you have in dtpicker2
4. What do you capture in textbox1?
5. What do you capture in the textbox2?
6. What do you want in textbox3, 4 and 5?
7.And so on.

Note: What I put is an example of a few steps. But you must put step by step what you write and what you want as a result, but please, do it step by step and without VBA code.
 
Upvote 0
1- useform is empty
1.JPG



2- fill textbox1,textbox2


2.JPG



3- press account old to get textbox3 =days, textbox4=months,texbox5= years



3.JPG




4- i choose date from enter date =dtpicker1 and press account date get a new date in dtpicker2


4.JPG



the data should this textbox3=15 textbox4=2 year=1
 
Last edited:
Upvote 0
3- press account old to get textbox3 =days, textbox4=months,texbox5= years
3.JPG
Stop, stop, stop
In step 3.
The results: days=439, months=14 and years=1
Are they the results you want, or what results do you want to see in each textbox(3,4,5)?
 
Upvote 0
Any system for counting years and months will inevitably have differences due to the variable length of months.
In my opinion, it is impossible to get a completely "accurate" result by measuring date differences in this way.
I recommend you only get a textbox (3) to get the total days.
Delete textbox 4 and 5, do the following:

1590424418214.png


This would be the code for the 2 buttons:
VBA Code:
Private Sub CommandButton1_Click()
'Account OLD
  datepicker2 = datepicker2 + (CDate(TextBox2.Value) - CDate(TextBox1.Value))
End Sub

Private Sub CommandButton2_Click()
'Account Date
  TextBox3.Value = CDate(TextBox2.Value) - CDate(TextBox1.Value)
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,517
Messages
6,114,085
Members
448,548
Latest member
harryls

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
Back
Top