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.

enter image description here

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?

enter image description here



Answers 1


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

enter image description here

The for your second table, use the following formula:

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

enter image description here

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)

enter image description here

cybernetic.nomad
cybernetic.nomad
October 10, 2019 14:22 PM

Related Questions





Select 1st day of each user id in excel

Updated December 19, 2018 07:01 AM

Text function in Excel isn't formatting year

Updated February 07, 2019 07:01 AM