Hi guys,

Having a small issue with my spreadsheet…

hourstable.jpg

This issue I am facing is trying to calculate the Expected Close Date (column H) and Time Remaining (column I)

For column H, the expected close date would be D2 + Q2 whilst taking into consideration the Start Time / End Time.

So, in the example above, Open Date = 11/09/2012 05:57, I would expect Expected Close Date to be = 11/09/2012 13:00 (as the order was placed outside of office hours, the start time would be 9:00am + 4hours (Q2) = 13:00.

However, my spreadsheet is displaying: 11/09/2012 09:57

Current formula:


=WORKDAY(D2,CEILING((Q2+MOD(D2,1)-N$2)/(O$2-N$2),1)-1)+MOD(D2,1)+Q2-CEILING(MOD(D2,1)+Q2-N$2,O$2-N$2)+O$2-N$2
Any help is highly appreciated.

Thanks