I need a formula that multiplies the number in 1 cell based on the range of dates in another. For example, the dates are 2/17 - 2/19, which is 3 days, so I would multiply my cell value by 3. However, there are a few exceptions to this rule...
For this formula, we can only go up to 3 consecutive days. So if it's 4, 5, 6, or 7 consecutive days, we still only want to multiply by 3.
This needs to reset every week. So if it was 2/17 - 2/27... its 11 days, but because of the max of 3, and it going over 2 different weeks, we would want to multiply by 6.
For NON consecutive days, we just want to multiply by the individual days (while still keeping the rule of a max of 3 per week). So if it was 2/17, 2/19 and 2/27, it would be 3. If it was 2/17 - 2/20 and 2/26 it would be 4.
I know this is very strange... thanks in advance for your help!
For this formula, we can only go up to 3 consecutive days. So if it's 4, 5, 6, or 7 consecutive days, we still only want to multiply by 3.
This needs to reset every week. So if it was 2/17 - 2/27... its 11 days, but because of the max of 3, and it going over 2 different weeks, we would want to multiply by 6.
For NON consecutive days, we just want to multiply by the individual days (while still keeping the rule of a max of 3 per week). So if it was 2/17, 2/19 and 2/27, it would be 3. If it was 2/17 - 2/20 and 2/26 it would be 4.
I know this is very strange... thanks in advance for your help!