Calculating Remaining Date Time Hours - Error In My Calculation/Code

[excelnube84]

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.

[excelnube84]

Is this issue because i have used WORKDAY instead of using NETWORKDAYS?

After a big of Googling it seems NETWORKDAYS is a favorite to skip weekends in calculations.