Calculate elapsed hours between two date/time excluding weekends.

[Jfreder6]

Hello,

I am trying to use the NETWORKDAYS formula to calculate the elapsed time in hours between two cells that both contain a date/time. I have attached an example fo the data. Thank you for your help!

[Jfreder6]

I figured out my issue but am now having another one in certain cells.

I used this formula =NETWORKDAYS(D2,E2)-1-MOD(D2,1)+MOD(E2,1) which is working for most cells. However, I have a few that I am getting an ########## error in the cell where the calculation is supposed to be.

Example: D cell contains 3/8/2024 9:55 PM and E cell contains 3/10/2024 9:09 PM. The error states " Dates and times that are negative or too large show as ####" The calculation would not result in a negative number and the resulting number should be ~48 so it's not too big.

Any suggestions?

[AliGW]

Provide a sample workbook with 10 examples, including some that produce the wrong result - where there is an incorrect result, fill in the expected result manually next to it.

Or you could try this:

=(C2-B2)*24

BUT I have no idea what result you are expecting as you didn't tell us that!

[HansDouwe]

The formula you came up with only works well when the first and last day is a working day.

In the example of Friday March 8, 2024 9:55 PM and Sunday March 10 9:09 PM this is not the case.
Moreover, in the latter case I would expect a result of 2:05 hours.

This formula takes into account that the first or last day does not have to be a working day:

Please Login or Register  to view this content.