Date Calculations
Posted by John Franklin on November 15, 2001 6:30 PM
We process Disability Claims. We need to find the specific days within a given period that an employee should be paid. We want to be able to enter the first and last dates of payment and have a formula in Excel that can tell us the number of days the employee should be paid within those two dates depending upon what days of the week they normally work. For example, if an employee works Mon, Wed, Fri and the period within the two dates adds up to 3 weeks the program should be able to tell us that we need to pay this individual for nine days. The reason we ask is because we process hundreds of claims a week. Please help.
Posted by Damon Ostrander on November 15, 2001 10:44 PM
Hi John,
Here's a solution to your problem. Simply install this VBA macro function that calculates the number of qualifying days between two dates. To use this function on a worksheet to find for example the number of working days between 11/1/2001 and 11/21/2001 given that working days are Mon, Wed, and Fri:
=WkgDays(DATEVALUE("11/1/2001"),DATEVALUE("11/21/2001"),135)
or, if the start date is in cell A4 and end date is in cell B4, and the qualifier 135 is in cell C4, then it would be
=WkgDays(A4,B4,C4)
Here's the code:
Function WkgDays(StartDate As Date, EndDate As Date, _
QualifyingDays As Long) As Integer
' Returns the number of qualifying days between (and including)
' StartDate and EndDate. Qualifying days are whole numbers where
' each digit represents a day of the week that should be counted,
' with Monday=1, Tuesday=2, etc. For example, all Mondays, Tuesdays
' and Thursdays are to be counted between the two dates, set
' QualifyingDays = 124.
Dim iDate As Date
Dim strQdays As String
strQdays = CStr(QualifyingDays)
WkgDays = 0
For iDate = StartDate To EndDate
If strQdays Like "*" & CStr(Weekday(iDate, vbMonday)) & "*" Then
WkgDays = WkgDays + 1
End If
Next iDate
End Function
If you don't know how to install a VBA macro, it's really
quite easy. Just follow these steps:
1) Go to the Visual Basic Editor (VBE). Do this from Tools ->
Macro -> Visual Basic Editor
2) In the VBE create a new Macro Module: Insert -> Module.
An empty code window pane will appear.
3) Paste the code into this window. The macro or function is
now available for use from Excel. If it is a Function type
macro you can immediately use it as an Excel function. If
it is a Sub (subroutine) type macro you can run it from
the Excel Tools -> Macro menu.
Happy computing.
Damon
