Hi guys,

I have a spreadsheet that is automatically populated from my DB when a customer places an order.

Sheet 1

Column D is populated with the order date e.g. - 01/12/2012 03:05:01

In Column C, i have the status of the importance of the order e.g. 1,2,3,4

Sheet 2

Col A Col B Col C Col D
Importance Time Start End
1 01:00 09:00AM 05:00PM
2 04:00
3 20:00
4 40:00
5 9999:00

Sheet 1

In Column H, i am attempting to calculate the date of when i need to process the order by adding the value in Column C to Column D within the hours of 9am and 5pm Monday to Friday.

My code:

With ActiveSheet.UsedRange
    .Value = .Value
    End With

    Dim LastRow As Long, LastCol As Long, i As Long
    LastRow = Cells.Find("*", , , , xlByRows, xlPrevious).Row
    LastCol = Cells.Find("*", , , , xlByColumns, xlPrevious).Column
    
    '' Apply formula to Process Date/Time - Column H
    Range("H2").Resize(LastRow - 1) = "=WORKDAY(D2,CEILING((Q2+MOD(D2,1)-Sheet2!C$2)/(Sheet2!D$2-Sheet2!C$2),1)-1)+MOD(D2,1)+Q2-CEILING(MOD(D2,1)+Q2-Sheet2!C$2,Sheet2!D$2-Sheet2!C$2)+Sheet2!D$2-Sheet2!C$2"
I thought this formula was correct, until i received an order yesterday

Col D - 01/12/2012 03:05:01
Col C - 3 (sheet 2 says 3 = 20:00 hours)
Col H - 03/12/2012 15:05

Please can anyone suggest where the error in my calculation is?

It appears my code is incorporating weekends

Thank you.