Forum Topic

excel macro programming

  • 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.
  • =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
  • 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")
  • ^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
  • 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
  • ^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).
  • ^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
  • 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
  • =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
  • 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()
  • 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
  • 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
  • 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 :)
  • 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
  • ^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?
  • It is just formatting.
    You can use
    y:m:d:h

    for
    year:month:day:hour
    So that the year difference is captured.
  • ano yung SLA?
  • ano yung SLA?
  • SLA = service level agreement
  • 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?
  • 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"))
  • shaqtn' a fool for rozier, lol
  • oops wrong thread, my bad
  • 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.
  • ^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...
  • @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
  • 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
  • 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")
  • 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.