 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2006issue with Workday Function - not calculating workdays correctly!
From: amran.majid@gmail.com Newsgroups: microsoft.public.excel Subject: issue with Workday Function - not calculating workdays correctly! Date: Thu, 05 Oct 2006 12:23:03 -0000 hi, trying to use the workday function to calculate the number of workdays from a specific date. i am trying to calculate the 11th working day for each month. it works for October, but not november and december 2006. A1 = 01/10/2006 B1 = 02/11/2006 C1 = 03/12/2006 A2 = WORKDAY(A1,11,Holidays) returns a value of 16/10/2006 which is correct. B2 = WORKDAY(A1,11,Holidays) returns a value of 16/11/2006 which is incorrect. it should be 15/11/2006 (wed 01/11 - wed 15/11) C2 = WORKDAY(A1,11,Holidays) returns an uncorrect value of 18/12, it should be 15/12/2006. curiously January 2007 is ok on 16/01/2007 whats going on? You seem to be confused. All of the formulae that you give, in A2, B2, and C2, will give 16/10/2006, because they all source from A1. If you really mean B2 = WORKDAY(B1,11,Holidays) gives 17/11 which is correct and C2 = WORKDAY(C1,11,Holidays) gives 18/12, which is also correct  SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > From: amran.majid@gmail.com > Newsgroups: microsoft.public.excel [quoted text clipped - 25 lines] > > whats going on? Bob, sorry yes your right i just copied and pasted wrong, and put the wrong dates in as starters in my example...! doh! i have corrected the info below to the correct start dates and correct formula reference. A1 = 01/10/2006 B1 = 01/11/2006 C1 = 01/12/2006 A2 = WORKDAY(A1,11,Holidays) returns a value of 16/10/2006 - correct. B2 = WORKDAY(B1,11,Holidays) returns a value of 16/11/2006 - incorrect. it should be 15/11/2006 (wed 01/11 - wed 15/11) C2 = WORKDAY(C1,11,Holidays) incorrect 18/12, it should be 15/12/2006. 18/12 is not the 11th working day of december however as you can see from the table below 01/12/2006 Fri 1 02/12/2006 Sat NWD (not working day) 03/12/2006 Sun NWD 04/12/2006 Mon 2 05/12/2006 Tue 3 06/12/2006 Wed 4 07/12/2006 Thu 5 08/12/2006 Fri 6 09/12/2006 Sat NWD 10/12/2006 Sun NWD 11/12/2006 Mon 7 12/12/2006 Tue 8 13/12/2006 Wed 9 14/12/2006 Thu 10 15/12/2006 Fri 11 16/12/2006 Sat NWD 17/12/2006 Sun NWD 18/12/2006 Mon 12 19/12/2006 Tue 13 > You seem to be confused. > [quoted text clipped - 12 lines] > > (replace somewhere in email address with gmail if mailing direct) I now think you misunderstand what WORKDAY does. It does not return the nth working day of a month, it returns n working days AFTER the start date. Because the 1st Dec is a working day, 11 days AFTER that is the 12th working day, so 18/12 is absolutely correct. It just seems right by your interpretation for October because 01/10 is a Sunday. SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > Bob, > [quoted text clipped - 50 lines] > > > > (replace somewhere in email address with gmail if mailing direct) > I now think you misunderstand what WORKDAY does. It does not return the nth > working day of a month, it returns n working days AFTER the start date. [quoted text clipped - 10 lines] > > (replace somewhere in email address with gmail if mailing direct) ah! i understand. any ideas how i could calculate the number of working days from a particluar date? i assume i could use EOMONTH to calc the last day and then use the WORKDAY function to calculate it.. That is what WORKDAY does, it calculates a date a number of working days past a date. If you want the nth working day of a month, just start at the last day of the previous month. SignatureHTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) > > I now think you misunderstand what WORKDAY does. It does not return the nth > > working day of a month, it returns n working days AFTER the start date. [quoted text clipped - 16 lines] > i assume i could use EOMONTH to calc the last day and then use the > WORKDAY function to calculate it.. just so you know used EOMONTH and based the wrokday cal on that and it seems to work. Thanks for the pointer. > That is what WORKDAY does, it calculates a date a number of working days > past a date. If you want the nth working day of a month, just start at the [quoted text clipped - 6 lines] > > (replace somewhere in email address with gmail if mailing direct) >> I now think you misunderstand what WORKDAY does. It does not return the nth >> working day of a month, it returns n working days AFTER the start date. [quoted text clipped - 16 lines] >i assume i could use EOMONTH to calc the last day and then use the >WORKDAY function to calculate it.. No need to use EOMONTH if you have the first day of the month in A1: =WORKDAY(A1-1,11) or, for any day of the month in A1: =WORKDAY(A1-DAY(A1),11) --ron
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.