[RESOLVED] [Excel] How to subtract Date from Userform

[allankevin]

Hi Guys,

How can is subract 2 dates in a userform VBA? Can anyone help me?

Thanks and Regards,
Allan

[Siddharth Rout]

Use The DateDiff function

Function DiffInDates(pDate1 As Date, pDate2 As Date) As Long
     DiffInDates = DateDiff("d", pDate1, pDate2)
End Function

Sub DateDifference()
    'This will give you 11
    MsgBox DiffInDates(#12/13/2008#, #12/24/2008#)
End Sub

[allankevin]

Hi Kool,

Your code seems to work.. but i dont know how to apply in my userform..

Want i want to do is when I input a date in textbox1 10/28/08 and I input again 10/20/08 in textbox2 then the answer should be in textbox3 8 after I Exit on textbox2..

[Siddharth Rout]

TextBox3.Text = DiffInDates(TextBox1.Text, TextBox2.Text)

[allankevin]

Thanks Kool! It works..

[allankevin]

Hi Kool,

What if I use Date Picker and netWorkdays?

[Siddharth Rout]

Date Picker

same goes there...

Replace the values inside DiffInDates()

netWorkdays

What about this?

[allankevin]

Ive already get the datepicker..networkdays function..if there is an holiday or saturday and sunday it will not count that as regualar day.. somthing like that..is it posible?

[Siddharth Rout]

Do you mean that when you insert dates in two textboxes then you want the difference between two dates which will exclude an holiday or saturday and sunday ???

[allankevin]

Hi Kool,

hmmm..something like that.. But I will use the NETWORKDAYS function NETWORKDAYS(start_date,end_date,holidays)... and I will apply the DateDiff on the DatePicker.. So in the computation it will not include holidays..

[Siddharth Rout]

There are few complications here...

1) NETWORKDAYS will not work till the time Analysis Toolpack is installed
2) The syntax is NETWORKDAYS(start_date,end_date,holidays) where Holidays is an optional range of one or more dates to exclude from the working calendar, such as state and federal holidays and floating holidays. The list can be either a range of cells that contains the dates or an array constant (array: Used to build single formulas that produce multiple results or that operate on a group of arguments that are arranged in rows and columns. An array range shares a common formula; an array constant is a group of constants used as an argument.) of the serial numbers that represent the dates. The challenge that I see here is that you will have to use the Excel workbook for this or then use an array.

This is not easy and will be time consuming...

Let me know if you are still interested...

[allankevin]

Hi Kool,

It seems complicated but im still interested.. Im fine in using a Excel workbook.. I already add the Analysis Toolpack and Analysis Toolpack for VBA In the worksheet but I cant find the NETWORKDAYS in VBA userform I tried looking in the Add-Ins but it is empty..

[allankevin]

Private Sub DTPicker2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim Holidays As Range

Set Holidays = Sheet1.[A2:A10]

Me.txt2.Value = _
    Application.WorksheetFunction.Networkdays(DTPicker1.Value, DTPicker2.Value, Holidays)

End Sub

I've come out to this code.. but i dont think there is a NETWROKDAYS function in my vb.. How can i install the analysistool pack in VBA..

[allankevin]

Yehey! I got it! atpvbaen.xls and funcres is the key! Thanks Kool!

Function DaysDiff(StartDay As Date, EndDay As Date, Hols As Range)
DaysDiff = networkdays(StartDay, EndDay, Hols)
End Function

Private Sub DTPicker2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim Holidays As Range

Set Holidays = Sheet1.[A2:A3]
txt2 = DaysDiff(DTPicker2.Value, DTPicker1.Value, Holidays)
End Sub

[Siddharth Rout]

sorry just came in...

yup... atpvbaen.xls is the key

[allankevin]

Thanks again.. I got my idea in your datediff almost the same..just need to set an array for the list of holidays in the worksheet.. I thought i can use the worksheetfunction.networkdays..