Re: Rogue formatting: "Normal" changing from general to time
hopeful nudge...
Re: Rogue formatting: "Normal" changing from general to time
hopeful nudge...
Re: Calculation of Breach
If 8 am to 6 pm:
=B10+IF(A10=1,2/24,IF(A10=2,4/24,8/24))+IF(AND(A10=3,OR(TEXT(B10,"H")*1>10,AND(TEXT(B10,"H")*1=10,LEFT(TEXT(B10,"mm:ss"),2)*1>0))),14/24,0)
If 8 am to 7 pm:
=B10+IF(A10=1,2/24,IF(A10=2,4/24,8/24))+IF(AND(A10=3,OR(TEXT(B10,"H")*1>11,AND(TEXT(B10,"H")*1=11,LEFT(TEXT(B10,"mm:ss"),2)*1>0))),13/24,0)
I changed the hour marker in two if statements from 10 to 11 because 11 am is now eight hours prior to closing time.
Add the following to stop weekend calculation:
+IF(AND(A10=3,TEXT(B10,"DDD")="Fri"),2,0)
All that does is add two days if the ticket is priority 3 and opened on a Friday.
You're still going to have trouble with holidays in that scenario. In the past, I've solved for holidays by having a table of holidays, and using a VLOOKUP function to see how many days the office would be closed (a table of dates and how many extra days to add). If the open date is before a three day weekend, I add 1. If it's the day before Thanksgiving, add 2.
Re: Calculation of Breach
1) It changes the formula, yes. If you were opening at 8 am instead of 6 am, you'd need to add 14 hours instead of 12. The part where you add 0.5 (twelve hours) would need to change to 14/24 (14 hours) if you open at 8 am, or 15/24 if you open at 9 am. The numerator is driven by how many hours from when you close to when you open.
2) Priority 4 would complicate in two ways. First, the IF statements at the beginning of the formula would need another layer to account for the amount of time being added. Second, if you're saying the SLA is greater than the amount of business hours in the day, you're adding additional complexity. Consider the request that comes in at 5 p.m., it's not going to be due until the day after tomorrow. If you plan to go this route, I would simplify the whole thing by creating a more robust table with priorities and logic built in to determine how much extra pad you have to add to the new priority. You can then use VLOOKUPs to bring in the extra time by priority. Your formula would be much simpler; something like this:
=B2+VLOOKUP(A2,SLAs!$A$1:$B$5,2,0)+IF(VLOOKUP(A2,SLAs!$A$1:$C$5,3,0)>TEXT(B2,"HH:MM"),0.5,0)+IF(VLOOKUP(A2,SLAs!$A$1:$D$5,3,0),0)>TEXT(B2,"HH:MM"),0.5,0)
VLOOKUP 1: the amount of hours each priority has
VLOOKUP 2: if the ticket is after the cut-off, add 12 hours so we know we can finish it the next day
VLOOKUP 3: if the ticket is at a different time of day, we add another 12 hours (the 5:00 pm example with a 16 hour service level)
Re: Calculation of Breach
No problem. It helps to know that Excel treats dates and times as a decimal. Dates themselves are whole numbers. Yesterday's date is represented by 41172, today is 41173. To add time, you add a fraction of the day, so today at noon is 41173.5. Today at 11:32 and 45 seconds (in the morning) is 41173.48108... Knowing that, if you want to add a day to a value, you add 1. To add one hour to a time, add 1/24. To add two hours, that's 2/24. Twelve hours is 12/24 (or 0.5).
You should probably update your formula to this:
=B10+IF(A10=1,2/24,IF(A10=2,4/24,8/24))+IF(AND(A10=3,OR(TEXT(B10,"H")*1>10,AND(TEXT(B10,"H")*1=10,LEFT(TEXT(B10,"mm:ss"),2)*1>0))),0.5,0)
For this formula, we start with the leading value, or the time the ticket starts.
=B2
Next, we add two, four, or eight hours, depending on the priority. If A2 = 1, add 2/24 (or two hours). If A2 = 2, add 4/24 (four hours). Otherwise, it must be priority three, so add eight hours (8/24).
+IF(A2=1,2/24,IF(A2=2,4/24,8/24))
Last, if it's priority three, we need to add twelve hours, but only under certain circumstances. First, we check if it's priority three.
+IF(AND(A2=3,
Then, we check for the circumstances. We know priority three is only from 6 am to 6 pm, so we can do a test based on the hour of the day. If it comes in after ten in the morning, we have to add an extra twelve hours to our time. This is tricky, though. If it comes in exactly at ten am, there's no need to add half a day, but 10:01 does require the extra day. To solve for this, I use an OR statement.
The first part of the OR checks to see if the hour is greater than ten. I do that by formatting the date/time value to just the hour. I used a text formula, which returns the text value of the number, so I multiply it by 1 to return it to a numeric value.
OR(TEXT(B2,"H")*1>10,
The second part of the OR checks to see if the value in the hours position is a ten and the value in the minutes position is greater than zero. Because Excel uses the text format "M" to represent the month, I had to figure out another way to get the minutes into my logic statement. That's why I asked you to update the formula. I had to pull out the minutes and seconds together ("mm:ss"), then take just the left two digits to extract the minutes. The old formula is adding half a day if the hour is ten and the month is greater than zero. We want to add half a day if the hour is ten and the number of minutes is greater than zero.
AND(TEXT(B10,"H")*1=10,LEFT(TEXT(B10,"mm:ss"),2)*1>0))),
Finally, if priority is three, and either the hour is greater than ten, or the hour IS ten and the minutes are greater than zero, what do we add? Half a day. Otherwise, we add zero.
0.5,0)
There's a lot going on there, but I've been playing with time calculations and service levels in Excel for ten years plus, so I feel pretty comfortable with this (and am a little embarrassed that I messed up with the first formula I gave you). Sorry about that - but this will get you where you need to go. If any of this is confusing, let me know and I'll take another pass at explaining it.
Re: Unable to Paste > Special > Values?
This is solved. :wowee:
In my office, Excel 2010 was installed with the Bluetooth Add In installed by default. I took it off her computer, and the values now copy properly.
This doesn't make a lot of sense to me... I have other users who have the Bluetooth Add In, and no one else is experiencing the issue, but when I deactivated it, the macro started working properly. Posting my results in case someone else has a similar issue.
Re: Calculation of Breach
For column C (this is C2, just copy down):
=B2+IF(A2=1,2/24,IF(A2=2,4/24,8/24))+IF(AND(A2=3,OR(TEXT(B2,"H")*1>10,AND(TEXT(B2,"H")*1=10,TEXT(B2,"M")*1>0))),0.5,0)
Re: find 3 matching criteria then copy entire row
Glad I could help - you're welcome.
Re: Unable to Paste > Special > Values?
The name of the file we are copying from varies - the software that generates that Excel file does not give it a consistent name, so that part is done manually by the end user.
Re: find 3 matching criteria then copy entire row
I forgot a line of code, sorry. This is copying the data to the same spot every time it finds a match... so it is looping through the whole thing, but it's putting it all in the same spot. Let's try one more bit of code:
Sub NewCollect()
Dim EntryRow As Integer, i As Integer
EntryRow = 1
For i = 1 To 10000
If Sheet2.Range("a1").Offset(i, 0).Value = "" Then Goto ExitiLoop
If Sheet2.Range("a1").Offset(i, 0).Value = Sheet3.Range("a2").Value And _
Sheet2.Range("b1").Offset(i, 0).Value = Sheet3.Range("b2").Value And _
Sheet2.Range("c1").Offset(i, 0).Value = Sheet3.Range("c2").Value Then
Sheet4.Range("a1").Offset(EntryRow, 0).Range("a1:ak1").Value = Sheet2.Range("a1").Offset(i, 0).Range("a1:ak1").Value
EntryRow = EntryRow + 1 ' This should help not put it all on top of each other
End If
Next i
ExitiLoop:
End Sub
Display More
Re: find 3 matching criteria then copy entire row
Something like this?
Sub NewCollect()
Dim EntryRow As Integer, i as Integer
EntryRow = 1
For i = 1 To 10000
If Sheet2.Range("a1").Offset(i, 0).Value = "" Then GoTo ExitiLoop
If Sheet2.Range("a1").Offset(i, 0).Value = Sheet3.Range("a2").Value And _
Sheet2.Range("b1").Offset(i, 0).Value = Sheet3.Range("b2").Value And _
Sheet2.Range("c1").Offset(i, 0).Value = Sheet3.Range("c2").Value Then
Sheet4.Range("a1").Offset(EntryRow, 0).Range("a1:ak1").Value = Sheet2.Range("a1").Offset(i, 0).Range("a1:ak1").Value
End If
Next i
ExitiLoop:
End Sub
Display More
Re: Unable to Paste > Special > Values?
If it helps, we're using Excel 2010.
Re: Macro to insert formula around cell contents
Sub CovnertToFormula()
For i = 1 To 1000
Range("a1").Offset(i, 0).FormulaR1C1 = "=IF(RC[1]="""","""",""" & Range("a1").Offset(i, 0).Value & """)"
Next i
End Sub
Edit: Assumes labels are in column A and values in column B. This would convert all of your labels to the IF statement in my original post.
Re: Macro to insert formula around cell contents
You don't need Visual Basic to do this.
For example, if A2 is "Number of Sales" and B2 is the value, put this in A2:
=IF(B2="","","Number of Sales")
Re: Stacked double y-axis graph
Your data isn't laid out in a way that is conducive to creating the graph you want. Make a table with four columns of data. 1a (actual), 1a (goal), 1b (actual), 1b (goal), then format the graph as needed.
Each row of data should be one quarter for your graph. No gaps, no yearly roll up.
We have software in our office that creates Excel reports as output. The reports are a mess. Data all over the place, no table format, merged cells everywhere, etc. To create usable data, I wrote a macro for end users to import the report daily. They open the report, copy columns A:V, then run the macro while those columns are in the clipboard.
The macro has this line of code to Paste > Special > Values:
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
This works for everyone that uses it but one person, who gets a run-time error 1004, PasteSpecial method of Range class failed. I tried to manually paste > special > values, and it is not available. The paste > special options are:
From what's available here, it looks like she's copying a picture. This feels like some sort of security option, but I can't figure it out. Any suggestions?
Hi, all. I'm looking to make a calculated field in a pivot table that can be used to show the percent of scores that are 100 (the Score field can have values from 40 to 100).
I thought this might work: =IF(Score = 100, 1, 0) - but no such luck.
What I really want is to be able to make some buckets of my data. <98, 98 to <100, and 100 - then use the pivot table to display a pivot table & pivot chart to show the % of scores that are in the various buckets over time.
Thanks!
Re: Excel crash when sending file as attachment.
bump
I am using Excel and Outlook 2010. When I click File > Save & Send > Send Using E-mail > Send as Attachment, Excel crashes every time. The email is sent successfully, but I have to go through the whole recovery cycle to get back to Excel and get back to work.
This is happening on a machine w/Windows XP.
Has anyone experienced this, or know of a solution?
Re: Disable Links message on open
Works like a charm - thanks.
Re: Employee Schedule Builder
My advice would be to walk away from this idea. :stare:
I have over a decade of professional scheduling experience, and would rather buy an off-the-shelf solution that try to code for all the complexities needed to do this right. Try googling "employee schedule software" and you'll find plenty of options, some with free trials.
Good luck,
James