Calculating the no. of days between two dates on a user form

Mlbblue

New Member
Joined
Oct 8, 2006
Messages
20
This is probably very easy but I'm totally stuck on it...

I've got a user form with three fields, the first two fields are for date A and date B which is entered by the user.

Upon exit of field B (after date B inputted) I want field C to show the number of days between dates A and B in fields A and B.

Afterwards the user will be submitting these to a sheet (no problems with that)

I'm totally stuck on how to automatically make field C show the days between dates A and B.

Is there anyone out there that can help me?

:biggrin:

Any help is appreciated!

Cheers

Mark
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

kkknie

Well-known Member
Joined
Apr 29, 2002
Messages
677
In your userform, enter the following (changing TextBox1,2,3 to whatever names you have given them).
Code:
Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   Call DoDateDiff
End Sub

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   Call DoDateDiff
End Sub

Sub DoDateDiff()

   Dim tmpD1 As String
   Dim tmpD2 As String
   Dim D1 As Date
   Dim D2 As Date
   Dim rDelta As Single
   
   tmpD1 = Trim(TextBox1.Text) 'Get rid of extra spaces
   tmpD2 = Trim(TextBox2.Text) 'Get rid of extra spaces
   
   If tmpD1 = "" Then Exit Sub
   If tmpD2 = "" Then Exit Sub
   
   If Not IsDate(tmpD1) Then Exit Sub
   If Not IsDate(tmpD2) Then Exit Sub
   
   D1 = CDate(tmpD1)
   D2 = CDate(tmpD2)
   
   rDelta = D2 - D1
   
   TextBox3.Text = CLng(rDelta)
   
End Sub
When the user exits either the first or second box, the DoDateDiff routine will run and calculate a date if it is possible to calculate (i.e. the inputs are valid dates).

Hope this helps,

K
 

Mlbblue

New Member
Joined
Oct 8, 2006
Messages
20
Thanks K, that's exactly what I was looking for. You're a life saver!

Cheers

Mark
 
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,834
Messages
5,855,898
Members
431,772
Latest member
dannyboi1

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