Calculate ETA time

The current challenge is to find an expected target time when a start date is given. There are different levels of priority requests and a target time for completion. for e.g P1 might have 2 hour, something with less with 2 days and so on .. 10 days, 15 days..

The challenge is that we need to consider only business hours and exclude holidays and weekends.

I almost arrived at a formula but feel its quite lenghty, and time consuming ..

Do While totalMins < targetMins
' Calculate within end time and date if its within business hours
 If TimeValue(arrvDate) < #4:59:59 PM# Then
    If TimeValue(arrvDate) < #8:00:00 AM# Then
        Do While TimeValue(arrvDate) < #7:59:59 AM#
            arrvDate = arrvDate + 0.00069444
        Loop
    End If
    Do While TimeValue(arrvDate) < #4:59:59 PM#
        arrvDate = arrvDate + 0.00069444
        totalMins = totalMins + 1
        If totalMins = targetMins Then
            Exit Do
        End If
    Loop
 Else
 ' If greater then business hours
    Do While TimeValue(arrvDate) > #7:59:59 AM#
            arrvDate = arrvDate + 0.00069444
    Loop
    arrvDate = dhAddWorkDaysA(1, arrvDate)
 End If
Loop
etaDate = arrvDate

The input will be start date and a target mins/hrs/days to complete. The result should give me a ETA.
LVL 7
JonybrvAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DonkeyOteCommented:
You can do this with formulae if so desired - attached is a working example

Col E represents your ETA equiv. based on start (A) and priority (C) - the time added is based on C and lookup value... Holidays are accounted for per the named range and working hours specified in H2:I2.

(note attached utilises the likes of NETWORKDAYS and WORKDAY and thus warrants activation of Analysis ToolPak Add-In pre XL2007 - you can circumvent the requirement if nec.)

A UDF is obviously a little cleaner on the worksheet but the native function would probably be a little easier to audit, manage and should calculate a little quicker.
445824-rrolfze1.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JonybrvAuthor Commented:
DonkeyOte:

Thanks for the quick reply. Can you explain what it exactly does.
0
DonkeyOteCommented:
Sorry, the file is something I put together on another forum for another question re: SLA turnaround.

For the sake of your question - you can disregard Columns B & F - these were relevant to the other question wherein a manually inserted end datetime was to be compared to the estimated SLA End time to see if work was completed within permitted window (shown in F).
They have no bearing on the remainder of the sheet which is all relevant to you.

To explain (hopefully better than last time)

Yellow cells = manual entry

Column A - this is your start datetime - entered manually.

Column C - this would be your "priority" flag - entered manually along with the start time.
Each flag has an associated duration as seen in Col K:L (referred to via Dynamic Named Range _SLA)

H2:I2 - these represent your working hours [for working days] - entered manually

K:L we've discussed (ie: Priority duration table - the amount of time assigned to each Priority)

N: is a listing of public holidays - entered manually
(utilised in formulae via another Dynamic Named Range: _Holidays - ie the DNR adapts as you add / remove dates to Col N)

Cols D:E are where the calculations take place regards establishing end time based on

a) start time
b) priority
c) working hours
d) public holidays

Col D is used to adjust the start time to a valid working start datetime should A fall outside of working hours

Col E is used to calculate the end time based on D and all other factors - this is your "ETA" calculation.
0
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

DonkeyOteCommented:
Perhaps if I walk through an example from the file it might make more sense...

A3: Sun, 27-Dec-2009 18:34

Priority Assigned is 6 - this has a duration of 80:00

Working Hours are 0700-1900 (Mon-Fri)

There are Public Holidays on 1st Jan 2010  (just an ex. remember!)

Given the Start DateTime occurs on a Sunday the Start value is adjusted in D3 to Mon, 28-Dec-2009 07:00 - ie the first valid working point after the manually inserted value and we will thus be adding 80:00 (working time) to this date time

We know that a working day constitutes 12 hours and we know that we have:

28-Dec-2009 [Mon]
29-Dec-2009 [Tue]
30-Dec-2009 [Wed]
31-Dec-2009 [Thu]

before we hit any issues - so that accounts for 48:00 leaving us with 32:00 to still account for.

01-Jan-2010 [Fri] is a public holiday so this is discounted

02-Jan-2010 [Sat]
03-Jan-2010 [Sun]

are both discounted given they are weekend dates so we then get to:

04-Jan-2010 [Mon]
05-Jan-2010 [Tue]

uses up a further 24:00 leaving 08:00 to be used on

06-Jan-2010 [Wed]

our shift starts at 07:00 so with the 8 hours added to it our end datetime is

06-Jan-2010 15:00

I hope that helps.
0
JonybrvAuthor Commented:
DonkeyOte:

Thanks for your great help. I modified it a little to change the work timings as  8AM-5PM. And P4 as 90:00. Now when entering the start date as 3/17/2010  6:53:46 AM it shows the end date as 3/31/2010  8:00:00 AM

Should it not be  3/30/2010  5:00:00 PM ? Correct me if I understood things wrongly.
0
DonkeyOteCommented:
The issue will be down to miniscule roundings - I would need to look at it in more depth... I guess you could discount a ms from the value in D if you wanted to be inclusive but I say that without much thought ;)

eg - using the sample file:

E2:
=WORKDAY($D2,CEILING((VLOOKUP($C2,_SLA,2)+MOD($D2-"0:0:0.001",1)-$H$2)/($I$2-$H$2),1)-1,_Holidays)+MOD($D2-"0:0:0.001",1)+VLOOKUP($C2,_SLA,2)-CEILING(MOD($D2-"0:0:0.001",1)+VLOOKUP($C2,_SLA,2)-$H$2,$I$2-$H$2)+$I$2-$H$2
0
JonybrvAuthor Commented:
Thanks. You are a life saver. I will keep this open for sometime as I might need more tweaking on this.
0
DonkeyOteCommented:
No problem - all kudos must go (the great) Barry Houdini as it is from him that I learnt this approach
(...if he sees this he may be able to streamline for you further)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.