Time/Date overlaps calculation broken by change in precedent column

Joined
May 10, 2017
Messages
1
Hi All

In the spreadsheet below the Conflict column used to correctly calculate whether or not there was clash/overlap of dates/times. However when I changed the R [Proposed Finish Time] from just entering the time manually to the calculation shown below (which adds 1 hour to the proposed start time), it stopped working and I am not sure why and if and/or how it can be fixed?

1583764887399.png


The O [Date Proposed] column is just a date column.
The Q [Proposed Start Time] column is just a time column.
The R [Proposed Finish Time] column has the following formula: =IF([Proposed Start Time]="","",[@[Proposed Start Time]]+(1/24))
The AG [Conflict] column has the following formula: =IF(O2="","",SUMPRODUCT(--((O2+R2<=[Date Proposed]+[Proposed Start Time])+(O2+Q2>=[Date Proposed]+[Proposed Finish Time])=0))<>1)

Thanks in advance for any help.
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Forum statistics

Threads
1,223,246
Messages
6,170,996
Members
452,373
Latest member
TimReeks

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top