Solved: Code not summing properly

[Hoopsah]

Hi

I had help with this code some tme ago, but it seems to no longer sum correctly.

I input details for an absence, and it should work out the networkdays and update the absence workshhet tab in 3 diffeerent columns, once the macro under the "Submit Details" button is pressed.

1 column shows the total absence the other 2 are broken down by week and month - but the figures do not add up anymore (????)

I have attached a copy of the sheet with some data already input to show the difference in totals.

Any help greatly appreciated.

[Bob Phillips]

Gerry,

Can you direct me where to look. There are lots of #VALUE errors, presumanly because of the liked workbook. If I add an entry on the form, where will the details go?

Maybe give an example of values and tell me where the wrong values are recorded.

[Hoopsah]

Hi Bob,

thanks for having a look at this. I have amended the spreadsheet and broken the links so hopefully it will open for you now.

When I input the data on tab "Create new record" it should update tab "Absence Worksheet" column F - this works fine.

Column L should have a breakdown by month and column S should be a breakdown by week.

As you can see from the data entered, F has a sum of 94, L a sum of 51 and S a sum of 19.

These 3 columns should all be equal.

Cheers bob

Gerry

[Bob Phillips]

Try replacing these two functions Gerry

[vba]

Private Function CalculateMonthDays(ByVal StartDate As Date, _
ByVal EndDate As Date, _
ByVal FromDate As Date, _
ByVal ToDate As Date) As Long

If (StartDate <= FromDate And EndDate >= FromDate) Or _
(StartDate <= ToDate And EndDate >= ToDate) Or _
(EndDate >= FromDate And EndDate <= ToDate) Then

If FromDate > StartDate Then StartDate = FromDate
If ToDate < EndDate Then EndDate = ToDate
CalculateMonthDays = wsAbsence.Evaluate("NETWORKDAYS(" & CLng(StartDate) & "," & CLng(EndDate) & ")")
End If

End Function

Private Function CalculateWeekDays(ByVal StartDate As Date, _
ByVal EndDate As Date, _
ByVal FromDate As Date, _
ByVal ToDate As Date) As Long

If (StartDate <= FromDate And EndDate >= FromDate) Or _
(StartDate <= ToDate And EndDate >= ToDate) Or _
(EndDate >= FromDate And EndDate <= ToDate) Then

If FromDate > StartDate Then StartDate = FromDate
If ToDate < EndDate Then EndDate = ToDate
CalculateWeekDays = wsAbsence.Evaluate("NETWORKDAYS(" & CLng(StartDate) & "," & CLng(EndDate) & ")")
End If

End Function
[/vba]

[Hoopsah]

Magic!!

I am looking at the changes you made and it makes perfect sense.

Works perfectly, thanks again for your help Bob

Cheers

Gerry

[Bob Phillips]

I think the original tests didn't cater for all date spans, hopefully the new does.