Networkdays function problem

[Tad Wesley]

I am trying to make a chart that charts production times
over workdays. For example, if we recieved a file on
3/5/04 at 12PM, and it was completed 3/9/04 at 3PM, I need
to know how long we had it, in workdays. Then I'd
multiply the result by 24, to give me hours, to 2 decimal
points. In the above example, I want it to show me 75
hours as the answer, because there is a weekend in there
that would remove 48 hours from the equation.

The problem is, the NETWORKDAYS function truncates any
fractional days. I'm using date functions that include
the time in a single cell.

Is there no way to do what I'm trying to do?

If I am not being as clear as I would like, please reply
and ask for clarification. Any help with this would be
GREAT! Thank you!

 

[Frank Kabel]

Hi
have a look at
http://www.cpearson.com/excel/DateTimeWS.htm

 

[Tad Wesley]

thanks sooo much! Wow that's quite a formula, I'm glad I
asked for help. I would have NEVER figured that out on my
own!

 

[Tad Wesley]

After trying for hours to try to get this formula to work,
and going through the code over and over to make sure I
input all the data correctly, I continue to get "invalid
formula" errors. I have the toolpak installed properly,
it's not an issue with the NETWORKDAYS command.

The formula I am using is this one:
=IF(INT(StartDT)=INT(EndDT),ROUND(24*(EndDT-StartDT),2),
(24*(DayEnd-DayStart)*
(MAX(NETWORKDAYS(StartDT+1,EndDT-1,HolidayList),0)+
INT(24*(((EndDT-INT(EndDT))-
(StartDT-INT(StartDT)))+(DayEnd-DayStart))/(24*(DayEnd-
DayStart))))+
MOD(ROUND(((24*(EndDT-INT(EndDT)))-24*DayStart)+
(24*DayEnd-(24*(StartDT-INT(StartDT)))),2),
ROUND((24*(DayEnd-DayStart)),2))))


I have replaced StartDT with the cell that has the start
date and time in the proper format, likewise with EndDT.
The DayEnd and DayStart I put in 8:00 and 17:00, just to
see if I could get it to work. To be honest, though, I'd
actually prefer not to have that part of the formula, but
I am unsure which parts I can safely delete from the
formula without screwing it all up (since to be honest I
don't understand what I'm typing well enough to make
adjustments)

Perhaps this will fix 2 problems simultaneously, as I
assume the formula that doesn't have to account for the
workday start and end would be a much simpler one (and
therefore one I'd be a lot less likely to screw up)

Any further help would, again, be very much appreciated!

 

[Daniel.M]

Hi Tad,

Try this one. Output is in time format and needs to be formatted accordingly,
aka [h]:mm

=IF(StartDT>EndDT,0,NETWORKDAYS(StartDT,EndDT,Holidays)*(OpEnd-OpStart)
-IF(NETWORKDAYS(StartDT,StartDT,Holidays),MAX(0,MIN(OpEnd,MOD(StartDT,1))-OpStar
t),0)
-IF(NETWORKDAYS(EndDT,EndDT,Holidays),MAX(0,OpEnd-MAX(MOD(EndDT,1),OpStart)),0))


If you don't care so much about OpStart and OpEnd (meaning your counting all the
valid hours, not only those between OpStart and OpEnd on each day), formula can
be expressed easier:
=IF(StartDt>EndDt,0,NETWORKDAYS(StartDt,EndDt,Holidays)
-IF(NETWORKDAYS(StartDt,StartDt,Holidays),MOD(StartDt,1))
-IF(NETWORKDAYS(EndDt,EndDt,Holidays),1-MOD(EndDt,1)))

Same format as previous one:
[h]:mm

Regards,

Daniel M.