Date Subtraction Formula not returning correct answer

[Conell8383]

Hi all. I hope you can help. I have inherited an excel sheet with a formula that I don't fully understand.
Essentially I believe the formula is looking to determine the amount of time a task from creation date and time (Cell U2) took to complete from (Cell V2) both in hours and minutes.
This is taking into consideration that my teams working hours are 8:30 am until 17:00 pm Monday to Friday excluding weekends and Holidays noted as 25th December 2017
I have attached a picture for better understand
The issue is that formula in W2 is returning a 0
The formula that is returning a 0 In W2 is

[FONT=Consolas]=(NETWORKDAYS.INTL(U2,V2,11,'Working Hours & Holidays'!$C$2)-1)*("17:00"-"8:30")+IF(NETWORKDAYS.INTL(V2,V2,11,'Working Hours & Holidays'!$C$2),MEDIAN(MOD(U2,1),"8:30","17:00"),"17:00")-MEDIAN(NETWORKDAYS.INTL(V2,V2,11,'Working Hours & Holidays'!$C$2)*MOD(U2,1),"8:30","17:00")[/FONT][/FONT][/COLOR]

As always any and all help is greatly appreciated.

 

[eduzs]

Try this formula to calculate time:

=INT((V2-U2)*24)+(V2-U2-INT((V2-U2)*24))

This formula will work only if the task have a total time less than 24hs.

Format the cells as "Hours" format.

 

[Conell8383]

Hi Eduzs. Thank you for taking the time to respond. Yes =INT((V2-U2)*24)+(V2-U2-INT((V2-U2)*24)) will work but I am curious as to why my formula wont return the correct answer. Again any help you can afford is greatly appreciated.

 

[eduzs]

Can you format as number only to see the actual value of "00:00"?

 

[barry houdini]

Hello Connell8383,

You cross-posted this at Stack Overflow

The formula needs to be adjusted as I explained there, thread closed