Modifying Networkdays

[Guest]

Hi,

I have the below code that utilizies the NETWORKDAYS capability of Excel. My
question is is it possible to Internally/Programaticaly modify how
NETWORKDAYS functions? I know that the "9.5" sets the hours per day, however,
I want to be able to set "When" the work day starts; "When" and "Howlong"
lunchtime is and "When the end of the day is.

Work Start - 7AM
Lunchtime - 11:30 till 12:30
End Day - 5PM


=IF(F14<>FALSE,(9.5+(MOD(F14,1)-MOD(E14,1))*24+(NETWORKDAYS(E14,F14)-2)*9.5)/24,"")


Thanks In Advance,
R

 

[Guest]

Rob,
Look here ... it solves some of your problem:

http://www.cpearson.com/excel/DateTimeWS.htm

 

[Dave O]

Your post doesn't mention what the MOD functions are doing, so I won't
attempt to address them; I mocked up data in cells A1:B4 to hold start
and end times, like this:
Work Start 7:00 AM
lunch start 11:30 AM
lunch end 12:30 PM
work end 5:00 PM

....and start / end calendar days for use by the NETWORKDAYS function
in B6:B7. I wrote this formula to get the total number of hours:
=NETWORKDAYS(B6,B7)*(((B2-B1)*24)+((B4-B3)*24))

Something I noticed about your post: you're using 9.5 hours for a full
day, but with an hour for lunch and those start / stop times, the
total is 9 hours.