I'm working on a data sheet that has start and end times/dates and a simple duration column (subtracting those two).

My start and end have custom formatting of: mm/dd/yy hh:mm:ss.

The simple duration column is easy, unfortunately it doesn't take into account work days. My company has non-traditional work hours which lead to a total number of hours in which we do not work being 9 hours.

I have created the following formula to do this:

=IF(F6=0,0,(IF(DAY(G6)=DAY(F6),H6,(TIME((HOUR(H6)-(9*NETWORKDAYS(F6,G6))),MINUTE(H6),SECOND(H6))))))

the steps are as follows:
- If there is no start time (F6) than return zero.
-If there is a start time (F6) than check if start date (F6) and end date (G6) are equal, if they are, return the same as the initial duration column (H6)
-If they are not equal give me the time minus the number of days (the NETWORKDAYS function) multiplied by 9.

The problem I'm having is that many of the current duration values are over 24 hours, meaning when I request the HOUR(H6) I get 22 rather than 46, this causes negative hours and an error.

Is there any way (a better function perhaps?) to get the actual number not the number in base 24? Am I making this way too hard for myself? (I don't really understand array functions so I never think to use them, and I would have no idea how to go about this using them anyway)


Thanks in Advance!