Formaula Explanation

Hello and welcome to the forum.

Just from a quick glance, it looks like the formula was built to count the number of "network hours" between two dates.

That is, count the number of hours between two dates not including weekends or non-work hours.

Yes, I got that part. Breaking down the formula:

("20:00-"6:00") = 0.58
IF(NETWORKDAYS(E1958,E1958),MEDIAN(MOD(E1958,1),"20:00","6:00"),"20:00") = 0.71
MEDIAN(NETWORKDAYS(D1958,D1958)*MOD(D1958,1),"20:00","6:00") = 0.47

This is the part I don't understand. The overall idea behind the formula is to calculate the network days but it is these small constraints to the formula that I am not able to follow.

What does ("20:00-"6:00") = 0.58 mean? Is it 10hours?

I was answering this question directly:

Can anyone please explain what this formula is trying to calculate.

After reading post #3, it looks like you're asking how the formula works, not what it is calculating.

Keep in mind that I am assuming that it works in the first place.

Let's take the second section from post #3. Work from the inside out.

MOD(E1958,1) returns only the decimal value of E1958. In Excel, dates and times are stored as numbers. Let's say E1958 = 4/3/2018 1:20 PM (which is 43193.56).
MOD(E1958,1) = 0.56, 20:00 = 0.83, and 6:00 = 0.25 so we have MEDIAN(0.56,0.83,0.25) = 0.56

An IF function is structured as follows: =IF(logical_test, value_if_true, value_if_false)
If the NETWORK days function returns a value of 0 (which is FALSE inside the IF function), then return 20:00, otherwise if anything other than 0 is returned, return the median value.

All that being said, I don't see the point of NETWORKDAYS(E1958,E1958). This will always return 0.

Originally Posted by CHExcel101

What does ("20:00-"6:00") = 0.58 mean? Is it 10hours?

6:00 = 6:00 AM
20:00 = 8:00 PM

6:00 AM to 8:00 PM is 14 hours which is 58/100 or 14/24 or 0.58 of a day.

Here's something worth checking out.

Link here: https://www.exceltactics.com/calcula...g-networkdays/

So, this part (NETWORKDAYS(D1958,E1958)-1)*("20:00"-"6:00") means 2days into 14hours (2*0.58) = 1.16 days ?
Am I understanding this correctly?