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

Major Aly

New Member
Joined
Mar 3, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. 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 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:

Excel Formula:
=(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:

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)*(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.
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Major Aly

New Member
Joined
Mar 3, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
I, for the life of me, cannot understand the usage of IF in this formula as to what it is being compared against

Excel Formula:
=(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)"
 

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
4,633
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
BCD
6Sun 31-Jan-21 19:55Wed 10-Feb-21 22:41152:46
8c
Cell Formulas
RangeFormula
D6D6=(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)
 
Solution

Major Aly

New Member
Joined
Mar 3, 2021
Messages
5
Office Version
  1. 2019
Platform
  1. Windows
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
BCD
6Sun 31-Jan-21 19:55Wed 10-Feb-21 22:41152:46
8c
Cell Formulas
RangeFormula
D6D6=(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!
 

Watch MrExcel Video

Forum statistics

Threads
1,128,099
Messages
5,628,682
Members
416,332
Latest member
blkbeltmjk

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