Count forward 6 hours if outside business hours to move date +1 & count from start bu

Long subject I know let me try this again.

Let me set the area for you. Do you like a challenge?

I am creating a sheet for work. To meet certain KPI if we receive urgent works we must have them completed in 6 business hours. I wish to create a sheet that can keep track of these work orders. In this sheet I wish to be able to enter the date a work order was received and the time. From here I want excel to calculate the date and time the work order is due and display a message stating due or not due by referring to a clock and date that is in built into excel.

All seems fairly straight forward.

In A1 I have created a clock that syncs with the systems time using VB, in another cell I created the date by using =today() these two cells are my reference point, they are A1 & c1 ( i have left a gap just for look sake)

I have created the following headings:

A3 - G3 are headings that are not important for my question

from H3 to L3 the headings are:

h3: Date Received
i3: Time received
j3: Date Due
k3: Time Due
L3: Work order Due

h3 - to be inputed by the user.
i3 - to be inputed by the user.
j3 - the date the work order is due ( calculated automatically )
k3 - the time the work order is due ( calculated automatically )
L3 - a simple if statement to display "due" or "not due"


The way I would like this to work is this, the user inputs data into H3 and I3, excel reads this date and time and adds 6 hours to the time and displays it under time due. for example works entered on the 25/3/10 at 8am, excel then would display date due as 25/3/10 at 2pm. That is simple to do. But my problem is this.

Now business hours are important here, so lets pretend business hours are from 8am to 5pm.

If we receive a work order on 25/3/10 at 4pm, I want excel to count forward 6 business hours. that would be 1 business hour on the 25/3/10 and than 5 business hours on the 26/3/10, starting at the start of business hours therefore displaying the date due as 26/3/10 and time due as 1pm.

That is the line of code I cant figure out. How do i tell excel to do this?

date received = 25/3
time = 4pm
therefore date due = date received + 1 ( as the 6 business hours will take it into tomorrow)
time due =8 + ( 6 - (the amount of hours that were left in business day 1)


I also need to make sure that if its Friday at 4pm than it starts counting from 8am Monday morning.Further I will need the ability to factor in public holidays at a later stage.

I have the following code:

A1 = VB code for the clock to auto update it self by the second.
C1 =today()
L4 =IF(C1>=J4,IF(A1>=K4,"due", "not due "),"not due")

I am not an expert with excel so any help you can give would be great. Like I said its pretty

I hope all of this made sense, If you like I can post the sheet.

That was a lot quicker than I thought it would have been.

Thank you for that really appreciated.

But I now left with a new problem, I was using the clock in cell a1& c1 to keep track of the current time and date. The excel sheet performs a basic test on cell l4 which was:

=IF(C1>=K4,IF(A1>=L4,"due", "not due "),"not due")

checking to see if the current date is above the due date that was calculated.

To use the format you have given me i will need to combine the date and time into another cell and use a if statement, unless there is a quicker way around this?

In the meantime I am gong to go and try and figure it out but feel free to step in

You have saved me a considerable amount of time and for that you have my thanks. I spent a great deal of time looking into VB code for a clock that would automatically update it self because I thought that the =now() function would only calc once. I never even thought of putting it inside a if statement.

Now that I see it works this simply, there is no need for the VB code.

Thanks again, really a HUGE help.

after thinking further I have decided to reinstate my vb code. Looks like this:

under this work book:



Private Sub Workbook_Open()
UpdateTime
End Sub

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
UpdateTime
End Sub

under module 1:

Option Explicit
Public setDate As Date
Sub UpdateTime()
setDate = Now() + TimeValue("00:0:01")
Application.OnTime setDate, "TimeUp"
End Sub
Sub TimeUp()
ThisWorkbook.Worksheets("Sheet1").Range("a1") = Time
UpdateTime
End Sub

***

Now under cell Work order due col formula is:

=IF(SUM(A1,C1)>=J4,"due","not due") ( as j4 is now where the formula you gave me sites.)

The advantage of this is that it is always updating without user input, its "live"

Its not perfectly neat for following all the rules but it gets the job done and now I can focus on my work rather than managing times.

Cheers