HOUR function returning wrong number

**milleribsen** · 11-04-2011, 01:24 PM

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!

**shg** · 11-04-2011, 02:18 PM

Our Daddylonglegs has posted this formula for calculating work hours between two dates and times, assuming that the start and end date/time are during work hours:

=(NETWORKDAYS(dateBeg, dateEnd) - 1) * (timeEnd - timeBeg) + MOD(dateBeg, 1) - MOD(dateEnd, 1)

... where timeEnd and timeBeg are your work start and stop times, e.g., 08:00 to 17:00.

E.g.,

Please Login or Register  to view this content.

A6 is formatted as [h]:mm:ss

**milleribsen** · 11-04-2011, 02:30 PM

This doesn't seem to work. On values that are the same day I'm getting what appears to be a neg. on values that are on different days it's returning a very wrong number.

I'm going to go ahead and upload a sample of what I'm looking at, with my original formula set.

**daddylonglegs** · 11-04-2011, 02:34 PM

From your data it looks like the start and end times are always on weekdays but can possibly start before working hours or end after in which case try putting the start of the working day in G2 and end of working day in G3 then use this version in D2 copied down

=IF(A2=0,0,(NETWORKDAYS(A2,B2)-1)*(G$3-G$2)+MEDIAN(MOD(B2,1),G$2,G$3)-MEDIAN(MOD(A2,1),G$2,G$3))

format as [h]:mm:ss

**milleribsen** · 11-04-2011, 02:42 PM

Working hours are 8:30 to 23:30

**daddylonglegs** · 11-04-2011, 02:45 PM

Sorry, I edited my post, can you try the formula I suggested - that should work for you......

**milleribsen** · 11-04-2011, 02:47 PM

Yes! It works! Thank you so much. Now I just need to Move that around for all the data sets I'm working on. Should take me the rest of the morning, perfect timing!

thank you!