Subtracting Dates in TextBoxes

[John Wilson]

Anyone have an idea how to do the following:

On a UserForm.
TextBox1 = 9/20/04
TextBox2 = 9/28/04

Once the date is entered into TextBox2, TextBox 3 should populate with:
"1 week & 2 days"

Thanks,
John

 

[Doug Glancy]

John,

Here's a start. It doesn't check to make sure date1 is earlier than date2.
It also starts filling in box3 before the second date is completely entered
because I used a Change event. An Exit event would only work if you left
one of the textboxes which seems worse to me. Anyways, enter this code into
the UserForm, or just wait for one of the many really smart people in this
group to follow up with a more complete answer:

Private Sub TextBox1_Change()
Call date_change
End Sub

Private Sub TextBox2_Change()
Call date_change
End Sub

Sub date_change()
Dim dif As Long

If IsDate(Me.TextBox1) And IsDate(Me.TextBox2) Then
dif = CDate(Me.TextBox2) - CDate(Me.TextBox1) + 1
Me.TextBox3 = Int((dif) / 7) & " weeks " _
& dif Mod 7 & " days"
End If
End Sub

hth,

Doug Glancy

 

[Bob Phillips]

Textbox3.Text = CDate(Textbox2.Text) - CDate(Textbox1.Text)

will give you 8. You can work out how to turn that into weeks and days,
especially as you get 9?

 

[John Wilson]

Thanks guys.....

CDate was the "key" that I was missing.
Brain fart, I guess?

Thanks again,
John