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 :)
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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.

VBA 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
ABCDEFGHIJK
1Personnel numberName of Employee or ApplicantAtt./Absence typeAtt./abs. type textDesc. of illnessDesc. of illnessAtt./abs. daysAbsence hoursCalendar daysStart DateEnd Date
29002285Ben Cooper220*SSP Sickness Abs p100%G0049Sickness/Diarrhoea1617/11/20197/11/2019
39002362Steven Smith220*SSP Sickness Abs p100%G0049Cough/Cold/Flu0.554010/05/201910/05/2019
49002565Matt Damon230Sickness Abs paid 100%G0034Cough/Cold/Flu0.53.7507/02/20197/02/2019
59002565Matt Damon230Sickness Abs paid 100%G0034Cough/Cold/Flu17.518/02/20198/02/2019
69002904Stephen Robinson220*SSP Sickness Abs p100%G0049Sickness/Diarrhoea32132/01/20194/01/2019
79002905John Carper220*SSP Sickness Abs p100%G0006Stress/Depression12901616/07/201931/07/2019
89002905John Carper220*SSP Sickness Abs p100%G0006Stress/Depression967.5131/08/201913/08/2019
99031129Mark Johnson225*SSP Sickness Abs p50%G0012Bad Back17153239/10/201931/10/2019
109031129Mark Johnson225*SSP Sickness Abs p50%G0012Bad Back21189301/11/201930/11/2019
119031129Mark Johnson225*SSP Sickness Abs p50%G0012Bad Back22198311/12/201931/12/2019
129031129Mark Johnson225*SSP Sickness Abs p50%G0012Bad Back65481/01/20208/01/2020
Sheet2
 

Doylezeebeast

New Member
Joined
Nov 19, 2020
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
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 timeEnd timeAbs. due to accidentFull-dayCompany CodeCompany NamePersonnel areaPersonnel Area TextPayroll areaPayroll area textGender
00:00:0000:00:00X
5123​
Made up nameG999Another name hereY1SalariedM

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

New Member
Joined
Nov 19, 2020
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
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

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

ADVERTISEMENT

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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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
1606091047928.png


1606090949848.png



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

VBA Code:
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
ABCDEFGHIJKLMNOPQRSTUV
1Personnel numberName of Employee or ApplicantAtt./ Absence typeAtt./abs. type textDesc. of illnessDesc. of illnessAtt./abs. daysAbsence hoursCalendar daysStart DateEnd DateTime 1Time 2Col 14Col 15Col 16Col 17Col 18Col 19Col 20Col 21Col 22
29002285Ben Cooper0220*SSP Sickness Abs p100%G0049Sickness/Diarrhoea1617/11/20197/11/201901:43 PM11:47 PM642739173568726542
39002362Steven Smith0220*SSP Sickness Abs p100%G0049Cough/Cold/Flu0.554010/05/201910/05/201908:09 PM03:09 AM39391866994635215
49002565Matt Damon0230Sickness Abs paid 100%G0034Cough/Cold/Flu0.53.7507/02/20197/02/201905:06 AM09:11 AM863173373438587188
59002565Matt Damon0230Sickness Abs paid 100%G0034Cough/Cold/Flu17.518/02/20198/02/201909:19 PM09:51 AM19495731474498735
69002904Stephen Robinson0220*SSP Sickness Abs p100%G0049Sickness/Diarrhoea32132/01/20194/01/201902:40 PM04:16 AM616588877187551
79002905John Carper0220*SSP Sickness Abs p100%G0006Stress/Depression12901616/07/201931/07/201909:27 PM11:05 PM745287919921514531
89002905John Carper0220*SSP Sickness Abs p100%G0006Stress/Depression967.5131/08/201913/08/201909:27 PM11:05 PM745287919921514531
99031129Mark Johnson0225*SSP Sickness Abs p50%G0012Bad Back17153239/10/201931/10/201903:45 PM11:59 PM77601065208785973
109031129Mark Johnson0225*SSP Sickness Abs p50%G0012Bad Back21189301/11/201930/11/201903:45 PM11:59 PM77601065208785973
119031129Mark Johnson0225*SSP Sickness Abs p50%G0012Bad Back20180311/12/201931/12/201903:45 PM11:59 PM77601065208785973
129031129Mark Johnson0225*SSP Sickness Abs p50%G0012Bad Back54581/01/20208/01/202003:45 PM11:59 PM77601065208785973
Sheet2
 

Doylezeebeast

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

ADVERTISEMENT

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

New Member
Joined
Nov 19, 2020
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
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

New Member
Joined
Nov 19, 2020
Messages
13
Office Version
  1. 2016
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,400
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,172
Messages
5,629,126
Members
416,365
Latest member
dof

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