If between dates

[pickslides]

Hi I have attached a workbook. In which I am adding either 10 or 11 hrs to a date/time given its between certain dates.

In this I am trying to change column B given the rule in column C.

Columns D and E shows how the actual result differs from the desired result.

I think this should be pretty straight forward and must be a date format issue.

MQ

[bebo021999]

Hrs to Added:
D4:
=SUMPRODUCT(($G$11:$G$15<=B4+0)*($H$11:$H$15>=B4+0))+10
Hrs added to date:
C4:
=B4+D4/24
This returns your desired results in E column

[bebo021999]

Follow your statement:"If Between columns J and K add 10 hours, else add 11 hours"
Hrs to Added:
D4:
=11-SUMPRODUCT(($G$11:$G$15<=B4+0)*($H$11:$H$15>=B4+0))

[pickslides]

B/C i'm adding hrs I went for

Please Login or Register  to view this content.

seems to give me the result.