Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Modifying Networkdays
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Modifying Networkdays
Rob,
Look here ... it solves some of your problem: http://www.cpearson.com/excel/DateTimeWS.htm "Rob" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Modifying Networkdays
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help Modifying this Formula .... | New Users to Excel | |||
Modifying an If Statement | Excel Worksheet Functions | |||
Modifying VLookUp | Excel Worksheet Functions | |||
Modifying a document | Excel Discussion (Misc queries) | |||
help Modifying | Excel Worksheet Functions |