The ExcelHelpDesk Support team received the following request for help
Problem – Calculate the Number of Days in another Date Range
I need help calculating the number of days falling within a specific time period. For example, for the period January 1, 2011 to December 31, 2012, I’m looking to calculate the number of days I can count when the X factor is March 15 to January 15, 2013.
For this problem we received a sample workbook, that explained where help was needed. See the screen shot below of the spreadsheet and an explaination of what was required. You can see that we have a Worksheet that is calculating the Number of Days (in Column F) that a particular Funding Source is available within a given Grant Year. The Grant Year is set in the cells F2 and G2. Once the Number of Days is calculated that is then used to calculate the amount from the Funding Source that is to be allocated to this Grant Year.
Step 1 – Calculate Number of Days
To acheive the required calculation we developed a custom function that would calculate the Number of Days using the Grant Start and End Dates and the Funding Source Start and End Dates. Before developing this function the various outcomes needed to be identified. See below for the possible scenarios for the date ranges that could be included and that needed to be allowed for in the custom function
With these possible options a custom function was then developed for the worksheet. The VBA script for the function is shown below.
strGrantStartDate As String, strGrantEndDate As String) As Double
'This function will calculate the number of days that a given Grant is contained within
'a Grant period
' the Grant Start Date and Grant End Date cannot be the same date
' the Grant Start Date is always less than the Grant End Date
Dim dteGrantStartDate As Date
Dim dteGrantEndDate As Date
dblDaysInPeriod = 0
If Not IsDate(strGrantStartDate) Then Exit Function
If Not IsDate(strGrantEndDate) Then Exit Function
dteGrantStartDate = CDate(strGrantStartDate)
dteGrantEndDate = CDate(strGrantEndDate)
If dteGrantEndDate <= dteGrantStartDate Then Exit Function If dteGrantStartDate >= dteHUDGrantStartDate And _
dteGrantEndDate <= dteHUDGrantEndDate Then
dblDaysInPeriod = (dteGrantEndDate - dteGrantStartDate) + 1
If dteGrantStartDate <= dteHUDGrantStartDate And _
dteGrantEndDate <= dteHUDGrantEndDate And _ dteGrantEndDate >= dteHUDGrantStartDate Then
dblDaysInPeriod = (dteGrantEndDate - dteHUDGrantStartDate) + 1
If dteGrantStartDate >= dteHUDGrantStartDate And _
dteGrantStartDate <= dteHUDGrantEndDate And _ dteGrantEndDate > dteHUDGrantEndDate Then
dblDaysInPeriod = (dteHUDGrantEndDate - dteGrantStartDate) + 1
If dteGrantStartDate < dteHUDGrantStartDate And _ dteGrantEndDate > dteHUDGrantEndDate Then
dblDaysInPeriod = (dteHUDGrantEndDate - dteHUDGrantStartDate) + 1
Step 2 – Include Custom Function to Worksheet
Now that we have the Custom Function developed and included to the Workbook we can include that to the cells in Column F to then calculate the Number of Days. For each cell in Column F the following is included to essentially call the Custom Function.
Where the values “HUDGrantYearStartDate” and “HUDGrantYearEndDate” are named ranges for the cells F2 and G2.
Step 3 – Update Other Cells to Use Calculated Number of Days
Lastly we need to update the calculation for the Amount of the Funding Source that is to be included for the given Grant Year. For this the formulae uses the Number of Days and the Grant Start and End Dates to determine how much of the total funding is applicable to the current Grant Year.
The formula to use in each cell for Column H is =C5/(E5-D5+1)*F5.
Finished – Sample Workbook including Custom Formula
Here is a link to a Sample Workbook that includes the Custom Formula discussed above as well as the other formula required to enable the calculation.
|Download Sample Workbook|
If you have a question on this post for the Excel Help Desk team or have something you would like to share on this topic then please leave a comment