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

[Doylezeebeast]

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 number	Name of Employee or Applicant	Att./Absence type	Att./abs. type text	Desc. of illness	Desc. of illness	Att./abs. days	Absence hours	Calendar days	Start Date	End Date
9002285​	Ben Cooper	0220	*SSP Sickness Abs p100%	G0049	Sickness/Diarrhoea	1.00	6.00	1.00	07/11/2019	07/11/2019
9002362​	Steven Smith	0220	*SSP Sickness Abs p100%	G0049	Cough/Cold/Flu	0.55	4.00	0.00	10/05/2019	10/05/2019
9002565​	Matt Damon	0230	Sickness Abs paid 100%	G0034	Cough/Cold/Flu	0.50	3.75	0.00	07/02/2019	07/02/2019
9002565​	Matt Damon	0230	Sickness Abs paid 100%	G0034	Cough/Cold/Flu	1.00	7.50	1.00	08/02/2019	08/02/2019
9002904​	Stephen Robinson	0220	*SSP Sickness Abs p100%	G0049	Sickness/Diarrhoea	3.00	21.00	3.00	02/01/2019	04/01/2019
9002905​	John Carper	0220	*SSP Sickness Abs p100%	G0006	Stress/Depression	21.00	157.50	29.00	16/07/2019	13/08/2019
9031129​	Mark Johnson	0225	*SSP Sickness Abs p50%	G0012	Bad Back	63.00	567.00	92.00	09/10/2019	08/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 number	Name of Employee or Applicant	Att./Absence type	Att./abs. type text	Desc. of illness	Desc. of illness	Att./abs. days	Absence hours	Calendar days	Start Date	End Date
9002285​	Ben Cooper	0220	*SSP Sickness Abs p100%	G0049	Sickness/Diarrhoea	1.00	6.00	1.00	07/11/2019	07/11/2019
9002362​	Steven Smith	0220	*SSP Sickness Abs p100%	G0049	Cough/Cold/Flu	0.55	4.00	0.00	10/05/2019	10/05/2019
9002565​	Matt Damon	0230	Sickness Abs paid 100%	G0034	Cough/Cold/Flu	0.50	3.75	0.00	07/02/2019	07/02/2019
9002565​	Matt Damon	0230	Sickness Abs paid 100%	G0034	Cough/Cold/Flu	1.00	7.50	1.00	08/02/2019	08/02/2019
9002904​	Stephen Robinson	0220	*SSP Sickness Abs p100%	G0049	Sickness/Diarrhoea	3.00	21.00	3.00	02/01/2019	04/01/2019
9002905​	John Carper	0220	*SSP Sickness Abs p100%	G0006	Stress/Depression	12.00	90.00	16.00	16/07/2019	31/07/2019
9002905​	John Carper	0220	*SSP Sickness Abs p100%	G0006	Stress/Depression	9.00	67.50	13.00	01/08/2019	13/08/2019
9031129​	Mark Johnson	0225	*SSP Sickness Abs p50%	G0012	Bad Back	17.00	153.00	23.00	09/10/2019	31/10/2019
9031129​	Mark Johnson	0225	*SSP Sickness Abs p50%	G0012	Bad Back	21.00	189.00	30.00	01/11/2019	30/11/2019
9031129​	Mark Johnson	0225	*SSP Sickness Abs p50%	G0012	Bad Back	20.00	180.00	31.00	01/12/2019	31/12/2019
9031129​	Mark Johnson	0225	*SSP Sickness Abs p50%	G0012	Bad Back	5.00	45.00	8.00	01/01/2020	08/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:

Excel VBA or Formula to Split Period of Months into Individual Month

Hi guys, I have a sheet with data similar to the following table: Name Location Start Date End Date Days John Canada January 1, 2013 February 24, 2013 55 Jack US February 18, 2013 March 23, 2013 34 Is it possible to create an Excel formula or VBA to split this set of...

www.mrexcel.com

Thanks for any help I can get on this one

 

[Doylezeebeast]

Hi Peter, please find below some sample data for columns B and G:K:

Name of Employee or Applicant	Att./abs. days	Absence hours	Calendar days	Start Date	End Date
Employee Name 1	0.00	0.00	2.00	02/01/2019	03/01/2019
Employee Name 2	0.00	0.00	1.00	20/01/2019	20/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.

 

[Peter_SSs]

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.

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]

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
	A	B	C	D	E	F	G	H	I	J	K
1	Personnel number	Name of Employee or Applicant	Att./Absence type	Att./abs. type text	Desc. of illness	Desc. of illness	Att./abs. days	Absence hours	Calendar days	Start Date	End Date
2	9263824	Employee Name 1	0230	Sickness Abs paid 100%	G0200	Infection - various	0.00	0.00	2.00	31/01/2019	01/02/2019
Sheet1

Output:

Sickness with code from 25.11.20 0 example.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Personnel number	Name of Employee or Applicant	Att./Absence type	Att./abs. type text	Desc. of illness	Desc. of illness	Att./abs. days	Absence hours	Calendar days	Start Date	End Date
2	9263824	Employee Name 1	0230	Sickness Abs paid 100%	G0200	Infection - various	0.00	0.00	2.00	31/01/2019	31/01/2019
3	9263824	Employee Name 1	0230	Sickness Abs paid 100%	G0200	Infection - various	0.00	0.00	2.00	01/02/2019	01/02/2019
Sheet2

Expected Output:

Sickness with code from 25.11.20 0 example.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	Personnel number	Name of Employee or Applicant	Att./Absence type	Att./abs. type text	Desc. of illness	Desc. of illness	Att./abs. days	Absence hours	Calendar days	Start Date	End Date
2	9263824	Employee Name 1	0230	Sickness Abs paid 100%	G0200	Infection - various	0.00	0.00	1.00	31/01/2019	31/01/2019
3	9263824	Employee Name 1	0230	Sickness Abs paid 100%	G0200	Infection - various	0.00	0.00	1.00	01/02/2019	01/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
	A	B	C	D	E	F	G	H	I	J	K
1	Personnel number	Name of Employee or Applicant	Att./Absence type	Att./abs. type text	Desc. of illness	Desc. of illness	Att./abs. days	Absence hours	Calendar days	Start Date	End Date
2	9256344	T A C	0205	*SSP Illness	G0026	Cancer related	59.00	377.00	88.00	01/11/2019	27/01/2020
3	9256344	T A C	0220	*SSP Sickness Abs p100%	G0011	Asthma/Pneumonia	29.00	185.00	42.00	01/01/2019	11/02/2019
4	9256344	T A C	0220	*SSP Sickness Abs p100%	G0026	Cancer related	44.00	281.00	62.00	30/06/2019	30/08/2019
5	9256344	T A C	0225	*SSP Sickness Abs p50%	G0026	Cancer related	44.00	282.50	61.00	01/09/2019	31/10/2019
6	9258744	R R	0220	*SSP Sickness Abs p100%	G0018	Surgery/Operation	39.00	312.00	55.00	06/02/2019	01/04/2019
7	9258744	R R	0220	*SSP Sickness Abs p100%	G0144	Post Op/Recovery	40.00	320.00	62.00	02/04/2019	02/06/2019
8	9258744	R R	0225	*SSP Sickness Abs p50%	G0144	Post Op/Recovery	5.00	40.00	7.00	12/08/2019	18/08/2019
9	9258744	R R	0225	*SSP Sickness Abs p50%	G0144	Post Op/Recovery	39.00	312.00	53.00	10/09/2019	01/11/2019
10	9259822	A J S	0200	Unpaid Sickness Absence	G0060	Eye Injury	1.00	8.00	1.00	22/12/2019	22/12/2019
11	9260571	P C G	0205	*SSP Illness	G0004	Muscle/Ligament Inj	56.00	448.00	84.00	11/03/2019	02/06/2019
12	9261525	J A H	0200	Unpaid Sickness Absence	G0006	Stress/Depression	2.00	16.00	2.00	28/02/2019	01/03/2019
13	9261525	J A H	0200	Unpaid Sickness Absence	G0006	Stress/Depression	2.00	16.00	2.00	11/07/2019	12/07/2019
14	9261525	J A H	0200	Unpaid Sickness Absence	G0006	Stress/Depression	11.00	77.00	20.00	13/12/2019	01/01/2020
15	9261525	J A H	0205	*SSP Illness	G0006	Stress/Depression	14.00	112.00	20.00	30/05/2019	18/06/2019
16	9261525	J A H	0205	*SSP Illness	G0006	Stress/Depression	20.00	160.00	28.00	29/07/2019	25/08/2019
17	9261525	J A H	0205	*SSP Illness	G0006	Stress/Depression	68.00	535.00	94.00	10/09/2019	12/12/2019
18	9263352	G L	0200	Unpaid Sickness Absence	G0097	Injury (Not Work)	1.00	8.00	1.00	24/04/2019	24/04/2019
19	9263352	G L	0200	Unpaid Sickness Absence	G0049	Sickness/Diarrhoea	2.00	16.00	2.00	08/07/2019	09/07/2019
20	9263352	G L	0200	Unpaid Sickness Absence	G0049	Sickness/Diarrhoea	1.00	8.00	1.00	29/07/2019	29/07/2019
21	9263352	G L	0200	Unpaid Sickness Absence	G0049	Sickness/Diarrhoea	0.63	5.00	0.00	15/08/2019	15/08/2019
22	9263352	G L	0205	*SSP Illness	G0049	Sickness/Diarrhoea	0.50	4.00	0.00	28/08/2019	28/08/2019
23	9263352	G L	0205	*SSP Illness	G0049	Sickness/Diarrhoea	2.00	15.00	4.00	29/08/2019	01/09/2019
24	9263594	J C	0230	Sickness Abs paid 100%	G0056	Endemetrosis	3.00	24.00	5.00	29/05/2019	02/06/2019
25	9263824	T L G	0230	Sickness Abs paid 100%	G0200	Infection - various	0.00	0.00	2.00	02/01/2019	03/01/2019
26	9263996	M J T	0220	*SSP Sickness Abs p100%	G0006	Stress/Depression	12.00	132.00	21.00	29/10/2019	18/11/2019
27	9263996	M J T	0230	Sickness Abs paid 100%	G0049	Sickness/Diarrhoea	0.00	0.00	1.00	20/01/2019	20/01/2019
28	9263996	M J T	0230	Sickness Abs paid 100%	G0049	Sickness/Diarrhoea	1.00	11.00	3.00	20/07/2019	22/07/2019
29	9264522	R D	0230	Sickness Abs paid 100%	G0049	Sickness/Diarrhoea	2.00	16.00	2.00	15/01/2019	16/01/2019
30	9264522	R D	0230	Sickness Abs paid 100%	G0034	Cough/Cold/Flu	2.00	16.00	2.00	06/03/2019	07/03/2019
31	9264522	R D	0230	Sickness Abs paid 100%	G0097	Injury (Not Work)	1.00	8.00	1.00	15/04/2019	15/04/2019
32	9264522	R D	0230	Sickness Abs paid 100%	G0002	Allergy/Reaction	3.00	24.00	5.00	27/11/2019	01/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
	A	B	C	D	E	F	G	H	I	J	K
1	Personnel number	Name of Employee or Applicant	Att./Absence type	Att./abs. type text	Desc. of illness	Desc. of illness	Att./abs. days	Absence hours	Calendar days	Start Date	End Date
2	9256344	T A C	0220	*SSP Sickness Abs p100%	G0026	Cancer related	0.00	0.00	1.00	30/06/2019	30/06/2019
3	9256344	T A C	0220	*SSP Sickness Abs p100%	G0026	Cancer related	0.00	0.00	1.00	01/07/2019	31/07/2019
4	9256344	T A C	0220	*SSP Sickness Abs p100%	G0026	Cancer related	0.00	0.00	1.00	01/08/2019	30/08/2019
5	9258744	R R	0220	*SSP Sickness Abs p100%	G0144	Post Op/Recovery	0.00	0.00	2.00	01/06/2019	02/06/2019
6	9259822	A J S	0200	Unpaid Sickness Absence	G0060	Eye Injury	0.00	0.00	1.00	22/12/2019	22/12/2019
7	9260571	P C G	0205	*SSP Illness	G0004	Muscle/Ligament Inj	0.00	0.00	2.00	01/06/2019	02/06/2019
8	9261525	J A H	0200	Unpaid Sickness Absence	G0006	Stress/Depression	0.00	0.00	1.00	01/01/2020	01/01/2020
9	9263352	G L	0205	*SSP Illness	G0049	Sickness/Diarrhoea	0.00	0.00	1.00	01/09/2019	01/09/2019
10	9263594	J C	0230	Sickness Abs paid 100%	G0056	Endemetrosis	0.00	0.00	2.00	01/06/2019	02/06/2019
11	9263824	T L G	0230	Sickness Abs paid 100%	G0200	Infection - various	0.00	0.00	2.00	02/01/2019	03/01/2019
12	9263996	M J T	0230	Sickness Abs paid 100%	G0049	Sickness/Diarrhoea	0.00	0.00	1.00	20/01/2019	20/01/2019
13	9264522	R D	0230	Sickness Abs paid 100%	G0002	Allergy/Reaction	0.00	0.00	1.00	01/12/2019	01/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
	A	B	C	D	E	F	G	H	I	J	K
2	9256344	T A C	0205	*SSP Illness	G0026	Cancer related	21.00	134.19	30.00	01/11/2019	30/11/2019
3	9256344	T A C	0205	*SSP Illness	G0026	Cancer related	20.00	127.80	31.00	01/12/2019	31/12/2019
4	9256344	T A C	0205	*SSP Illness	G0026	Cancer related	18.00	115.02	27.00	01/01/2020	27/01/2020
5	9256344	T A C	0220	*SSP Sickness Abs p100%	G0011	Asthma/Pneumonia	22.00	140.34	31.00	01/01/2019	31/01/2019
6	9256344	T A C	0220	*SSP Sickness Abs p100%	G0011	Asthma/Pneumonia	7.00	44.66	11.00	01/02/2019	11/02/2019
7	9256344	T A C	0220	*SSP Sickness Abs p100%	G0026	Cancer related	0.00	0.00	1.00	30/06/2019	30/06/2019
8	9256344	T A C	0220	*SSP Sickness Abs p100%	G0026	Cancer related	0.00	0.00	1.00	01/07/2019	31/07/2019
9	9256344	T A C	0220	*SSP Sickness Abs p100%	G0026	Cancer related	0.00	0.00	1.00	01/08/2019	30/08/2019
10	9256344	T A C	0225	*SSP Sickness Abs p50%	G0026	Cancer related	21.00	134.83	30.00	01/09/2019	30/09/2019
11	9256344	T A C	0225	*SSP Sickness Abs p50%	G0026	Cancer related	23.00	147.67	31.00	01/10/2019	31/10/2019
12	9258744	R R	0220	*SSP Sickness Abs p100%	G0018	Surgery/Operation	17.00	136.00	23.00	06/02/2019	28/02/2019
13	9258744	R R	0220	*SSP Sickness Abs p100%	G0018	Surgery/Operation	21.00	168.00	31.00	01/03/2019	31/03/2019
14	9258744	R R	0220	*SSP Sickness Abs p100%	G0018	Surgery/Operation	1.00	8.00	1.00	01/04/2019	01/04/2019
15	9258744	R R	0220	*SSP Sickness Abs p100%	G0144	Post Op/Recovery	19.00	152.00	29.00	02/04/2019	30/04/2019
16	9258744	R R	0220	*SSP Sickness Abs p100%	G0144	Post Op/Recovery	21.00	168.00	31.00	01/05/2019	31/05/2019
17	9258744	R R	0220	*SSP Sickness Abs p100%	G0144	Post Op/Recovery	0.00	0.00	2.00	01/06/2019	02/06/2019
18	9258744	R R	0225	*SSP Sickness Abs p50%	G0144	Post Op/Recovery	5.00	40.00	7.00	12/08/2019	18/08/2019
19	9258744	R R	0225	*SSP Sickness Abs p50%	G0144	Post Op/Recovery	15.00	120.00	21.00	10/09/2019	30/09/2019
20	9258744	R R	0225	*SSP Sickness Abs p50%	G0144	Post Op/Recovery	23.00	184.00	31.00	01/10/2019	31/10/2019
21	9258744	R R	0225	*SSP Sickness Abs p50%	G0144	Post Op/Recovery	1.00	8.00	1.00	01/11/2019	01/11/2019
22	9259822	A J S	0200	Unpaid Sickness Absence	G0060	Eye Injury	0.00	0.00	1.00	22/12/2019	22/12/2019
23	9260571	P C G	0205	*SSP Illness	G0004	Muscle/Ligament Inj	15.00	120.00	21.00	11/03/2019	31/03/2019
24	9260571	P C G	0205	*SSP Illness	G0004	Muscle/Ligament Inj	20.00	160.00	30.00	01/04/2019	30/04/2019
25	9260571	P C G	0205	*SSP Illness	G0004	Muscle/Ligament Inj	21.00	168.00	31.00	01/05/2019	31/05/2019
26	9260571	P C G	0205	*SSP Illness	G0004	Muscle/Ligament Inj	0.00	0.00	2.00	01/06/2019	02/06/2019
27	9261525	J A H	0200	Unpaid Sickness Absence	G0006	Stress/Depression	1.00	8.00	1.00	28/02/2019	28/02/2019
28	9261525	J A H	0200	Unpaid Sickness Absence	G0006	Stress/Depression	1.00	8.00	1.00	01/03/2019	01/03/2019
29	9261525	J A H	0200	Unpaid Sickness Absence	G0006	Stress/Depression	2.00	16.00	2.00	11/07/2019	12/07/2019
30	9261525	J A H	0200	Unpaid Sickness Absence	G0006	Stress/Depression	11.00	77.00	19.00	13/12/2019	31/12/2019
31	9261525	J A H	0200	Unpaid Sickness Absence	G0006	Stress/Depression	0.00	0.00	1.00	01/01/2020	01/01/2020
32	9261525	J A H	0205	*SSP Illness	G0006	Stress/Depression	2.00	16.00	2.00	30/05/2019	31/05/2019
33	9261525	J A H	0205	*SSP Illness	G0006	Stress/Depression	12.00	96.00	18.00	01/06/2019	18/06/2019
34	9261525	J A H	0205	*SSP Illness	G0006	Stress/Depression	3.00	24.00	3.00	29/07/2019	31/07/2019
35	9261525	J A H	0205	*SSP Illness	G0006	Stress/Depression	17.00	136.00	25.00	01/08/2019	25/08/2019
36	9261525	J A H	0205	*SSP Illness	G0006	Stress/Depression	15.00	118.01	21.00	10/09/2019	30/09/2019
37	9261525	J A H	0205	*SSP Illness	G0006	Stress/Depression	23.00	180.96	31.00	01/10/2019	31/10/2019
38	9261525	J A H	0205	*SSP Illness	G0006	Stress/Depression	21.00	165.22	30.00	01/11/2019	30/11/2019
39	9261525	J A H	0205	*SSP Illness	G0006	Stress/Depression	9.00	70.81	12.00	01/12/2019	12/12/2019
40	9263352	G L	0200	Unpaid Sickness Absence	G0097	Injury (Not Work)	1.00	8.00	1.00	24/04/2019	24/04/2019
41	9263352	G L	0200	Unpaid Sickness Absence	G0049	Sickness/Diarrhoea	2.00	16.00	2.00	08/07/2019	09/07/2019
42	9263352	G L	0200	Unpaid Sickness Absence	G0049	Sickness/Diarrhoea	1.00	8.00	1.00	29/07/2019	29/07/2019
43	9263352	G L	0200	Unpaid Sickness Absence	G0049	Sickness/Diarrhoea	0.63	5.00	0.00	15/08/2019	15/08/2019
44	9263352	G L	0205	*SSP Illness	G0049	Sickness/Diarrhoea	0.50	4.00	0.00	28/08/2019	28/08/2019
45	9263352	G L	0205	*SSP Illness	G0049	Sickness/Diarrhoea	2.00	15.00	3.00	29/08/2019	31/08/2019
46	9263352	G L	0205	*SSP Illness	G0049	Sickness/Diarrhoea	0.00	0.00	1.00	01/09/2019	01/09/2019
47	9263594	J C	0230	Sickness Abs paid 100%	G0056	Endemetrosis	3.00	24.00	3.00	29/05/2019	31/05/2019
48	9263594	J C	0230	Sickness Abs paid 100%	G0056	Endemetrosis	0.00	0.00	2.00	01/06/2019	02/06/2019
49	9263824	T L G	0230	Sickness Abs paid 100%	G0200	Infection - various	0.00	0.00	2.00	02/01/2019	03/01/2019
50	9263996	M J T	0220	*SSP Sickness Abs p100%	G0006	Stress/Depression	3.00	33.00	3.00	29/10/2019	31/10/2019
51	9263996	M J T	0220	*SSP Sickness Abs p100%	G0006	Stress/Depression	12.00	132.00	18.00	01/11/2019	18/11/2019
52	9263996	M J T	0230	Sickness Abs paid 100%	G0049	Sickness/Diarrhoea	0.00	0.00	1.00	20/01/2019	20/01/2019
53	9263996	M J T	0230	Sickness Abs paid 100%	G0049	Sickness/Diarrhoea	1.00	11.00	3.00	20/07/2019	22/07/2019
54	9264522	R D	0230	Sickness Abs paid 100%	G0049	Sickness/Diarrhoea	2.00	16.00	2.00	15/01/2019	16/01/2019
55	9264522	R D	0230	Sickness Abs paid 100%	G0034	Cough/Cold/Flu	2.00	16.00	2.00	06/03/2019	07/03/2019
56	9264522	R D	0230	Sickness Abs paid 100%	G0097	Injury (Not Work)	1.00	8.00	1.00	15/04/2019	15/04/2019
57	9264522	R D	0230	Sickness Abs paid 100%	G0002	Allergy/Reaction	3.00	24.00	4.00	27/11/2019	30/11/2019
58	9264522	R D	0230	Sickness Abs paid 100%	G0002	Allergy/Reaction	0.00	0.00	1.00	01/12/2019	01/12/2019
Sheet 2 after sp EEs

Thank you for your help.

 

[Doylezeebeast]

It also then seems to have "lost" some of there data.

Please ignore that part of the message. I was unable to amend the post to remove this as 10 minutes had passed. Thank you.

 

[Doylezeebeast]

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
	A	B	C	D	E	F	G	H	I	J	K
1	Personnel number	Name of Employee or Applicant	Att./Absence type	Att./abs. type text	Desc. of illness	Desc. of illness	Att./abs. days	Absence hours	Calendar days	Start Date	End Date
2	9256344	T A C	0205	*SSP Illness	G0026	Cancer related	21.00	134.19	30.00	01/11/2019	30/11/2019
3	9256344	T A C	0205	*SSP Illness	G0026	Cancer related	20.00	127.80	31.00	01/12/2019	31/12/2019
4	9256344	T A C	0205	*SSP Illness	G0026	Cancer related	18.00	115.02	27.00	01/01/2020	27/01/2020
5	9256344	T A C	0220	*SSP Sickness Abs p100%	G0011	Asthma/Pneumonia	22.00	140.34	31.00	01/01/2019	31/01/2019
6	9256344	T A C	0220	*SSP Sickness Abs p100%	G0011	Asthma/Pneumonia	7.00	44.66	11.00	01/02/2019	11/02/2019
7	9256344	T A C	0220	*SSP Sickness Abs p100%	G0026	Cancer related	0.00	0.00	1.00	30/06/2019	30/06/2019
8	9256344	T A C	0220	*SSP Sickness Abs p100%	G0026	Cancer related	0.00	0.00	1.00	01/07/2019	31/07/2019
9	9256344	T A C	0220	*SSP Sickness Abs p100%	G0026	Cancer related	0.00	0.00	1.00	01/08/2019	30/08/2019
10	9256344	T A C	0225	*SSP Sickness Abs p50%	G0026	Cancer related	21.00	134.83	30.00	01/09/2019	30/09/2019
11	9256344	T A C	0225	*SSP Sickness Abs p50%	G0026	Cancer related	23.00	147.67	31.00	01/10/2019	31/10/2019
Sheet 2 after sp EEs

Thank you.

 

[Peter_SSs]

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?

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

 

[Doylezeebeast]

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
	A	B	C	D	E	F	G	H	I	J	K
1	Personnel number	Name of Employee or Applicant	Att./Absence type	Att./abs. type text	Desc. of illness	Desc. of illness	Att./abs. days	Absence hours	Calendar days	Start Date	End Date
2	Before
3	9259822	A J S	0200	Unpaid Sickness Absence	G0060	Eye Injury	1.00	8.00	1.00	22/12/2019	22/12/2019
4
5	After
6	9259822	A J S	0200	Unpaid Sickness Absence	G0060	Eye Injury	0.00	0.00	1.00	22/12/2019	22/12/2019
7
8
9	Before (Column G shows as 12 as this employee works Monday to Thursday)
10	9263996	M J T	0220	*SSP Sickness Abs p100%	G0006	Stress/Depression	12.00	132.00	21.00	29/10/2019	18/11/2019
11	9263996	M J T	0230	Sickness Abs paid 100%	G0049	Sickness/Diarrhoea	0.00	0.00	1.00	20/01/2019	20/01/2019
12	9263996	M J T	0230	Sickness Abs paid 100%	G0049	Sickness/Diarrhoea	1.00	11.00	3.00	20/07/2019	22/07/2019
13
14	After
15	9263996	M J T	0220	*SSP Sickness Abs p100%	G0006	Stress/Depression	3.00	33.00	3.00	29/10/2019	31/10/2019
16	9263996	M J T	0220	*SSP Sickness Abs p100%	G0006	Stress/Depression	12.00	132.00	18.00	01/11/2019	18/11/2019
17	9263996	M J T	0230	Sickness Abs paid 100%	G0049	Sickness/Diarrhoea	0.00	0.00	1.00	20/01/2019	20/01/2019
18	9263996	M J T	0230	Sickness Abs paid 100%	G0049	Sickness/Diarrhoea	1.00	11.00	3.00	20/07/2019	22/07/2019
19
20	Holidays
21	01/01/2019
22	19/04/2019
23	22/04/2019
24	06/05/2019
25	27/05/2019
26	26/08/2019
27	25/12/2019
28	26/12/2019
29	01/01/2020
Sheet1

Thank you

 

[Peter_SSs]

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.

 

[Doylezeebeast]

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.

 

[Peter_SSs]

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

You're welcome. Sorry I wasn't able to get to the next step.