If/than formula based on dates being consecutive or not?

[lichodo24]

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!

 

[Juniad]

Can u upload a sample file with manual result ?

 

[shaikhrulez]

An Excel file with sample and your desired output will make it easy for others to answer.

 

[Peter Bartholomew]

If you can define the reset date to be Sundays, then a list of Sundays in 'dateBins' (i.e. weeks) would enable
= FREQUENCY( listDates, dateBins )
to determine how many of your list of dates occur in each week as an array.