# Calculate Number of Days under Contract from List of Contracts

by cpage   Last Updated October 09, 2019 15:01 PM - source

I have two sheets in an Excel workbook. The first is a list of contracts for workers A, B, and C and the contracted revenue rate per day.

In the other sheet, I would like to calculate the number of days each worker (A, B, C, etc.) (ignore the Revenues in the image) is under contract during the given period. What formula would I use to do this?

Tags :

Add columns with the period date on the right of your first table. Then under each period's end date, insert eh following formula:

``````=MAX(IF(\$C3>G\$2,G\$2,\$C3)-IF(\$B3<F\$2,F\$2,\$B3),0)
``````

And populate to fill the table

The for your second table, use the following formula:

``````=SUMIF(\$A3:\$A6,\$A13,G3:G6)
``````

If you want to have the number of days times the rate calculated, use the following formula in the second table:

``````=SUMPRODUCT(--(\$A\$3:\$A\$6=\$A13),\$D\$3:\$D\$6,G\$3:G\$6)
``````