 |
 | 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 2006How do I count workdays using an excel formula?
Hello, I have a spreadsheet where I manually count the number of workdays in a given month, then I multiply those days by 8 hours. This tells me how many hours I've worked that month over or under the expected amount. For the last few years I've manually counted the number of work days for each month. Is there a formula that will determine the number of work days in any given month? My spreadsheet does have the month in Row A. For example Row 2, Column BW is October 2006. Is there a formula that will point to Row 2, Column BW and recognise it as October 2006 and determine there is 21 work days? Thank you for your help, Art. What are the workdays, Monday through Friday? Do you want to exclude any holidays? >For example Row 2, Column BW is October 2006. Is BW2 a formatted date to appear as October 2006 or is it a TEXT entry? Take a look at the NETWORKDAYS function in Help. Biff > Hello, > [quoted text clipped - 13 lines] > > Art. Hi Art, As Biff suggested look at NETWORKDAYS(). In this example it will return all weekdays in a particular month, where A2 contains the date e.g. 01/12/2006. In addition it will eliminate holidays, e.g. A3 contains 04/12/06 so it will return 20 days as a result: =NETWORKDAYS(DATE(YEAR(A2),MONTH(A2),1),EOMONTH(A2,0),A3) Regards, Jon  SignatureEmail: subst1tut3 numb3rs for l3tt3rs... > Hello, > [quoted text clipped - 13 lines] > > Art. Thank you for replying Biff and Jon. >What are the workdays, Monday through Friday? Do you want to exclude any >holidays? >For example Row 2, Column BW is October 2006. >Is BW2 a formatted date to appear as October 2006 or is it a TEXT entry? Monday through Friday with holidays excluded is what I'm looking for. BW2 is formatted as "mmm. yyyy" I entered 01/12/2006 in A2 and 04/12/06 in A3 and used the formula below but I get a #NAME? error. =NETWORKDAYS(DATE(YEAR(A2),MONTH(A2),1),EOMONTH(A2,0),A3) Art. > Hi Art, > [quoted text clipped - 31 lines] >> >> Art. Tools>Add-ins>Analysis ToolPak must be checked for this function. Gord Dibben MS Excel MVP >Thank you for replying Biff and Jon. > [quoted text clipped - 51 lines] >>> >>> Art. >I entered 01/12/2006 in A2 and 04/12/06 in A3 and used the formula below but >I get a #NAME? error. > >=NETWORKDAYS(DATE(YEAR(A2),MONTH(A2),1),EOMONTH(A2,0),A3) It's always a good idea to check HELP for functions. Seems to describe your exact problem: If this function is not available, and returns the #NAME? error, install and load the Analysis ToolPak add-in. How? On the Tools menu, click Add-Ins. In the Add-Ins available list, select the Analysis ToolPak box, and then click OK. If necessary, follow the instructions in the setup program --ron I have Excel 2003 and it's up to date but I don't have this function but I don't have EOMONTH or NEWORKDAYS. Can the formula be rewritten so it doesn't use either of these. Office 2003 came with my computer so I don't have a CD to install the Analysis Tool Pak add-in. > Hi Art, > [quoted text clipped - 31 lines] >> >> Art. Hi Art Without using the ATP functions. In place of Networkdays =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6)) Where A1 holds your start date and B1 your end date This uses WEEKDAY(date,2) to make Monday =1 and Sunday =7, so Weekdays <6 will be working days. ROW(INDIRECT(A1&":"&B1)) creates an array of all of the dates between A1 and A2, and SUMPRODUCT adds all the values that Match the criterion of being less than 6. This is an adaptation of a formula that I first saw posted by Biff. In place of EOMONTH =DATE(Year(A1),Month(A1)+1,0) relies on the fact the 0th day of a month is the same as the last day of the previous month and will return the same value as =EOMONTH(A1,0) Change the addition of +1 after Month to compute other months SignatureRegards Roger Govier >I have Excel 2003 and it's up to date but I don't have this function >but I don't have EOMONTH or NEWORKDAYS. Can the formula be rewritten [quoted text clipped - 38 lines] >>> >>> Art. That seems to do the trick, thank you. > Hi Art > [quoted text clipped - 60 lines] >>>> >>>> Art. To exclude holidays: J1:J10 = list of holiday dates to be excluded from the calculation =SUMPRODUCT(--(WEEKDAY(ROW(INDIRECT(A1&":"&B1)),2)<6),--(ISNA(MATCH(ROW(INDIRECT(A1&":"&B1)),J1:J10,0)))) There's a slightly shorter alternative formula to do this but I don't understand the logic behind it and no one has been able to explain it to me so I prefer this formula. Biff > That seems to do the trick, thank you. > [quoted text clipped - 62 lines] >>>>> >>>>> Art. Very nice Biff!! SignatureRegards Roger Govier > To exclude holidays: > [quoted text clipped - 75 lines] >>>>>> >>>>>> Art.
|
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.