Hello All,
I'm in pursuit of creating a VBA macro that automates and simplifies a task of sorting of data that contains anywhere from thousands to tens of thousands of rows. This sorting macro also creates an additional column of calculation that calculates the number of hours worked per day and exclude non-working hours in the calculation.
I stumbled upon what I believe is a complex formula that I modified as per my needs but what I can't figure out for the life of me is what the "IF" function is doing seeing how no logical operator is used. Because of this, I'm unable to convert this formula into a macro. I tried using macro recorder and it just uses Active.Cell.FormulaR1C1 to paste the formula as is, whereas when I try to do the same, it gives an error.
Anyways, the formula in question is:
=(NETWORKDAYS.INTL(StartDate,EndDate,"0000000")-1)*(EndTime-StartTime)+IF(NETWORKDAYS.INTL(EndDate,EndDate,"0000000"),MEDIAN(MOD(EndDate,1),EndTime,StartTime),EndTime)-MEDIAN(NETWORKDAYS.INTL(StartDate,StartDate,"0000000")*MOD(StartDate,1),EndTime,StartTime)
Where:
StartDate = Start Date of a task in "dd/mm/yyyy hh:mm:ss" format
EndDate = End Date of a task in "dd/mm/yyyy hh:mm:ss" format
StartTime = Start of working hours i.e. "08:00 AM" format
EndTime = End of working hours i.e. "11:00 PM" format.
As you can see, the "IF" contains NetworkDays.Intl without a logical operator and then just proceeds to returning a value if true or false. This is what is confusing me as to how it is working, as it works OK when using it as a formula in cell directly.
Looking forward to any explanations that I might be missing.
Thank You!
