Converting/Using a complicated formula for excluding working hours in VBA

[Major Aly]

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 non-working 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:

=(NETWORKDAYS.INTL([@[DC_CREATION_DATE]],[@[ACTUAL_END_DATE]],""0000000"")-1)*(upper-lower)+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:

    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:

ActiveCell.FormulaR1C1 = _
    "=(NETWORKDAYS.INTL([@[DC_CREATION_DATE]],[@[ACTUAL_END_DATE]],""0000000"")-1)*(upper-lower)" & 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.

The result is...nothing, when the code is run, it doesn't give any errors, but the Column "V" is completely empty, without any value/results.

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 Run-time error: 1004, Application-defined or object-defined 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.

 

[Major Aly]

Anyone, who can help out?

 

[Major Aly]

I, for the life of me, cannot understand the usage of IF in this formula as to what it is being compared against

=(NETWORKDAYS.INTL([@[DC_CREATION_DATE]],[@[ACTUAL_END_DATE]],""0000000"")-1)*(upper-lower)+[B][U]IF(NETWORKDAYS.INTL([@[ACTUAL_END_DATE]],[@[ACTUAL_END_DATE]],""0000000""),MEDIAN(MOD([@[ACTUAL_END_DATE]],1),upper,lower),upper)[/U][/B]-MEDIAN(NETWORKDAYS.INTL([@[DC_CREATION_DATE]],[@[DC_CREATION_DATE]],""0000000"")*MOD([@[DC_CREATION_DATE]],1),upper,lower)"

 

[Major Aly]

Anyone who can help out?

 

[Dave Patton]

A concise example with expected results would be very useful. See the forum's tool XL2BB.

Consider the formula that follows

Date and Time.xlsm
	B	C	D
6	Sun 31-Jan-21 19:55	Wed 10-Feb-21 22:41	152:46
8c

Cell Formulas
Range	Formula
D6	D6	=(INT(C6)-INT(B6)-1)*15/24+MAX(MOD(B6,1),23/24)-MAX(MOD(B6,1),8/24)+MIN(MOD(C6,1),23/24)-MIN(MOD(C6,1),8/24)

 

[Major Aly]

A concise example with expected results would be very useful. See the forum's tool XL2BB.

Consider the formula that follows

Date and Time.xlsm
	B	C	D
6	Sun 31-Jan-21 19:55	Wed 10-Feb-21 22:41	152:46
8c

Cell Formulas
Range	Formula
D6	D6	=(INT(C6)-INT(B6)-1)*15/24+MAX(MOD(B6,1),23/24)-MAX(MOD(B6,1),8/24)+MIN(MOD(C6,1),23/24)-MIN(MOD(C6,1),8/24)

Perfect, it works great. Ran it side by side with the formula I found and yields exact results and is also simpler to boot.

Now I just need to convert it into VBA coding (for which so far the results are different, but I'll work on it).

Thank You!

 

[Dave Patton]

Thanks for the feedback.