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

'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