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

 

[Peter_SSs]

Welcome to the MrExcel board!

You could see if this is headed in the right direction but test with a copy of your workbook.
I don't get identical results to you as I do not have a list of holidays to factor in. Is there a list in the workbook somewhere? Since I don't have that list my Att./abs. days are just calculated on the number of Mon-Fri days in the relevant period.

I have assumed ..

• Sheet1 data is in columns A:K with nothing in column L and nothing below the last row of 'standard data' in the form shown in your post.
• Sheet2 exists already and has the headings but if there is other data below that it can be deleted to receive the results of this code.

Sub SplitItUp()
  Dim a As Variant, b As Variant, tempvals(7 To 11) 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 11)
  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
      tempvals(7) = IIf(a(i, 7) < 1, a(i, 7), WorksheetFunction.NetworkDays(d1, d2))
      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)
      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: b(k, j) = a(i, j)
          Case Else: b(k, j) = tempvals(j)
        End Select
      Next j
    Loop Until dStart > dEnd
  Next i
  With Sheets("Sheet2").UsedRange.Offset(1)
    .ClearContents
    .Resize(k, UBound(b, 2)).Value = b
  End With
End Sub

Here are my results with differences highlighted.

Doylezeebeast.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	9002285	Ben Cooper	220	*SSP Sickness Abs p100%	G0049	Sickness/Diarrhoea	1	6	1	7/11/2019	7/11/2019
3	9002362	Steven Smith	220	*SSP Sickness Abs p100%	G0049	Cough/Cold/Flu	0.55	4	0	10/05/2019	10/05/2019
4	9002565	Matt Damon	230	Sickness Abs paid 100%	G0034	Cough/Cold/Flu	0.5	3.75	0	7/02/2019	7/02/2019
5	9002565	Matt Damon	230	Sickness Abs paid 100%	G0034	Cough/Cold/Flu	1	7.5	1	8/02/2019	8/02/2019
6	9002904	Stephen Robinson	220	*SSP Sickness Abs p100%	G0049	Sickness/Diarrhoea	3	21	3	2/01/2019	4/01/2019
7	9002905	John Carper	220	*SSP Sickness Abs p100%	G0006	Stress/Depression	12	90	16	16/07/2019	31/07/2019
8	9002905	John Carper	220	*SSP Sickness Abs p100%	G0006	Stress/Depression	9	67.5	13	1/08/2019	13/08/2019
9	9031129	Mark Johnson	225	*SSP Sickness Abs p50%	G0012	Bad Back	17	153	23	9/10/2019	31/10/2019
10	9031129	Mark Johnson	225	*SSP Sickness Abs p50%	G0012	Bad Back	21	189	30	1/11/2019	30/11/2019
11	9031129	Mark Johnson	225	*SSP Sickness Abs p50%	G0012	Bad Back	22	198	31	1/12/2019	31/12/2019
12	9031129	Mark Johnson	225	*SSP Sickness Abs p50%	G0012	Bad Back	6	54	8	1/01/2020	8/01/2020
Sheet2

 

[Doylezeebeast]

Hi Peter, thanks for the warm welcome and the time you've taken to help me. It's much appreciated.

I made a test using the code with my small sample data (as is shown in my post) I noticed on Sheet2 that from column C "Att./Absence type" it has dropped the leading "0". So for example 0220 becomes, 220. Is there a way to keep the leading "0" on this split output?

In my "real" data, sheet1 the columns go through to "V" (wasn't sure if I could post on that onto the forum). But the data in columns L to V are just repeated data. For example:

Start time	End time	Abs. due to accident	Full-day	Company Code	Company Name	Personnel area	Personnel Area Text	Payroll area	Payroll area text	Gender
00:00:00	00:00:00		X	5123​	Made up name	G999	Another name here	Y1	Salaried	M

Column L and M are formatted as "Time" , Columns N to V are formatted as "General. Is it possible to include this data to?

For your assumption with Sheet2 this is all great. But please can I ask is it possible to, create sheet2 if it does not exist? Also to then copy the header row into this sheet, and then of course the "split" data?

Thank you for highlighting your differences and the reason why. I can see this is because the UK bank holidays are not taken into account (as well as the info form your explanation). In my data I don't have a list of the UK bank holidays. I was not sure if there was some clever formula/vba that can work this out when the code is run? If not I can create sheet in my workbook (perhaps Sheet3) and in column A list all UK bank holiday dates from 2010 to DATE (as my data only goes back to 2010) could this then be used in the code, what would you advise is best to do?

Thank you very much for your help with this. I think it is amazing what you have done for me.

 

[Doylezeebeast]

Hi Peter, sorry I should also said incase it makes a difference to the code. My "real" data can have around 10k rows to it. It does not matter if it takes a while to run though I can leave the PC to do it's thing. Thank you.

 

[Doylezeebeast]

Hi Peter. I just tried running this on a file that has around 3k lines of "real" data in it (but just to column K - I deleted column L to V). When I ran it it comes up with an error message that says:
Run-time error "6":
Overflow.

Thank you

 

[Peter_SSs]

Hi Peter. I just tried running this on a file that has around 3k lines of "real" data in it (but just to column K - I deleted column L to V). When I ran it it comes up with an error message that says:
Run-time error "6":
Overflow.

Which line of code is highlighted when you get the error and click Debug?

column C "Att./Absence type" it has dropped the leading "0"

Yes, I noticed that too & can be easily fixed.

the columns go through to "V"

No problem (provided we get the above error sorted out. )

Column L and M are formatted as "Time"

No problem, but there are a number of time formats. I have chosen one below but easily changed if required.

For your assumption with Sheet2 this is all great. But please can I ask is it possible to, create sheet2 if it does not exist? Also to then copy the header row into this sheet, and then of course the "split" data?

Shouldn't be a problem - addressed in the code below.

If not I can create sheet in my workbook (perhaps Sheet3) and in column A list all UK bank holiday dates

Yes, do that, with a heading of Holidays. Then make that heading & data into a formal Excel table (Select the data -> Insert ribbon tab -> Table -> My table has headers)
Then go to Formulas ribbon tab -> Name Manager -> select the table row if not already -> Edit... -> Give it a name of Hols -> OK -> Close
Here is mine

Updated code to address the above issues (still pending the error problem resolution)

Sub SplitItUp_v2()
  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
      tempvals(7) = IIf(a(i, 7) < 1, a(i, 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)
      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
  If wsDest Is Nothing Then
    Sheets.Add(After:=Sheets("Sheet1")).Name = "Sheet2"
    Set wsDest = Sheets("Sheet2")
    wsDest.Range("A1:V1").Value = Application.Index(a, 1, 0)
  End If
  With Sheets("Sheet2").UsedRange.Offset(1)
    .ClearContents
    With .Resize(k, UBound(b, 2))
      .Columns(3).NumberFormat = "@"
      .Columns(12).Resize(, 2).NumberFormat = "hh:mm AM/PM" '<- Check/edit this time formatting
      .Value = b
      .EntireColumn.AutoFit
    End With
  End With
End Sub

My updated results, given the 3 holiday dates in my table above & some dummy data in columns L:V.

Doylezeebeast.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
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	Time 1	Time 2	Col 14	Col 15	Col 16	Col 17	Col 18	Col 19	Col 20	Col 21	Col 22
2	9002285	Ben Cooper	0220	*SSP Sickness Abs p100%	G0049	Sickness/Diarrhoea	1	6	1	7/11/2019	7/11/2019	01:43 PM	11:47 PM	64	27	39	17	35	68	72	65	42
3	9002362	Steven Smith	0220	*SSP Sickness Abs p100%	G0049	Cough/Cold/Flu	0.55	4	0	10/05/2019	10/05/2019	08:09 PM	03:09 AM	39	39	1	86	69	94	63	52	15
4	9002565	Matt Damon	0230	Sickness Abs paid 100%	G0034	Cough/Cold/Flu	0.5	3.75	0	7/02/2019	7/02/2019	05:06 AM	09:11 AM	86	31	73	37	34	38	58	71	88
5	9002565	Matt Damon	0230	Sickness Abs paid 100%	G0034	Cough/Cold/Flu	1	7.5	1	8/02/2019	8/02/2019	09:19 PM	09:51 AM	19	49	57	31	47	44	9	87	35
6	9002904	Stephen Robinson	0220	*SSP Sickness Abs p100%	G0049	Sickness/Diarrhoea	3	21	3	2/01/2019	4/01/2019	02:40 PM	04:16 AM	6	16	58	8	87	71	87	5	51
7	9002905	John Carper	0220	*SSP Sickness Abs p100%	G0006	Stress/Depression	12	90	16	16/07/2019	31/07/2019	09:27 PM	11:05 PM	74	52	87	91	99	21	51	45	31
8	9002905	John Carper	0220	*SSP Sickness Abs p100%	G0006	Stress/Depression	9	67.5	13	1/08/2019	13/08/2019	09:27 PM	11:05 PM	74	52	87	91	99	21	51	45	31
9	9031129	Mark Johnson	0225	*SSP Sickness Abs p50%	G0012	Bad Back	17	153	23	9/10/2019	31/10/2019	03:45 PM	11:59 PM	77	60	10	65	20	8	78	59	73
10	9031129	Mark Johnson	0225	*SSP Sickness Abs p50%	G0012	Bad Back	21	189	30	1/11/2019	30/11/2019	03:45 PM	11:59 PM	77	60	10	65	20	8	78	59	73
11	9031129	Mark Johnson	0225	*SSP Sickness Abs p50%	G0012	Bad Back	20	180	31	1/12/2019	31/12/2019	03:45 PM	11:59 PM	77	60	10	65	20	8	78	59	73
12	9031129	Mark Johnson	0225	*SSP Sickness Abs p50%	G0012	Bad Back	5	45	8	1/01/2020	8/01/2020	03:45 PM	11:59 PM	77	60	10	65	20	8	78	59	73
Sheet2

 

[Doylezeebeast]

Hi Peter, thanks for your comprehensive reply on this, it is very helpful.

When getting the error and clicking "Debug" it is the line that says: tempvals (8) = Round(tempvals(7) / a(i, 7) * a(i, 8), 2) . I also tried my real data (circa 3k lines) with the new VBA code (with data to column V) and had the same error message and the same line of code as the issue.

Using my sample data and creating the holidays on Sheet3 as you instructed I was able to create the output given in your post so that's great. It just seems some issue with doing a lot of data?

Can I ask it is possible. The header row in my sheet1 data has a background colour of "Grey", is it possible to create this in Sheet2 as well? (if that's a big thing then it's quite ok to leave as it)

Thank you for updating the code so that it includes the leading "0" for column C data.

With columns G, H and I, these are formatted as "Number", when the data is split these are then changed to "General", is it possible to have them as "Number" please?

With the time format my times are as hours:minutes:seconds, (24 hour clock) I think for VBA this would then be "HH:MM:SS" ? (I just tried to look myself for my learning - please let me know if it's correct or not!?)

Thank you for your help.

 

[Doylezeebeast]

Hi Peter. I was just looking at my "real" data (circa 3k rows) and trying to see/understand what might make the code not work (not that I have enough knowledge to understand the code!) But was checking my data for anything that might cause an error.

I noticed for two of my employees in column G (Att./abs days) and column H (absence hours) there are both "0". But in column I (Calendar days) there are a number of days shown for them. The "0" are caused by an issue with the setup of these employees in the HR software. (where absences have been recorded on days that they dont work according to their work schedule in the system)

I wondered if because with having "0" being divided by a "0" (I think that's what's happening in the code?) it then causes an issue? I don't know if this helps or is a red herring but just thought I'd try and see.
Thank you.

 

[Doylezeebeast]

Hi Peter, I just made a test using the latest VBA code and removing the two offending employees. This then worked! Is there a way to make the code still work with these "0" employees in it? (is this called "error handling?")

Thank you.

 

[Peter_SSs]

being divided by a "0"

That is exactly the issue that I was expecting re the error.

Can you post some sample data (only really needs to be columns B and G:K) that shows what those rows are like when column G is zero, and also post the expected results?

In particular I am interested to see what is (or might be) in columns H & I in the raw data and in the results since column G seems to be used when splitting data across 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?
If these things are possible I would like to see data and results for

• an employee who has 0 in col G and exactly the same date in J:K
• an employee who has 0 in col G and different dates in J:K but those dates are in the same month
• an employee who has 0 in col G and different dates in J:K and those dates are in different months

We should be able to address this issue and all the other issues that you raised.