Calculating Date in VBA

[Taurus]

I would like to calculate the equipment's life by using start date and
failure date.
How would i programmatically write the syntax in excel?
for example - start date : 12 - March -05
failure date: 02 - April - 06
I taught using failure date minus - start date = Equipment Life
programmatically but it doesn't work.
Could anyone help me here? Thank you

 

[Ken Johnson]

Hi Taurus,
VBA has a DateDiff method that you might be able to use. The example
straight from Help is...

Public Sub DateDiff_eg()
Dim TheDate As Date
Dim Msg
TheDate = InputBox("Enter a date")
Msg = "Days from today: " & DateDiff("d", Now, TheDate)
MsgBox Msg
End Sub

Applying this to your situation could be something like...

Public Sub EquipLife()
Dim StartDate As Date, FailDate As Date
Dim EquipLife As Single
Dim Msg
StartDate = InputBox("Enter Start Date")
FailDate = InputBox("Enter Fail Date")
Msg = "Equipment life = " & DateDiff("d", _
StartDate, FailDate) & " days"
MsgBox Msg
End Sub

If you prefer the equipment life in weeks or months use "w" or "m"
instead of "d" and of course change the " days" string to suit.

Look up DateDiff Function in VBA Help for the full story.

Ken Johnson

 

[JE McGimpsey]

I would like to calculate the equipment's life by using start date and
failure date.
How would i programmatically write the syntax in excel?
for example - start date : 12 - March -05
failure date: 02 - April - 06
I taught using failure date minus - start date = Equipment Life
programmatically but it doesn't work.
Could anyone help me here? Thank you

What do you mean by "doesn't work"?

Dates in VBA are integer offsets from 30 December 1899, so subtracting
one date from another will return the equipment life in days.

MsgBox "Equipment Life: " & Date2 - Date1 & " days."

The DateDiff method can be used to get months, weeks, or years.