Home / Functions / Calculate Number of Days in another Date Range

Calculate Number of Days in another Date Range

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.

Function dblDaysInPeriod(dteHUDGrantStartDate As Date, dteHUDGrantEndDate As Date, _
                        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

'Assumptions
'       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
        Exit Function
    End If
 
    If dteGrantStartDate <= dteHUDGrantStartDate And _
    dteGrantEndDate <= dteHUDGrantEndDate And _
    dteGrantEndDate >= dteHUDGrantStartDate Then
        dblDaysInPeriod = (dteGrantEndDate - dteHUDGrantStartDate) + 1
        Exit Function
    End If
 
    If dteGrantStartDate >= dteHUDGrantStartDate And _
    dteGrantStartDate <= dteHUDGrantEndDate And _
    dteGrantEndDate > dteHUDGrantEndDate Then
        dblDaysInPeriod = (dteHUDGrantEndDate - dteGrantStartDate) + 1
        Exit Function
    End If
 
    If dteGrantStartDate < dteHUDGrantStartDate And _
    dteGrantEndDate > dteHUDGrantEndDate Then
        dblDaysInPeriod = (dteHUDGrantEndDate - dteHUDGrantStartDate) + 1
        Exit Function
    End If
 
End Function

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

About Excel Help Desk

Check Also

Retain Trailing Zeros in a Text String

For this request we needed to ensure that a trailing zero was always displayed for a calculated text string. Through the use of an additional function the string included trailing zero's