Major Aly
New Member
 Joined
 Mar 3, 2021
 Messages
 8
 Office Version

 2019
 Platform

 Windows
Hello All,
After searching extensively for a few days, I came across this thread: Calculate only working hours between two dates excluding weekends, and the formula presented by Mr. Barry Houdini, was modified by me to suit my needs. This modified formula itself works when applied into a cell with variables but I would like to include it in my VBA code as well.
I have written some VBA codes recently but would rate my experience in it as still new/fresh, despite having some coding background. I have searched extensively to see similar topics and implement the solutions there before asking my own question, but after 2 days of search/work, either I'm bad at searching or just couldn't find a solution similar to my own problem to implement.
I'm using Excel 2019.
I have a sort of RAW DATA that I get WEEKLY/MONTHLY and this RAW DATA contains anywhere from thousands to tens of thousands of rows, my VBA code sorts this RAW DATA by taking only what is needed. Now what I want to automate as well is to exclude nonworking hours from the 2 dates.
I tried macro recorder (as I do for multiple things to get a hint on how to implement stuff), but I'm kind of stuck on this one and hence requiring your expertise and knowledge on the matter.
The formula in question is:
My target is that there are no weekends for at all (hence using NetworkDays.Intl to custom set all as work days using "0000000"), and only set working hours (from 0800 to 2300) (8:00AM to 11:00PM), and any time after 11:01PM until 7:59AM is to be excluded from the total.
Here's my VBA Code for my approach of implementing the above formula:
This is what the macro recorder gives:
What I have tried:
I'm sure that I'm missing something such as the correct syntax of using the formula with variables or setting the formula itself to a cell/range, but I've racked my brain enough to seek out help and learn in the process.
After commenting out "On Error Resume Next", I'm running into an Runtime error: 1004, Applicationdefined or objectdefined error, on the line where my formula is placed.
Alternatively, if someone has a better solution for excluding workhours without using NetworkDays.Intl (because there are no weekends), I would appreciate it as well.
My sincere apologies if such a question has already been answered and my utmost gratitude for reading my post completely.
After searching extensively for a few days, I came across this thread: Calculate only working hours between two dates excluding weekends, and the formula presented by Mr. Barry Houdini, was modified by me to suit my needs. This modified formula itself works when applied into a cell with variables but I would like to include it in my VBA code as well.
I have written some VBA codes recently but would rate my experience in it as still new/fresh, despite having some coding background. I have searched extensively to see similar topics and implement the solutions there before asking my own question, but after 2 days of search/work, either I'm bad at searching or just couldn't find a solution similar to my own problem to implement.
I'm using Excel 2019.
I have a sort of RAW DATA that I get WEEKLY/MONTHLY and this RAW DATA contains anywhere from thousands to tens of thousands of rows, my VBA code sorts this RAW DATA by taking only what is needed. Now what I want to automate as well is to exclude nonworking hours from the 2 dates.
I tried macro recorder (as I do for multiple things to get a hint on how to implement stuff), but I'm kind of stuck on this one and hence requiring your expertise and knowledge on the matter.
The formula in question is:
Excel Formula:
=(NETWORKDAYS.INTL([@[DC_CREATION_DATE]],[@[ACTUAL_END_DATE]],""0000000"")1)*(upperlower)+IF(NETWORKDAYS.INTL([@[ACTUAL_END_DATE]],[@[ACTUAL_END_DATE]],""0000000""),MEDIAN(MOD([@[ACTUAL_END_DATE]],1),upper,lower),upper)MEDIAN(NETWORKDAYS.INTL([@[DC_CREATION_DATE]],[@[DC_CREATION_DATE]],""0000000"")*MOD([@[DC_CREATION_DATE]],1),upper,lower)"
My target is that there are no weekends for at all (hence using NetworkDays.Intl to custom set all as work days using "0000000"), and only set working hours (from 0800 to 2300) (8:00AM to 11:00PM), and any time after 11:01PM until 7:59AM is to be excluded from the total.
Here's my VBA Code for my approach of implementing the above formula:
VBA Code:
Sub RAWDATA_SORT()
Dim Main As Worksheet, Processed As Worksheet
Dim LastRow As Long, col As Long, k As Integer
Dim colName As String, maincolName As String
Dim i As Range
Dim Headers As Range, SearchHeaders As Range
Dim upper As Date, lower As Date, StartDate As Date, EndDate As Date
On Error Resume Next
Set Main = ActiveSheet
Main.Name = "RAW DATA"
Sheets.Add(After:=Sheets("RAW DATA")).Name = "Processed Data"
Set Processed = Sheets("Processed Data")
Main.Activate
Main.ShowAllData
Set Headers = Main.Range("1:1")
LastRow = 0
lower = Format(TimeValue("08:00 AM"), "hh:mm AMPM")
upper = Format(TimeValue("11:00 PM"), "hh:mm AMPM")
Debug.Print (lower)
Debug.Print (upper)
' More Code Here
With Processed
Processed.Activate
Processed.AutoFilterMode = False
Processed.ShowAllData
' More Code Here
LastRow = Processed.AutoFilter.Range.Columns(1).SpecialCells(xlCellTypeVisible).Cells.Count
k = 2
For Each i In Range("N2:N" & LastRow)
StartDate = Range("N" & k).Value
EndDate = Range("R" & k).Value
Debug.Print (StartDate)
Debug.Print (EndDate)
Range("U" & k).Value = DateDiff("s", Range("N" & k).Value, Range("R" & k).Value)
Range("V" & k).Value = "=(NETWORKDAYS.INTL([" & StartDate & "],[" & EndDate & "],""0000000"")1)*([" & upper & "] [" & lower & "])" _
& "+IF(NETWORKDAYS.INTL([" & EndDate & "],[" & EndDate & "],""0000000""),MEDIAN(MOD([" & EndDate & "],1),[" & upper & "],[" & lower & "]),[" & upper & "])" _
& "MEDIAN(NETWORKDAYS.INTL([" & StartDate & "],[" & StartDate & "],""0000000"")*MOD([" & StartDate & "],1),[" & upper & "],[" & lower & "])"
k = k + 1
Next i
Range("U:U").NumberFormat = "General"
End With
' Proceeding to End
This is what the macro recorder gives:
VBA Code:
ActiveCell.FormulaR1C1 = _
"=(NETWORKDAYS.INTL([@[DC_CREATION_DATE]],[@[ACTUAL_END_DATE]],""0000000"")1)*(upperlower)" & Chr(10) & "+IF(NETWORKDAYS.INTL([@[ACTUAL_END_DATE]],[@[ACTUAL_END_DATE]],""0000000""),MEDIAN(MOD([@[ACTUAL_END_DATE]],1),upper,lower),upper)" & Chr(10) & "MEDIAN(NETWORKDAYS.INTL([@[DC_CREATION_DATE]],[@[DC_CREATION_DATE]],""0000000"")*MOD([@[DC_CREATION_DATE]],1),upper,lower)"
What I have tried:
 Replacing Range("V" & k).Value with: Formula, FormulaR1C1, Formula2, Formula2R1C1
 Replace Range with Cells
 Tried using Application.WorksheetFunction.NetworkDays_Intl but I'm not experienced enough to translate the whole formula to code properly.
I'm sure that I'm missing something such as the correct syntax of using the formula with variables or setting the formula itself to a cell/range, but I've racked my brain enough to seek out help and learn in the process.
After commenting out "On Error Resume Next", I'm running into an Runtime error: 1004, Applicationdefined or objectdefined error, on the line where my formula is placed.
Alternatively, if someone has a better solution for excluding workhours without using NetworkDays.Intl (because there are no weekends), I would appreciate it as well.
My sincere apologies if such a question has already been answered and my utmost gratitude for reading my post completely.