-
ram2017
on
10 May 17 @ 07:07 AM #
Later I will create a formula for due dates considering saturdays, sundays and holidays.
Gagawa ako ng new column para hindi ma revise yong na receive mo from 2 reports.
-
CrashAndBurn
on
10 May 17 @ 07:17 AM #
=IF(INT(NOW())<D2,TEXT(D2-NOW(),"d:h"),"-"&TEXT(NOW()-D2,"d:h"))
Gumagana apart from those rows where Due time is either blank or Not Assigned
-
ram2017
on
10 May 17 @ 07:18 AM #
Formula for time to SLA with "Not Assigned" or blank.
=IF(AND(D2 <> "Not Assigned", D2 <> ""),IF(INT(NOW())<D2,TEXT(D2-NOW(),"d:h"),"-"&TEXT(NOW()-D2,"d:h")),"N/A")
-
CrashAndBurn
on
10 May 17 @ 07:35 AM #
^oks sir, pero in reality dapat walang N/A kasi dapat ma populate ng formula yung due by time in cases na walang value or not assigned, dito na siguro need yung different column?
-- edited by CrashAndBurn on May 10 2017, 07:35 AM
-
ram2017
on
10 May 17 @ 10:18 AM #
Almost done just testing different service types and priority, I will send the file later.
I created additional SLA column to simplify the formula for MyDueDate and act as a visual check considering that this is a critical info in the calculation of due dates. The formula for SLA(day) column is shown in the image, this can be expanded but would break after more than 64 if's. I like to create a public function for this to solve the 64 if limit, if you still add more conditions.
Saturday's and Sunday's as well as holidays in holiday sheet are all considered in the calculation of due dates.
This can take SLA in hours too.
Sample with Security Incident, P3 = 6hrs SLA
Full image here.
https://i.imgur.com/Yc5TvE4.png
-- edited by ram2017 on May 10 2017, 10:37 AM
-
CrashAndBurn
on
10 May 17 @ 11:48 AM #
^mas ok nga yan kasi mas madali maintindihan, so yung column J sir eh calculated lang pag walang value yung column D tama ba? Pag merong value eh i-convert lang nya to days e.g. D-C=dd:hh? Kaya ko natanong kasi there are cases na manually updated yung due date, like if escalated as urgent yung ticket.
Yung sa MyDueDates column naman eh tingin ko pwede ko nalang i duplicate to another column in case I need to modify it like kung counted ang weekends at holidays tama ba? So parang MyDueDate (Business Hours) tapos another column na MyDueDate (24x7).
-
ram2017
on
10 May 17 @ 05:25 PM #
^mas ok nga yan kasi mas madali maintindihan, so yung column J sir eh calculated lang pag walang value yung column D tama ba?
Hindi, column J or SLA is always calculated based on the type and priority, but it is not necessarily be used if there is already a date in DueBy Time or column D. If someone would bypass the SLA then they can use "Emergency" or something in the Request type or change priority to 0. As I understand SLA condition should not be altered as much as possible because this is SLA in the first place, this is one of the driving forces behind the bussiness itself. But of course it is still up to you to decide if you don't calculate it or not.
Pag merong value eh i-convert lang nya to days e.g. D-C=dd:hh?
Correct.
Kaya ko natanong kasi there are cases na manually updated yung due date, like if escalated as urgent yung ticket.
By calculating the SLA you can also compare if the date in col D (from 2 reports) if there is, the SLA of the said service is not breached, sort of counter-check even if you don't use this anymore because due date was already indicated.
Yung sa MyDueDates column naman eh tingin ko pwede ko nalang i duplicate to another column in case I need to modify it like kung counted ang weekends at holidays tama ba? So parang MyDueDate (Business Hours) tapos another column na MyDueDate (24x7).
Correct.
-- edited by ram2017 on May 10 2017, 05:29 PM
-
ram2017
on
10 May 17 @ 07:42 PM #
CrashAndBurn Send Message View User Items on 10 May 17 @ 11:48 AM #
Link to file was sent via PM with updated formula.
-- edited by ram2017 on May 10 2017, 07:42 PM
-
CrashAndBurn
on
11 May 17 @ 04:20 PM #
=IF(OR(J2<>"",AND(D2<>"Not Assigned",D2<>"")),IF(J2>=1,IF(OR(D2="Not Assigned",D2=""),(HOUR(C2)/24)+WORKDAY.INTL(C2,J2,1,Holiday!$A$2:$A$1000),D2),C2+J2),"")
Pano sir alisin yung pag factor ng holiday at weekend e.g. 24x7? Naguluhan ako dun sa WORKDAY.INTL first time ko lang nakita to eh.
=IF(AND(E6="Service Request",H6="Priority 3"), 5,IF(AND(E6="Service Request",H6="Priority 4"),10,IF(AND(E6="Service Request",H6="Priority 5"),15,IF(AND(E6="Incident",H6="Priority 3"),3,IF(AND(E6="Incident",H6="Priority 4"),5,IF(AND(E6="Incident",H6="Priority 5"),10,""))))))
Merong ilang request type na pareho ng SLA, pwede ba na incorporate as OR statement? Like =IF(AND(E6="Service Request" OR "Event",H6="Priority 3"), 5
-
ram2017
on
11 May 17 @ 07:36 PM #
CrashAndBurn Send Message View User Items on 11 May 17 @ 04:20 PM #
Pano sir alisin yung pag factor ng holiday at weekend e.g. 24x7? Naguluhan ako dun sa WORKDAY.INTL first time ko lang nakita to eh.
Change:
WORKDAY.INTL(C2,J2,1,Holiday!$A$2:$A$1000)
To:
WORKDAY.INTL(C2,J2,"0000000")
Meaning, all days are working days, represented by 7 0's string. The 4th argument (holiday is removed) in workday.intl()
-
ram2017
on
11 May 17 @ 07:55 PM #
CrashAndBurn Send Message View User Items on 11 May 17 @ 04:20 PM #
Merong ilang request type na pareho ng SLA, pwede ba na incorporate as OR statement? Like =IF(AND(E6="Service Request" OR "Event",H6="Priority 3"), 5
Hindi, iba ang syntax ng excel pag gagamit ka ng or.
if (or(condition1, condition2, other_conditions),true,false)
Same with and.
if (and(condition1, condition2, other_conditions),true,false)
Pag may idag-dag ka, I suggest to put it in the last statement.
Example, add Emergency, Priority 0, 1. Then add ")" at the end.
=IF(AND(E6="Service Request",H6="Priority 3"), 5,IF(AND(E6="Service Request",H6="Priority 4"),10,IF(AND(E6="Service Request",H6="Priority 5"),15,IF(AND(E6="Incident",H6="Priority 3"),3,IF(AND(E6="Incident",H6="Priority 4"),5,IF(AND(E6="Incident",H6="Priority 5"),10,IF(AND(E6="Emergency",H6="Priority 0"),1,"")))))))
-- edited by ram2017 on May 11 2017, 08:00 PM
-
ram2017
on
11 May 17 @ 10:19 PM #
I created a public function to calculate SLA. The logic is simpler to follow than using formula in a cell.
Create a module in VBA window then paste the following. This is easier to extend as well.
Public Function GetSLA(ByVal st As Range, ByVal sp As Range) As Integer
' Calculates SLA given service type and priority
If st = "Service Request" Then
If sp = "Priority 3" Then
GetSLA = 5
ElseIf sp = "Priority 4" Then
GetSLA = 10
ElseIf sp = "Priority 5" Then
GetSLA = 15
End If
ElseIf st = "Incident" Then
If sp = "Priority 3" Then
GetSLA = 3
ElseIf sp = "Priority 4" Then
GetSLA = 5
ElseIf sp = "Priority 5" Then
GetSLA = 10
End If
Else
GetSLA = 0
End If
End Function
Then use it like this.
Basically.
=GetSLA(cell1, cell2)
where:
cell1 = request type
cell2 = priority
-- edited by ram2017 on May 11 2017, 10:22 PM
-
CrashAndBurn
on
12 May 17 @ 01:48 AM #
Pag may idag-dag ka, I suggest to put it in the last statement.
Example, add Emergency, Priority 0, 1. Then add ")" at the end.
Does it mean na mas better to just add another statement rather than use the OR option? Is there a limit sa dami ng nested IF or eto na yung nabanggit earlier na 64 ata ang limit?
I created a public function to calculate SLA. The logic is simpler to follow than using formula in a cell.
Create a module in VBA window then paste the following. This is easier to extend as well.
Mas madali nga maintindihan pero tanong ko lang does this VBA come part of the spreadsheet lets say i need to copy it elsewhere or I need to re-add the VBA? Sorry noob talaga :)
-
ram2017
on
12 May 17 @ 04:40 AM #
Does it mean na mas better to just add another statement rather than use the OR option?
No, you can use OR if you want, my suggestion is based on the fact that you are still not familiar on how to use OR in the statement and the degree of the complication in the formula.
Is there a limit sa dami ng nested IF or eto na yung nabanggit earlier na 64 ata ang limit?
eto na yung nabanggit earlier na 64...
Mas madali nga maintindihan pero tanong ko lang does this VBA come part of the spreadsheet lets say i need to copy it elsewhere or I need to re-add the VBA?
Part of spreadsheet.
If you want it to be available in other workbooks, you can make it as an excel add-in. Then enable that add-in.
-- edited by ram2017 on May 12 2017, 04:51 AM
-
CrashAndBurn
on
18 May 17 @ 09:15 AM #
^ngayon lang nagkatime i test, napansin ko yung time to SLA eh mali ang result?
lets say yung value nung MyDueDate eh 17-01-2016 10:00:00 AM, after applying the formula ang result eh -1.3 so to me that is 1 day 3 hours breached. Which is mali since that day is calculated against todays date tama ba? So dapat more than a year na since 2016 yung value sa due date?
-
ram2017
on
19 May 17 @ 07:27 PM #
It is just formatting.
You can use
y:m:d:h
for
year:month:day:hour
So that the year difference is captured.
-
jaredski
on
19 May 17 @ 07:47 PM #
ano yung SLA?
-
jaredski
on
19 May 17 @ 07:47 PM #
ano yung SLA?
-
ram2017
on
20 May 17 @ 01:31 AM #
SLA = service level agreement
-
CrashAndBurn
on
20 May 17 @ 03:46 AM #
It is just formatting.
You can use
y:m:d:h
for
year:month:day:hour
So that the year difference is captured.
This means I need to format the entire column tama ba? And will not affect the formula? Or do I need to include the format in the formula?
-
ram2017
on
20 May 17 @ 05:10 AM #
Just the formula:
From
=IF(NOW()<K2,TEXT(K2-NOW(),"d:h"),"-"&TEXT(NOW()-K2,"d:h"))
To
=IF(NOW()<K2,TEXT(K2-NOW(),"y:m:d:h"),"-"&TEXT(NOW()-K2,"y:m:d:h"))
-
jaredski
on
20 May 17 @ 09:10 AM #
shaqtn' a fool for rozier, lol
-
jaredski
on
20 May 17 @ 09:10 AM #
oops wrong thread, my bad
-
triptolemus
on
05 Jun 17 @ 12:11 PM #
Mga guru, patulong naman ako. baka sa inyo eh chicken lang to.
Regarding barcode Reader and Excel. Like inventory ng serial numbers then at the same time eh i ka count nya in realtime kung anong nakikita ng bar code scanner tapos na didisplay ang total sa excel sheet.
Kumbaga example eh ballpen, pag ini scan nya ang bar code sunod sunod eh bibilangin na din nya agad para nakikita mo kung ilan na ang na scan mo.
Salamat sa makakatulong and more power to this thread.
-
dodick
on
05 Jun 17 @ 04:51 PM #
^try this method below.
-Table or list of you inventory with the associated code.
A1:11111
A2:22222
A3:33333
A4:44444
A5:55555
-C1-C11 range of your scanned bar code, A1 criteria for search then count's result
B1:=COUNTIF($C$1:$C$11,A1) = 3
B2:=COUNTIF($C$1:$C$11,A2) = 1
B3:=COUNTIF($C$1:$C$11,A3) = 0
Note: This is where your bar code goes.
C1:11111
C2:11111
C3:22222
C4:11111
Good luck...
-
triptolemus
on
05 Jun 17 @ 07:02 PM #
@dodick
Salamat ng madami. try ko to bukas di kasi kaya ng powers ko at di naman ako masyadong maabilidad sa Excel.
- Post deleted #11971625
-
alexgutz
on
10 Jul 17 @ 01:17 PM #
Baka me maka tulong. Column A e series of numbers say 1 to 100. Column B and C e real numbers with 3 significant digist. Say column D and E e real numbers din with 3 significant digits din. I need column F to tell me which row naka match ang D & E sa B & C. Yung hindi sana gagawa ng program at pag program naman e sa tingin ko e kaya ko gawin. Doon lang sana gagawin sa worksheet.
-- edited by alexgutz on Jul 12 2017, 03:56 PM
-
dodick
on
12 Jul 17 @ 02:01 PM #
A B C
111 222 Not Matched
222 111 Not Matched
333 333 Matched on Row : 3
222 222 Matched on Row : 4
111 333 Not Matched
111 222 Not Matched
333 333 Matched on Row : 7
333 111 Not Matched
111 111 Matched on Row : 9
formula for col C : =IF(A1=B1,"Matched on Row : " & ROW(),"Not Matched")
-
alexgutz
on
12 Jul 17 @ 03:55 PM #
A B C D E F
1 1.234 5.367 1.320 3.245
2 3.785 6.258 6.335 5.358
3 9.334 10.123 3.785 6.258 MATCH ROW 2 (SINCE D3=B2 AND E3=C2 kaya magsasabi na match sila)
4 0.125 11.723 3.356 8.879
Yung column F ang dapat magsabi na yung data sa D & E e naka match sa ROW 2 ang B & C. Yan ang gusto ko sana gawin.