Excel VBA to Split Period of Months into Individual Month with extra criteria

Doylezeebeast

New Member
Joined
Nov 19, 2020
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
Hi,
I have a sheet of data such as the example below for sickness absence data for employees which is in sheet1 of the file

Personnel numberName of Employee or ApplicantAtt./Absence typeAtt./abs. type textDesc. of illnessDesc. of illnessAtt./abs. daysAbsence hoursCalendar daysStart DateEnd Date
9002285​
Ben Cooper0220*SSP Sickness Abs p100%G0049Sickness/Diarrhoea1.006.001.0007/11/201907/11/2019
9002362​
Steven Smith0220*SSP Sickness Abs p100%G0049Cough/Cold/Flu0.554.000.0010/05/201910/05/2019
9002565​
Matt Damon0230Sickness Abs paid 100%G0034Cough/Cold/Flu0.503.750.0007/02/201907/02/2019
9002565​
Matt Damon0230Sickness Abs paid 100%G0034Cough/Cold/Flu1.007.501.0008/02/201908/02/2019
9002904​
Stephen Robinson0220*SSP Sickness Abs p100%G0049Sickness/Diarrhoea3.0021.003.0002/01/201904/01/2019
9002905​
John Carper0220*SSP Sickness Abs p100%G0006Stress/Depression21.00157.5029.0016/07/201913/08/2019
9031129​
Mark Johnson0225*SSP Sickness Abs p50%G0012Bad Back63.00567.0092.0009/10/201908/01/2020

Is it is possible to create some VBA to split this data into individual months so it looks like this, and create this data onto sheet 2, leaving sheet 1 data as is/unchanged:

Personnel numberName of Employee or ApplicantAtt./Absence typeAtt./abs. type textDesc. of illnessDesc. of illnessAtt./abs. daysAbsence hoursCalendar daysStart DateEnd Date
9002285​
Ben Cooper0220*SSP Sickness Abs p100%G0049Sickness/Diarrhoea1.006.001.0007/11/201907/11/2019
9002362​
Steven Smith0220*SSP Sickness Abs p100%G0049Cough/Cold/Flu0.554.000.0010/05/201910/05/2019
9002565​
Matt Damon0230Sickness Abs paid 100%G0034Cough/Cold/Flu0.503.750.0007/02/201907/02/2019
9002565​
Matt Damon0230Sickness Abs paid 100%G0034Cough/Cold/Flu1.007.501.0008/02/201908/02/2019
9002904​
Stephen Robinson0220*SSP Sickness Abs p100%G0049Sickness/Diarrhoea3.0021.003.0002/01/201904/01/2019
9002905​
John Carper0220*SSP Sickness Abs p100%G0006Stress/Depression12.0090.0016.0016/07/201931/07/2019
9002905​
John Carper0220*SSP Sickness Abs p100%G0006Stress/Depression9.0067.5013.0001/08/201913/08/2019
9031129​
Mark Johnson0225*SSP Sickness Abs p50%G0012Bad Back17.00153.0023.0009/10/201931/10/2019
9031129​
Mark Johnson0225*SSP Sickness Abs p50%G0012Bad Back21.00189.0030.0001/11/201930/11/2019
9031129​
Mark Johnson0225*SSP Sickness Abs p50%G0012Bad Back20.00180.0031.0001/12/201931/12/2019
9031129​
Mark Johnson0225*SSP Sickness Abs p50%G0012Bad Back5.0045.008.0001/01/202008/01/2020

A couple of things to mention...

In column "Att./abs.days" if this is shown as less than 1 then for column "Calendar days" this should show as "0.00".

For column "Att./abs.days" , the number of days shown here for are the number of working days (Monday to Friday) and based on UK bank holiday. So for example 1/1/20 - 8/1/20 is 5 working days because of bank holiday on 1/1/20

For column "Absence hours" , the absence hours shown should be calculated from the number of "Att./abs.days" column. For example with employee John Carper who has 157.50 absence hours based on 21 days absence = 7.5 hours per day. We can then use this "7.5" value for the calculation of hours in the split output.

I looked at this post as it was helpful but didn't quite go far enough for what i needed:

Thanks for any help I can get on this one :)
 

Doylezeebeast

New Member
Joined
Nov 19, 2020
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
Hi Peter, please find below some sample data for columns B and G:K:

Name of Employee or ApplicantAtt./abs. daysAbsence hoursCalendar daysStart DateEnd Date
Employee Name 10.000.002.0002/01/201903/01/2019
Employee Name 20.000.001.0020/01/201920/01/2019

The expected results would just be the same as this data, as the absences do not span over any months / there are no values in column G that need "splitting" over the months.

Is it possible to have a row that includes more than one month in columns J:K that also has a 0 in column G?

No that would not be possible over a period of a month, there would be at least some valves in column G and H. Indeed even over a period of a week (Monday to Sunday) there would be at least a value shown in G and H respectively.
Thank you.
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
Just checking then about Employee Name 1 above then. In your sample Start Date and End Date are one day apart. If that is possible then surely those two dates could possibly be the last day in one month and the first day in the next month. If that happened, the way my code is working, it would create two rows for that employee.
So, was that a typo with the data (or perhaps just trying to give sample data that I asked for) or do we have to rethink how the code works?

With the code below, if we did have an example with 0 in column G and, say 31/01/2019 and 01/02/2019 as Start Date and End Date then the results would include two identical rows for that employee.

Otherwise, I think I have addressed the issue of 0 in column G. See what you think.

VBA Code:
Sub SplitItUp_v3()
  Dim wsDest As Worksheet
  Dim a As Variant, b As Variant, tempvals(7 To 22) As Variant
  Dim i As Long, j As Long, k As Long
  Dim dStart As Date, dEnd As Date, d1 As Date, d2 As Date
  Dim myStart As String, myEnd As String
  
  a = Sheets("Sheet1").Range("A1").CurrentRegion.Value
  ReDim b(1 To Rows.Count, 1 To 22)
  For i = 2 To UBound(a)
    For j = 7 To 11
      tempvals(j) = a(i, j)
    Next j
    myStart = Format(tempvals(10), "myy")
    myEnd = Format(tempvals(11), "myy")
    dStart = a(i, 10)
    dEnd = a(i, 11)
    Do
      d1 = dStart
      If myStart = myEnd Then
        d2 = dEnd
      Else
        d2 = DateAdd("m", 1, dStart) - Day(DateAdd("m", 1, dStart))
      End If
      If tempvals(7) >= 1 Then
        tempvals(7) = WorksheetFunction.NetworkDays(d1, d2, Range("Hols[Holidays]"))
        tempvals(8) = Round(tempvals(7) / a(i, 7) * a(i, 8), 2)
        tempvals(9) = IIf(a(i, 9) < 1, a(i, 9), d2 - d1 + 1)
      End If
      tempvals(10) = d1
      tempvals(11) = d2
      dStart = d2 + 1
      myStart = Format(dStart, "myy")
      k = k + 1
      For j = 1 To UBound(a, 2)
        Select Case j
          Case Is < 7, Is > 11: b(k, j) = a(i, j)
          Case Else: b(k, j) = tempvals(j)
        End Select
      Next j
    Loop Until dStart > dEnd
  Next i
  On Error Resume Next
  Set wsDest = Sheets("Sheet2")
  On Error GoTo 0
  Application.ScreenUpdating = False
  If wsDest Is Nothing Then
    Sheets.Add(After:=Sheets("Sheet1")).Name = "Sheet2"
    Set wsDest = Sheets("Sheet2")
    Sheets("Sheet1").Range("A1:V1").Copy Destination:=wsDest.Range("A1")
  End If
  With wsDest.UsedRange.Offset(1)
    .ClearContents
    With .Resize(k, UBound(b, 2))
      .Columns(3).NumberFormat = "@"
      .Columns(7).Resize(, 3).NumberFormat = "0.00"
      .Columns(12).Resize(, 2).NumberFormat = "hh:mm:ss"
      .Value = b
      .EntireColumn.AutoFit
    End With
  End With
  wsDest.Activate
  Application.ScreenUpdating = True
End Sub
 

Doylezeebeast

New Member
Joined
Nov 19, 2020
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
Hi Peter, thanks for amending the code again to take in account these "0"s employees.
If that is possible then surely those two dates could possibly be the last day in one month and the first day in the next month
Yes you are quite correct, this is an oversight of mine, sorry. It's very unlikely to ever happen, but it is of course possible that it could, so the code would need to be able to take account of it should it ever occur.

Perhaps I've misunderstood your message. I made a test for an employee, using dates of 31/1/19 to 1/2/19 (for this "0") and it was not quite what I expected to see, for example:

Data:
Sickness with code from 25.11.20 0 example.xlsm
ABCDEFGHIJK
1Personnel numberName of Employee or ApplicantAtt./Absence typeAtt./abs. type textDesc. of illnessDesc. of illnessAtt./abs. daysAbsence hoursCalendar daysStart DateEnd Date
29263824Employee Name 10230Sickness Abs paid 100%G0200Infection - various0.000.002.0031/01/201901/02/2019
Sheet1


Output:
Sickness with code from 25.11.20 0 example.xlsm
ABCDEFGHIJK
1Personnel numberName of Employee or ApplicantAtt./Absence typeAtt./abs. type textDesc. of illnessDesc. of illnessAtt./abs. daysAbsence hoursCalendar daysStart DateEnd Date
29263824Employee Name 10230Sickness Abs paid 100%G0200Infection - various0.000.002.0031/01/201931/01/2019
39263824Employee Name 10230Sickness Abs paid 100%G0200Infection - various0.000.002.0001/02/201901/02/2019
Sheet2


Expected Output:
Sickness with code from 25.11.20 0 example.xlsm
ABCDEFGHIJK
1Personnel numberName of Employee or ApplicantAtt./Absence typeAtt./abs. type textDesc. of illnessDesc. of illnessAtt./abs. daysAbsence hoursCalendar daysStart DateEnd Date
29263824Employee Name 10230Sickness Abs paid 100%G0200Infection - various0.000.001.0031/01/201931/01/2019
39263824Employee Name 10230Sickness Abs paid 100%G0200Infection - various0.000.001.0001/02/201901/02/2019
Sheet4


I also then made a test using my "real" data. It seems to have produced some unexpected results. In my real data there are only two employees who have a "0". But after the split there is then 10 employees who have a "0". It also then seems to have "lost" some of there data. Please see data below:

Employees Concerned before split

Sickness with code from 25.11.20 queries with 0.xlsm
ABCDEFGHIJK
1Personnel numberName of Employee or ApplicantAtt./Absence typeAtt./abs. type textDesc. of illnessDesc. of illnessAtt./abs. daysAbsence hoursCalendar daysStart DateEnd Date
29256344T A C0205*SSP IllnessG0026Cancer related59.00377.0088.0001/11/201927/01/2020
39256344T A C0220*SSP Sickness Abs p100%G0011Asthma/Pneumonia29.00185.0042.0001/01/201911/02/2019
49256344T A C0220*SSP Sickness Abs p100%G0026Cancer related44.00281.0062.0030/06/201930/08/2019
59256344T A C0225*SSP Sickness Abs p50%G0026Cancer related44.00282.5061.0001/09/201931/10/2019
69258744R R0220*SSP Sickness Abs p100%G0018Surgery/Operation39.00312.0055.0006/02/201901/04/2019
79258744R R0220*SSP Sickness Abs p100%G0144Post Op/Recovery40.00320.0062.0002/04/201902/06/2019
89258744R R0225*SSP Sickness Abs p50%G0144Post Op/Recovery5.0040.007.0012/08/201918/08/2019
99258744R R0225*SSP Sickness Abs p50%G0144Post Op/Recovery39.00312.0053.0010/09/201901/11/2019
109259822A J S0200Unpaid Sickness AbsenceG0060Eye Injury1.008.001.0022/12/201922/12/2019
119260571P C G0205*SSP IllnessG0004Muscle/Ligament Inj56.00448.0084.0011/03/201902/06/2019
129261525J A H0200Unpaid Sickness AbsenceG0006Stress/Depression2.0016.002.0028/02/201901/03/2019
139261525J A H0200Unpaid Sickness AbsenceG0006Stress/Depression2.0016.002.0011/07/201912/07/2019
149261525J A H0200Unpaid Sickness AbsenceG0006Stress/Depression11.0077.0020.0013/12/201901/01/2020
159261525J A H0205*SSP IllnessG0006Stress/Depression14.00112.0020.0030/05/201918/06/2019
169261525J A H0205*SSP IllnessG0006Stress/Depression20.00160.0028.0029/07/201925/08/2019
179261525J A H0205*SSP IllnessG0006Stress/Depression68.00535.0094.0010/09/201912/12/2019
189263352G L0200Unpaid Sickness AbsenceG0097Injury (Not Work)1.008.001.0024/04/201924/04/2019
199263352G L0200Unpaid Sickness AbsenceG0049Sickness/Diarrhoea2.0016.002.0008/07/201909/07/2019
209263352G L0200Unpaid Sickness AbsenceG0049Sickness/Diarrhoea1.008.001.0029/07/201929/07/2019
219263352G L0200Unpaid Sickness AbsenceG0049Sickness/Diarrhoea0.635.000.0015/08/201915/08/2019
229263352G L0205*SSP IllnessG0049Sickness/Diarrhoea0.504.000.0028/08/201928/08/2019
239263352G L0205*SSP IllnessG0049Sickness/Diarrhoea2.0015.004.0029/08/201901/09/2019
249263594J C0230Sickness Abs paid 100%G0056Endemetrosis3.0024.005.0029/05/201902/06/2019
259263824T L G0230Sickness Abs paid 100%G0200Infection - various0.000.002.0002/01/201903/01/2019
269263996M J T0220*SSP Sickness Abs p100%G0006Stress/Depression12.00132.0021.0029/10/201918/11/2019
279263996M J T0230Sickness Abs paid 100%G0049Sickness/Diarrhoea0.000.001.0020/01/201920/01/2019
289263996M J T0230Sickness Abs paid 100%G0049Sickness/Diarrhoea1.0011.003.0020/07/201922/07/2019
299264522R D0230Sickness Abs paid 100%G0049Sickness/Diarrhoea2.0016.002.0015/01/201916/01/2019
309264522R D0230Sickness Abs paid 100%G0034Cough/Cold/Flu2.0016.002.0006/03/201907/03/2019
319264522R D0230Sickness Abs paid 100%G0097Injury (Not Work)1.008.001.0015/04/201915/04/2019
329264522R D0230Sickness Abs paid 100%G0002Allergy/Reaction3.0024.005.0027/11/201901/12/2019
Sheet 1 data for EEs with 0 iss


Employees concerned after split (just 0 data):
Sickness with code from 25.11.20 queries with 0.xlsm
ABCDEFGHIJK
1Personnel numberName of Employee or ApplicantAtt./Absence typeAtt./abs. type textDesc. of illnessDesc. of illnessAtt./abs. daysAbsence hoursCalendar daysStart DateEnd Date
29256344T A C0220*SSP Sickness Abs p100%G0026Cancer related0.000.001.0030/06/201930/06/2019
39256344T A C0220*SSP Sickness Abs p100%G0026Cancer related0.000.001.0001/07/201931/07/2019
49256344T A C0220*SSP Sickness Abs p100%G0026Cancer related0.000.001.0001/08/201930/08/2019
59258744R R0220*SSP Sickness Abs p100%G0144Post Op/Recovery0.000.002.0001/06/201902/06/2019
69259822A J S0200Unpaid Sickness AbsenceG0060Eye Injury0.000.001.0022/12/201922/12/2019
79260571P C G0205*SSP IllnessG0004Muscle/Ligament Inj0.000.002.0001/06/201902/06/2019
89261525J A H0200Unpaid Sickness AbsenceG0006Stress/Depression0.000.001.0001/01/202001/01/2020
99263352G L0205*SSP IllnessG0049Sickness/Diarrhoea0.000.001.0001/09/201901/09/2019
109263594J C0230Sickness Abs paid 100%G0056Endemetrosis0.000.002.0001/06/201902/06/2019
119263824T L G0230Sickness Abs paid 100%G0200Infection - various0.000.002.0002/01/201903/01/2019
129263996M J T0230Sickness Abs paid 100%G0049Sickness/Diarrhoea0.000.001.0020/01/201920/01/2019
139264522R D0230Sickness Abs paid 100%G0002Allergy/Reaction0.000.001.0001/12/201901/12/2019
Sheet 2 after split 0s


Employees concerned after split, all of their data:
Sickness with code from 25.11.20 queries with 0.xlsm
ABCDEFGHIJK
29256344T A C0205*SSP IllnessG0026Cancer related21.00134.1930.0001/11/201930/11/2019
39256344T A C0205*SSP IllnessG0026Cancer related20.00127.8031.0001/12/201931/12/2019
49256344T A C0205*SSP IllnessG0026Cancer related18.00115.0227.0001/01/202027/01/2020
59256344T A C0220*SSP Sickness Abs p100%G0011Asthma/Pneumonia22.00140.3431.0001/01/201931/01/2019
69256344T A C0220*SSP Sickness Abs p100%G0011Asthma/Pneumonia7.0044.6611.0001/02/201911/02/2019
79256344T A C0220*SSP Sickness Abs p100%G0026Cancer related0.000.001.0030/06/201930/06/2019
89256344T A C0220*SSP Sickness Abs p100%G0026Cancer related0.000.001.0001/07/201931/07/2019
99256344T A C0220*SSP Sickness Abs p100%G0026Cancer related0.000.001.0001/08/201930/08/2019
109256344T A C0225*SSP Sickness Abs p50%G0026Cancer related21.00134.8330.0001/09/201930/09/2019
119256344T A C0225*SSP Sickness Abs p50%G0026Cancer related23.00147.6731.0001/10/201931/10/2019
129258744R R0220*SSP Sickness Abs p100%G0018Surgery/Operation17.00136.0023.0006/02/201928/02/2019
139258744R R0220*SSP Sickness Abs p100%G0018Surgery/Operation21.00168.0031.0001/03/201931/03/2019
149258744R R0220*SSP Sickness Abs p100%G0018Surgery/Operation1.008.001.0001/04/201901/04/2019
159258744R R0220*SSP Sickness Abs p100%G0144Post Op/Recovery19.00152.0029.0002/04/201930/04/2019
169258744R R0220*SSP Sickness Abs p100%G0144Post Op/Recovery21.00168.0031.0001/05/201931/05/2019
179258744R R0220*SSP Sickness Abs p100%G0144Post Op/Recovery0.000.002.0001/06/201902/06/2019
189258744R R0225*SSP Sickness Abs p50%G0144Post Op/Recovery5.0040.007.0012/08/201918/08/2019
199258744R R0225*SSP Sickness Abs p50%G0144Post Op/Recovery15.00120.0021.0010/09/201930/09/2019
209258744R R0225*SSP Sickness Abs p50%G0144Post Op/Recovery23.00184.0031.0001/10/201931/10/2019
219258744R R0225*SSP Sickness Abs p50%G0144Post Op/Recovery1.008.001.0001/11/201901/11/2019
229259822A J S0200Unpaid Sickness AbsenceG0060Eye Injury0.000.001.0022/12/201922/12/2019
239260571P C G0205*SSP IllnessG0004Muscle/Ligament Inj15.00120.0021.0011/03/201931/03/2019
249260571P C G0205*SSP IllnessG0004Muscle/Ligament Inj20.00160.0030.0001/04/201930/04/2019
259260571P C G0205*SSP IllnessG0004Muscle/Ligament Inj21.00168.0031.0001/05/201931/05/2019
269260571P C G0205*SSP IllnessG0004Muscle/Ligament Inj0.000.002.0001/06/201902/06/2019
279261525J A H0200Unpaid Sickness AbsenceG0006Stress/Depression1.008.001.0028/02/201928/02/2019
289261525J A H0200Unpaid Sickness AbsenceG0006Stress/Depression1.008.001.0001/03/201901/03/2019
299261525J A H0200Unpaid Sickness AbsenceG0006Stress/Depression2.0016.002.0011/07/201912/07/2019
309261525J A H0200Unpaid Sickness AbsenceG0006Stress/Depression11.0077.0019.0013/12/201931/12/2019
319261525J A H0200Unpaid Sickness AbsenceG0006Stress/Depression0.000.001.0001/01/202001/01/2020
329261525J A H0205*SSP IllnessG0006Stress/Depression2.0016.002.0030/05/201931/05/2019
339261525J A H0205*SSP IllnessG0006Stress/Depression12.0096.0018.0001/06/201918/06/2019
349261525J A H0205*SSP IllnessG0006Stress/Depression3.0024.003.0029/07/201931/07/2019
359261525J A H0205*SSP IllnessG0006Stress/Depression17.00136.0025.0001/08/201925/08/2019
369261525J A H0205*SSP IllnessG0006Stress/Depression15.00118.0121.0010/09/201930/09/2019
379261525J A H0205*SSP IllnessG0006Stress/Depression23.00180.9631.0001/10/201931/10/2019
389261525J A H0205*SSP IllnessG0006Stress/Depression21.00165.2230.0001/11/201930/11/2019
399261525J A H0205*SSP IllnessG0006Stress/Depression9.0070.8112.0001/12/201912/12/2019
409263352G L0200Unpaid Sickness AbsenceG0097Injury (Not Work)1.008.001.0024/04/201924/04/2019
419263352G L0200Unpaid Sickness AbsenceG0049Sickness/Diarrhoea2.0016.002.0008/07/201909/07/2019
429263352G L0200Unpaid Sickness AbsenceG0049Sickness/Diarrhoea1.008.001.0029/07/201929/07/2019
439263352G L0200Unpaid Sickness AbsenceG0049Sickness/Diarrhoea0.635.000.0015/08/201915/08/2019
449263352G L0205*SSP IllnessG0049Sickness/Diarrhoea0.504.000.0028/08/201928/08/2019
459263352G L0205*SSP IllnessG0049Sickness/Diarrhoea2.0015.003.0029/08/201931/08/2019
469263352G L0205*SSP IllnessG0049Sickness/Diarrhoea0.000.001.0001/09/201901/09/2019
479263594J C0230Sickness Abs paid 100%G0056Endemetrosis3.0024.003.0029/05/201931/05/2019
489263594J C0230Sickness Abs paid 100%G0056Endemetrosis0.000.002.0001/06/201902/06/2019
499263824T L G0230Sickness Abs paid 100%G0200Infection - various0.000.002.0002/01/201903/01/2019
509263996M J T0220*SSP Sickness Abs p100%G0006Stress/Depression3.0033.003.0029/10/201931/10/2019
519263996M J T0220*SSP Sickness Abs p100%G0006Stress/Depression12.00132.0018.0001/11/201918/11/2019
529263996M J T0230Sickness Abs paid 100%G0049Sickness/Diarrhoea0.000.001.0020/01/201920/01/2019
539263996M J T0230Sickness Abs paid 100%G0049Sickness/Diarrhoea1.0011.003.0020/07/201922/07/2019
549264522R D0230Sickness Abs paid 100%G0049Sickness/Diarrhoea2.0016.002.0015/01/201916/01/2019
559264522R D0230Sickness Abs paid 100%G0034Cough/Cold/Flu2.0016.002.0006/03/201907/03/2019
569264522R D0230Sickness Abs paid 100%G0097Injury (Not Work)1.008.001.0015/04/201915/04/2019
579264522R D0230Sickness Abs paid 100%G0002Allergy/Reaction3.0024.004.0027/11/201930/11/2019
589264522R D0230Sickness Abs paid 100%G0002Allergy/Reaction0.000.001.0001/12/201901/12/2019
Sheet 2 after sp EEs


Thank you for your help.
 
Last edited:

Doylezeebeast

New Member
Joined
Nov 19, 2020
Messages
13
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Hi Peter I was just taking another look at the "split" data. one of these "0" employees is personnel number 9256344 (in the pervious post to). Where it has split all the dates correctly for the absences. But for the absence 30/6/19 - 30/8/19 it has these "0" in it, so not quite sure what is happening?

Split data:

Sickness with code from 25.11.20 queries with 0.xlsm
ABCDEFGHIJK
1Personnel numberName of Employee or ApplicantAtt./Absence typeAtt./abs. type textDesc. of illnessDesc. of illnessAtt./abs. daysAbsence hoursCalendar daysStart DateEnd Date
29256344T A C0205*SSP IllnessG0026Cancer related21.00134.1930.0001/11/201930/11/2019
39256344T A C0205*SSP IllnessG0026Cancer related20.00127.8031.0001/12/201931/12/2019
49256344T A C0205*SSP IllnessG0026Cancer related18.00115.0227.0001/01/202027/01/2020
59256344T A C0220*SSP Sickness Abs p100%G0011Asthma/Pneumonia22.00140.3431.0001/01/201931/01/2019
69256344T A C0220*SSP Sickness Abs p100%G0011Asthma/Pneumonia7.0044.6611.0001/02/201911/02/2019
79256344T A C0220*SSP Sickness Abs p100%G0026Cancer related0.000.001.0030/06/201930/06/2019
89256344T A C0220*SSP Sickness Abs p100%G0026Cancer related0.000.001.0001/07/201931/07/2019
99256344T A C0220*SSP Sickness Abs p100%G0026Cancer related0.000.001.0001/08/201930/08/2019
109256344T A C0225*SSP Sickness Abs p50%G0026Cancer related21.00134.8330.0001/09/201930/09/2019
119256344T A C0225*SSP Sickness Abs p50%G0026Cancer related23.00147.6731.0001/10/201931/10/2019
Sheet 2 after sp EEs

Thank you.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
But for the absence 30/6/19 - 30/8/19 it has these "0" in it, so not quite sure what is happening?
OK, that was a problem with the code. The first period for that section is 30/06/2019 - 30/06/2019 and that particular day was a Sunday so the number of work days missed is zero. Once my code established a zero in that column for an absence it kept zero in that column, which was a mistake.

I have tried to address that issue below, as well as now trying to split the calendar days for absences that span months and have a zero in col G. I am getting some slightly different results to you but hoping that is related to holidays again. If there are still issues, can you also post your list of holidays with XL2BB please?

VBA Code:
Sub SplitItUp_v4()
  Dim wsDest As Worksheet
  Dim a As Variant, b As Variant, tempvals(7 To 22) As Variant
  Dim i As Long, j As Long, k As Long
  Dim dStart As Date, dEnd As Date, d1 As Date, d2 As Date
  Dim myStart As String, myEnd As String
 
  a = Sheets("Sheet1").Range("A1").CurrentRegion.Value
  ReDim b(1 To Rows.Count, 1 To 22)
  For i = 2 To UBound(a)
    For j = 7 To 11
      tempvals(j) = a(i, j)
    Next j
    myStart = Format(tempvals(10), "mmyy")
    myEnd = Format(tempvals(11), "mmyy")
    dStart = a(i, 10)
    dEnd = a(i, 11)
    Do
      d1 = dStart
      If myStart = myEnd Then
        d2 = dEnd
      Else
        d2 = DateAdd("m", 1, dStart) - Day(DateAdd("m", 1, dStart))
      End If
      If a(i, 7) >= 1 Then
        tempvals(7) = WorksheetFunction.NetworkDays(d1, d2, Range("Hols[Holidays]"))
        tempvals(8) = Round(tempvals(7) / a(i, 7) * a(i, 8), 2)
      End If
      tempvals(9) = IIf(a(i, 9) < 1, a(i, 9), d2 - d1 + 1)
      tempvals(10) = d1
      tempvals(11) = d2
      dStart = d2 + 1
      myStart = Format(dStart, "mmyy")
      k = k + 1
      For j = 1 To UBound(a, 2)
        Select Case j
          Case Is < 7, Is > 11: b(k, j) = a(i, j)
          Case Else: b(k, j) = tempvals(j)
        End Select
      Next j
    Loop Until dStart > dEnd
  Next i
  On Error Resume Next
  Set wsDest = Sheets("Sheet2")
  On Error GoTo 0
  Application.ScreenUpdating = False
  If wsDest Is Nothing Then
    Sheets.Add(After:=Sheets("Sheet1")).Name = "Sheet2"
    Set wsDest = Sheets("Sheet2")
    Sheets("Sheet1").Range("A1:V1").Copy Destination:=wsDest.Range("A1")
  End If
  With wsDest.UsedRange.Offset(1)
    .ClearContents
    With .Resize(k, UBound(b, 2))
      .Columns(3).NumberFormat = "@"
      .Columns(7).Resize(, 3).NumberFormat = "0.00"
      .Columns(12).Resize(, 2).NumberFormat = "hh:mm:ss"
      .Value = b
      .EntireColumn.AutoFit
    End With
  End With
  wsDest.Activate
  Application.ScreenUpdating = True
End Sub
 
Solution

Doylezeebeast

New Member
Joined
Nov 19, 2020
Messages
13
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Hi Peter, I made some more tests, definitely getting there!

OK, that was a problem with the code. The first period for that section is 30/06/2019 - 30/06/2019 and that particular day was a Sunday so the number of work days missed is zero. Once my code established a zero in that column for an absence it kept zero in that column, which was a mistake.
Great, I can see this is working now.

I was making a check with my real data and I noticed that once the data was split the total number of days for column G (and H) had increased. This seems to be for employees who are part time (not that you would know this, nor is it in the data to say as such), and then it has recalculated the column G and H values once split. Please see the example below for an employee who works Monday to Thursday.

Also there was an employee who works a "rolling" shift type pattern. In their case the 22/12/19 (a Sunday) so the values given in the "unsplit" data is correct. But once split we then have "0" in column G and H.

Is it possible to address these issues? (I guess for the Monday to Thursday employee the split data might have to show something like, more column G 2.4 29/10/19 - 31/10/19 and then 9.6 for 1/11/19 - 18/11/19? as you would not in the data what the working patterns are) - Sadly no way for me to include this in the "Real" data either.

I've also put the list of the bank holidays that i have in my Sheet3.

v4 code queries examples for forum.xlsx
ABCDEFGHIJK
1Personnel numberName of Employee or ApplicantAtt./Absence typeAtt./abs. type textDesc. of illnessDesc. of illnessAtt./abs. daysAbsence hoursCalendar daysStart DateEnd Date
2Before
39259822A J S0200Unpaid Sickness AbsenceG0060Eye Injury1.008.001.0022/12/201922/12/2019
4
5After
69259822A J S0200Unpaid Sickness AbsenceG0060Eye Injury0.000.001.0022/12/201922/12/2019
7
8
9Before (Column G shows as 12 as this employee works Monday to Thursday)
109263996M J T0220*SSP Sickness Abs p100%G0006Stress/Depression12.00132.0021.0029/10/201918/11/2019
119263996M J T0230Sickness Abs paid 100%G0049Sickness/Diarrhoea0.000.001.0020/01/201920/01/2019
129263996M J T0230Sickness Abs paid 100%G0049Sickness/Diarrhoea1.0011.003.0020/07/201922/07/2019
13
14After
159263996M J T0220*SSP Sickness Abs p100%G0006Stress/Depression3.0033.003.0029/10/201931/10/2019
169263996M J T0220*SSP Sickness Abs p100%G0006Stress/Depression12.00132.0018.0001/11/201918/11/2019
179263996M J T0230Sickness Abs paid 100%G0049Sickness/Diarrhoea0.000.001.0020/01/201920/01/2019
189263996M J T0230Sickness Abs paid 100%G0049Sickness/Diarrhoea1.0011.003.0020/07/201922/07/2019
19
20Holidays
2101/01/2019
2219/04/2019
2322/04/2019
2406/05/2019
2527/05/2019
2626/08/2019
2725/12/2019
2826/12/2019
2901/01/2020
Sheet1

Thank you
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
I was making a check with my real data and I noticed that once the data was split the total number of days for column G (and H) had increased. This seems to be for employees who are part time (not that you would know this, nor is it in the data to say as such), and then it has recalculated the column G and H values once split. Please see the example below for an employee who works Monday to Thursday.

Also there was an employee who works a "rolling" shift type pattern. In their case the 22/12/19 (a Sunday) so the values given in the "unsplit" data is correct. But once split we then have "0" in column G and H.

Is it possible to address these issues?
I can't see how that could be incorporated, especially if the workday patterns for each employee are not given in the data**. The calculations that I have made for each month are Mom-Fri less listed holidays.
** Even if this was in the data, I doubt the calculations/code would be something to reasonably expect in a free public forum like this. :cautious:
 

Doylezeebeast

New Member
Joined
Nov 19, 2020
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
Hi Peter, ok thanks for saying, I wasn't sure if they was some way to do this. Thanks for saying how it is working to. Not being very familiar with VBA I wasn't sure how "easy" this all would be for someone to do/ help me with.

Thank you very much for all the time you have taken to get me to this stage. It is very much appreciated.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,846
Messages
5,627,235
Members
416,232
Latest member
Ash1432

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top