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
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

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
 

Watch MrExcel Video

Forum statistics

Threads
1,113,793
Messages
5,544,315
Members
410,602
Latest member
lidovi
Top