calculate date differences and give popup box

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Having a bit of trouble with this. What Im trying to do is calculate the
amount of working days between 2 dates, and then provide the resulting value
in a msgbox along with other text.

At the moment I have the formula working, but it is set as the "initial
value" of another field.

The formula is:

DateDiff("d", [First.Day], [Last.Day])+2 - ((DateDiff("w", [First.Day],
[Last.Day]) * 2) + 1)

This formula works perfectly when the user only inputs actual working days,
it's not accurate if they put in the date of a weekend.

Now because it's set as the initial value of another field, it automatically
calculates when either field is changed. What I want is the result of the
formula to appear in a msgbox instead of a field as a value, and anytime
either input field is updated the msgbox should come up. Is it possible?

Thanks
 
I've now solved this but without using the msgbox, code for anyone else
looking to do the same thing:

Function calculate_Click()
leave()
End Function

Sub leave()

dStartDate = Item.UserProperties("First.Date").Value
dEndDate = Item.UserProperties("Last.Date").Value

Dim sDayDifference, tempDay, dayTally

sDayDifference = DateDiff("d", dStartDate, dEndDate) + 1
dayTally = 0
for i = 0 To (sDayDifference-1)
tempDay = FormatDateTime(dateAdd("d", i, dStartDate), 2)
If (WeekDay(tempDay) = 7) Or (Weekday(tempDay) = 1) Then
dayTally = dayTally + 1
End If
next
Item.UserProperties("LeaveDaysEntry").Value = sDayDifference - dayTally -
Item.UserProperties("PublicHolsEntry").Value

End Sub
 
Back
Top