Results 1 to 3 of 3

Thread: VBA Code to Formula code
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Oct 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA Code to Formula code

    Hi

    Am trying to convert the attached VBA code to formula code because google sheet doesnt recognise the excel coding.

    Can someone please asissit.

    On insertion of the dates in Colunmn B, Column E, F, G, H and I automatically get populated Via VBA Code.

    I would like an Excel Formula to inserted in the columns to replace the VBA code or an alternative for google sheet ( Please delete if am breaching the terms)

    The Condition is : M-F 0600 - 1800 considers as Day , M-F 1800 - 0600 considers as Night, SAt and Sun are 24 hours

    Thannks

  2. #2
    Board Regular
    Join Date
    Nov 2006
    Location
    London
    Posts
    8,259
    Post Thanks / Like
    Mentioned
    21 Post(s)
    Tagged
    2 Thread(s)

    Default Re: VBA Code to Formula code

    You cant attach files on this forum.

    Just copy the code and surround it within CODE tags, e.g.

    [ code ]
    [ /code ]

    NOTE: Remove the spaces above when entering the tags - I had to enter the spaces or it wouldnt show up in this post.

  3. #3
    New Member
    Join Date
    Oct 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA Code to Formula code

    Code:
    Function ShiftSplit(sd, st, et, Optional PH, Optional State)
    Dim z(0 To 3), zz(0 To 3)
    If Not (IsEmpty(sd) Or IsEmpty(st) Or IsEmpty(et)) Then
      ord = [{2.2708,2.7708;3.2708,3.7708;4.2708,4.7708;5.2708,5.7708;6.2708,6.7708}]
      night = [{2,2.2708;2.7708,3.2708;3.7708,4.2708;4.7708,5.2708;5.7708,6.2708;6.7708,7}]
      sat = [{0,1;7,8}]
      sun = [{1,2;8,9}]
      AllShifts = Array(ord, night, sat, sun)
      Fullsdt = CDbl(sd + st)
      Fulledt = CDbl(sd + et - (st >= et))
      sdt = Evaluate("mod(" & Fullsdt & ",7)")
      edt = Evaluate("mod(" & Fulledt & ",7)")
      If edt < sdt Then edt = edt + 7
      If Not IsMissing(PH) Then
        PHVals = PH.Value2
        sdLng = sd.Value2
        For i = 1 To UBound(PHVals)
          If sdLng = PHVals(i, 2) Then  'matching date
            If State = PHVals(i, 1) Then  'matching state
              PHsdt = sdLng
              PHedt = PHsdt + 1
              Exit For
            End If
          End If
        Next i
        'now check for day 2 if necessary:
        If Int(edt) > Int(sdt) Then  'it is necessary
          sdLng = sdLng + 1  'add 1 to startdate to test for second day being public holiday
          For i = 1 To UBound(PHVals)
            If sdLng = PHVals(i, 2) Then  'matching date
              If State = PHVals(i, 1) Then  'matching state
                If IsEmpty(PHsdt) Then PHsdt = sdLng
                PHedt = sdLng + 1
                Exit For
              End If
            End If
          Next i
        End If
        'now convert PH start/end to days of week
        PHsdt = Evaluate("mod(" & PHsdt & ",7)")
        PHedt = Evaluate("mod(" & PHedt & ",7)")
        If PHedt < PHsdt Then PHedt = PHedt + 7
      End If
      For j = LBound(AllShifts) To UBound(AllShifts)
        For i = LBound(AllShifts(j)) To UBound(AllShifts(j))
          ThisBlockStart = Application.Max(AllShifts(j)(i, 1), sdt)
          ThisBlockEnd = Application.Min(AllShifts(j)(i, 2), edt)
          temp = Application.Max(0, ThisBlockEnd - ThisBlockStart)
          If temp > 0 Then
            z(j) = z(j) + temp
            'check against public hols:
            PHBlockStart = Application.Max(PHsdt, ThisBlockStart)
            PHBlockEnd = Application.Min(PHedt, ThisBlockEnd)
            PHtemp = Application.Max(0, PHBlockEnd - PHBlockStart)
            If PHtemp > 0 Then
              zz(j) = zz(j) + PHtemp
            End If
          End If
        Next i
      Next j
    End If
    For i = LBound(z) To UBound(z)
      If z(i) = 0 Then z(i) = ""
      If zz(i) = 0 Then zz(i) = ""
    Next i
    If IsMissing(PH) Then ShiftSplit = z Else ShiftSplit = zz
    End Function

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •