Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Workday function including sat, excluding sun
Hi, I'm trying to create a function which would calculate estimated kilometers driven. Our trucks will stop only for sundays so there comes my WORKDAY-function problem. If I have example dates: 1.1.2006 - 31.1.2006 = 26 truck workdays How can I get that result with workdays function, saturdays included but bank holidays excluded? Thanks in advance :) -- Handyy ------------------------------------------------------------------------ Handyy's Profile: http://www.excelforum.com/member.php...o&userid=30958 View this thread: http://www.excelforum.com/showthread...hreadid=515264 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Workday function including sat, excluding sun
If you're trying to calculate the number of workdays between 2 dates the function you need is NETWORKDAYS not WORKDAY. To count weekdays and Saturdays =NETWORKDAYS(A1,B1,holidays)+INT((WEEKDAY(A1)+B1-A1)/7) where A1 contains start date and B1 end date and holidays is a named range containing (weekday) holiday dates -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=515264 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Workday function including sat, excluding sun
might want to cater for Saturday holidays
=NETWORKDAYS(A1,B1,holidays)+INT((WEEKDAY(A1)+B1-A1)/7)- SUMPRODUCT(--(WEEKDAY(A1:A20))=7,--ISNUMBER(MATCH(A1:A20,holidays,0))) -- HTH Bob Phillips (remove nothere from email address if mailing direct) "daddylonglegs" wrote in message news:daddylonglegs.23mq5m_1140609302.5881@excelfor um-nospam.com... If you're trying to calculate the number of workdays between 2 dates the function you need is NETWORKDAYS not WORKDAY. To count weekdays and Saturdays =NETWORKDAYS(A1,B1,holidays)+INT((WEEKDAY(A1)+B1-A1)/7) where A1 contains start date and B1 end date and holidays is a named range containing (weekday) holiday dates -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=515264 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Workday function including sat, excluding sun
Thanks for the tips, really helpful info for me! -- Handyy ------------------------------------------------------------------------ Handyy's Profile: http://www.excelforum.com/member.php...o&userid=30958 View this thread: http://www.excelforum.com/showthread...hreadid=515264 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VBA | Excel Worksheet Functions | |||
to use workday function in excel vba code | Excel Discussion (Misc queries) | |||
clock | Excel Worksheet Functions | |||
How do I get the WORKDAY function? | Excel Discussion (Misc queries) | |||
HOW CAN I GET OFFICE 2003 EXCEL BASIC TO NEST FUNCTIONS LIKE EXCE. | Excel Worksheet Functions |