Solved

Need to calculate whether metric was met using business days

Posted on 2013-01-15
15
253 Views
Last Modified: 2013-01-28
I need to create a report that shows whether the acknowledgement was completed within 3 business days of being created. I would like the Acknowledgement date/time fields to be filled in green if they met the requirement and in red if not.

Fields provided:
Ticket Number
Create Date/Time
Acknowledgement Date/Time

Definition of Business Days: Mon - Fri, 8am-5pm CST

How can I do that?
Formulas and rules would be preferable to VB as I am not familiar with that.
0
Comment
Question by:awp2012
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
  • 4
15 Comments
 
LVL 13

Expert Comment

by:Shanan212
ID: 38779552
How would you know the acknowledgement was met? I understand that it has a creation date. Does it has a completion date? (so that we can check if it was completed within 3 days?)
0
 

Author Comment

by:awp2012
ID: 38779573
Sometimes before working on an issue a team selects Acknowledged, indicating they are aware it is open and they need to prioritize it. So there is an Acknowledgement Date/Time field.

We also have completion but have other reports that indicate whether items were resolved within 30 min or 4 hrs for higher impact issues.

My biggest trouble is trying to figure out how to identify if it was met within 3 business days for requests which have a lower impact.
0
 
LVL 13

Expert Comment

by:Shanan212
ID: 38779608
Maybe I am wrong.

You have a starting/acknowledged date. How would you know if it was 'met within 3 business days'

If you want an indication of days left to complete, that I can do. But without a end point, I cannot tell you if it was completed or not. I mean you need something to compare correct?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:awp2012
ID: 38779666
I probably wasn't clear, sorry. The objective is whether the acknowledgement was accomplished within 3 business days. We have other reports that focus on the completion of the request.

Think of it as using the acknowledge date/time as the end date/time - it's just another field.

If I'm looking at minutes, hours, days, that is easy. But calculating based upon business days (Mon-Fri 8a-5p CST) is difficult.
0
 
LVL 13

Expert Comment

by:Shanan212
ID: 38779705
see attached and let me know.

My assumption in this case is that you want to know how many workdays it took to acknowledge.
see.xlsx
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 38779919
Will the create/acknowledgement dates/times always be within the working hours? If so then with create time/date in A2 you can get exactly 3 working days later (including time) by using this formula

=WORKDAY(A2,3)+MOD(A2,1)

so you can just compare that to B2, if it's later then you are late so you can format cells green by default and use conditional formatting to format the others red by using this formula as the condition

=B2>WORKDAY(A2,3)+MOD(A2,1)

This requires Excel 2007 or later - post back if you can't make it work, preferably with an example worksheet

regards, barry
0
 

Author Comment

by:awp2012
ID: 38780395
The tickets could be open at any time in a 24 hour period. I need to see them in partial days. Such as in the attached example it shows 4 days when the acknowledgement was only 4 min late.
0
 
LVL 13

Expert Comment

by:Shanan212
ID: 38780465
=IF(AND(INT(STARTTIME)=INT(ENDTIME),NOT(ISNA(MATCH(INT(STARTTIME),HOLIDAYLIST,0)))),"0 DAYS 0 HOURS", IF(INT(STARTTIME)=INT(ENDTIME),
 "0 DAYS " & ROUND(24*(ENDTIME-STARTTIME),2)&"HOURS", MAX(NETWORKDAYS(STARTTIME+1,ENDTIME-1,HOLIDAYLIST),0)+ INT(24*(((ENDTIME-INT(ENDTIME))-(STARTTIME-INT(STARTTIME)))+ (ENDDATE-STARTDATE))/(24*(ENDDATE-STARTDATE)))& " DAYS "&MOD(ROUND(((24*(ENDTIME-INT(ENDTIME)))-24*STARTDATE)+ (24*ENDDATE-(24*(STARTTIME-INT(STARTTIME)))),2), ROUND((24*(ENDTIME-STARTDATE)),2))&" HOURS "))

Open in new window


Try this. Untested. Try naming a cell as endtime and starttime with respective times in it (eg: 8 AM and 5 PM)
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 38780808
If you have create time/date in B2 and acknowledgement time/date in C2 then you can use this formula in D2 for the elapsed hours

=(NETWORKDAYS(B2,C2)-1)*(H$2-G$2)+IF(NETWORKDAYS(C2,C2),MEDIAN(MOD(C2,1),G$2,H$2),H$2)-MEDIAN(NETWORKDAYS(B2,B2)*MOD(B2,1),G$2,H$2)

where G2 and H2 define the start and end time of the working day

That will work whether start  times/dates are in the working day or not

On the attached I set up the conditional formatting to format red where the elapsed time exceeds 27 hours (3 days) - i.e. the value in I2

Times/dates are randomly generated by formulas - press F9 to generate new ones - conditional formatting will update accordingly.

regards, barry
business-hours-barry.xls
0
 

Author Comment

by:awp2012
ID: 38781267
barryhoudini your formula works. I am unable to figure out the conditional formatting. I can see the formatting works the way I need it to in your sample.
0
 
LVL 50

Expert Comment

by:barry houdini
ID: 38782217
All that I did with the formatting was initially to use regular formatting to format the whole of B2:D11 green.

Then I selected that range again and used this formula in conditional formatting

=$D2>$I$2

that formats each row if the column D value exceeds the value in I2 (currently 27:00 = 3 days)

If you need I can explain further how you set that up - which version of Excel are you using?

regards, barry
0
 

Author Comment

by:awp2012
ID: 38785036
I tried that but all of the acknowledgement times were correct (except those which were not acknowledged at all and those fields showed all ###### instead of the time).

I made a change to one of the acknowledgement times which would have clearly been over the 27 hrs and it didn't change the background for that cell. It did change another cell to red that was in compliance.

I am using Excel 2010.
0
 
LVL 50

Accepted Solution

by:
barry houdini earned 500 total points
ID: 38785194
OK, you can make a change to the formula like this, so that you get a blank if both date/times are not filled in

=IF(COUNT(B2,C2)=2,(NETWORKDAYS(B2,C2)-1)*(H$2-G$2)+IF(NETWORKDAYS(C2,C2),MEDIAN(MOD(C2,1),G$2,H$2),H$2)-MEDIAN(NETWORKDAYS(B2,B2)*MOD(B2,1),G$2,H$2),"")

and then change conditional formatting formula to

=($D2>$I$2)*($D2<>"")

If you are getting the wrong rows formatted then your ranges/formulas may be mismatched - my conditional formatting formula refers to D2 because that's the first row of the "applies to" range - make sure that your "applies to" range and formula references match - if you can't make it work can you attach the workbook here (or a representative example)

regards, barry
0
 

Author Comment

by:awp2012
ID: 38787804
I am in meetings today and will try this tomorrow. Thanks for the update!
0
 

Author Closing Comment

by:awp2012
ID: 38828436
Thank you Barry. That was exactly what I needed!
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question