How come this works????

[gtag44]

I'm going crazy.

Have a look here
Get Excel Jet : work hours between dates and times

=(NETWORKDAYS(start,end)-1)*(upper-lower)
+IF(NETWORKDAYS(end,end),MEDIAN(MOD(end,1),upper,lower),upper)
-MEDIAN(NETWORKDAYS(start,start)*MOD(start,1),upper,lower)

Looking at it NETWORKDAYS(end,end) and NETWORKDAYS(start,start) , as there are 0 days between end-end and start-start , should evaluate to 0 so that the formula could be reduced to

=(NETWORKDAYS(start,end)-1)*(upper-lower)
+IF(0,lower,upper)
-MEDIAN(0,upper,lower)

And because IF(0) -> FALSE, and MEDIAN(0, latetime, earlytime)-> earlytime (upper late time, lower = early time) shoulf further reduce to

=(NETWORKDAYS(start,end)-1)*(upper-lower)
+ upper
- lower

But - it does not give the same result...

What am I not seeing here ??

 

[Fluff]

Hi & welcome to MrExcel.
The easiest way to see what a formula is doing, is to use the Evaluate Formula button on the formula tab.

 

[gtag44]

Hi & welcome to MrExcel.
The easiest way to see what a formula is doing, is to use the Evaluate Formula button on the formula tab.

Thank you for the warm welcome.

I'm aware of the Evaluate, and looking at the evaluation NETWORKDAYS gives different results depending on

If start date is a date (numbervalue = 44125) or a timedate (numbervalue =44125,56757)

in the first case NETWORKDAYS(start, start) ->0
in the second NETWORKDAYS(start, start) ->1

and that baffles me, as the real time difference in both cases (and especially the second) is 0

​

 

[Fluff]

If the start date is a weekend you'll get 0, otherwise you'll get 1.
the NETWORKDAYS(start, start) has nothing to do with time.

 

[gtag44]

If the start date is a weekend you'll get 0, otherwise you'll get 1.
the NETWORKDAYS(start, start) has nothing to do with time.

Really ? 44125 and 44125,56757 is both on Monday September 21st 2020.

NETWORKDAYS() - is according to the description, specifically about time :
NETWORKDAYS function - def
Returns the number of whole working days between start_date and end_date. Working days exclude weekends and any dates identified in holidays. Use NETWORKDAYS to calculate employee benefits that accrue based on the number of days worked during a specific term.

 

[MARK858]

NETWORKDAYS() - is according to the description, specifically about time :

Where does it mention time in the description? It states whole working days

 

[Fluff]

Networkdays calculates days, not time.
The time element is irrelevant, as can be seen by this

+Fluff 1.xlsm
	B	C	D	E
6	21/10/2020 18:56	21/10/2020 13:37		1
Main

Cell Formulas
Range	Formula
E6	E6	=NETWORKDAYS(B6,C6)

 

[gtag44]

A day is a span of time on the time vector. A day is a unit of time just a a second, a minute, an hour. It's about time.

 

[MARK858]

It is not about time. It is about Integers starting from 1/1/1900, i.e. days

 

[Fluff]

Ok as you want to be very pedantic I should have said
the NETWORKDAYS(start, start) has nothing to do with the time element of the date. (ie the decimal part of the number)