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

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?

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)
```

- ServerfaultXchanger
- SuperuserXchanger
- UbuntuXchanger
- WebappsXchanger
- WebmastersXchanger
- ProgrammersXchanger
- DbaXchanger
- DrupalXchanger
- WordpressXchanger
- MagentoXchanger
- JoomlaXchanger
- AndroidXchanger
- AppleXchanger
- GameXchanger
- GamingXchanger
- BlenderXchanger
- UxXchanger
- CookingXchanger
- PhotoXchanger
- StatsXchanger
- MathXchanger
- DiyXchanger
- GisXchanger
- TexXchanger
- MetaXchanger
- ElectronicsXchanger
- StackoverflowXchanger
- BitcoinXchanger
- EthereumXcanger