Counting work days

This wI want to count the number of work days between two dates.  Do I have 
to rewrite the formula for each row or can I tell it to compare the date in 
column a to the date in column b and count the workdays and exclude holidays. 
 The worksheet has over 1,000 rows.  Example:

1/4/05       1/4/05
1/5/05       1/11/05
2/6/05       3/15/05
0
Hausma (5)
4/10/2005 9:11:07 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
615 Views

Similar Articles

[PageSpeed] 12

"Hausma" <Hausma@discussions.microsoft.com> wrote in message
news:BC75C101-63D0-45F5-A009-56067683EBBA@microsoft.com...
> This wI want to count the number of work days between two dates.  Do I
have
> to rewrite the formula for each row or can I tell it to compare the date
in
> column a to the date in column b and count the workdays and exclude
holidays.
>  The worksheet has over 1,000 rows.  Example:
>
> 1/4/05       1/4/05
> 1/5/05       1/11/05
> 2/6/05       3/15/05

I think the NETWORKDAYS function is what you need
http://support.microsoft.com/?kbid=259200

/Fredrik


0
4/10/2005 9:13:16 PM
Reply:

Similar Artilces:

DDE
In the access program (97,2000,XP, 2003) I was using DDE commands for sending data to Excel and creating charts. For the chart I was using Chart.Wizard which is faster than defining property by property the chart values. In 2007 this command is not executed. I don't receive an error but it does not work. Is this command not available in 2007 ? Has it been replaced by sth else ? ...

combo box control issue - working fine in '03,
when I add a new record on the form via VBA - and then go to the combo box control, the control tip shows properly, but the cursor is not blinking. can not type anything into the box. when I click on the box, all is ok. any ideas as to what is wrong? works fine in access 2003. thx Make sure that you have at least the first 2 service packs in Access 2007. There was a combo box dropdown problem that I think was fixed in SP2. If you still have the problem, try SP3. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.accessmvp.com http://www.mvps.org/access &...

Exchange doesn't work on Comcast <a8b4472c-4eb6-40f2-ac68-d0f9b77f50eb@z24g2000prf.googlegroups.com>
I just upgraded to Outlook 2007, and suddenly I am no longer able to connect to my exchange server that is hosted by 4smartphone through my Comcast internet. However, if I change the same computer over to my AT&T DSL internet it works just fine. What can I go to comcast with to ask them to change on their servers?? ...

Record Count 10-02-07
Experts, I'm trying to dynamically stored the record count from a subform into a field on my form. Now I have to look at the record count on the subform and manually entered the count into a field on form. There must be a better way. Please help! In the Control Source property of a text box on the main form: =[SubFormControlName].[Form].[recordset].[RecordCount] Where SubFormControlName is the name of the subform control, Not the name of the form that is the Source Object of the subform control. -- Dave Hargis, Microsoft Access MVP "Shiller" wrote: > Experts, >...

Count column difference
Hi Using MSExcel 97. I have two columns of data e.g. A1: A4, containing values 5,10, 3, 6 B1:B4, containing values 3, 8, 7, 4 I wish to perform a count (e.g. in C5) of the number of rows where the value in column A exceeds the respective value in column B (in this case count = 3, as A1>B1, A2>B2, and A4>B4). Just cannot get my formula right. Tried using an array (but difficult when comparing the difference between two columns), and COUNT. Thanks in advance for any suggestions. Wizzy ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.co...

Pivot Table
I've this problem, when I drag the fields into the "Data" area, it will show as "Count of Q1 Results". But what I actually want is "Sum of Q1 Results". I would have to manually go to field setting and reconfigure from count to sum. It happens for all the fields I drag into the "Data" area. Is there any way around this? Thanks. If there are blank cells, or cells with text, in the column, Excel will default to the Count function when the field is added to the data area. If the column contains only numbers, it should default to Sum. Derrick wr...

"Microsoft Outlook Stopped Working" error
Hi all, may I firstly apologise if I seem stupid in anything I say here; I have always used another PIM programme in the past with only the occasional dalliance with any version of Outlook. I am running Vista (Fully updated) Home Premium on a Tosh laptop (32 bit) 1.73 Gb 2GM Ram. I recently installed Outlook 2007 (I also have Office Home & Student 2007 (service desk edition?) which I have been using for a year or so.) The problem is that I keep getting this error when using Outlook. (for example 4 time in 15 mins). I have removed the programme (to ensure no trace o...

.stm increase 500MB/Day
Hi all, I have five Exchange Servers 2003, one of this servers is a bridgehead server to internet messages. This server is a dual PIII 1GB with 2GB RAM and 36GB DISK. It work fine but the .stm file of this server increase 500MB per day. The flow of messages is about 11000 per day. I tried to change "Deletion Settings" in file group properties to 0 for all, but this not have relation with the problem and every month I need to create a new file group and delete the old to free disk space. Anyone knows any solution? Tks. Alexsandro Camargo ...

OWA not work properly
When users go to http://sitename/exchange they get the yellow OWA screen without the graphics. If I enter username, I get "HTTP Error 404 - Not Found". I have NT 4.0 SP6a, Exchange 5.5 SP4, and IIS 4.0 with all the latest updates. In Internet Service Manager, in the Default Web Site properties, I have the redirect to the above URL in the Home Directory tab. Under the Directory Security tab I have both Anonymous and Challenge/Response. In the Exchange folder properties (in Internet Service Manager) under the Virtual Directory tab I have the Local Path "c:\exchsrvr\webdata&quo...

Mouse scroll not working in Module window
Access97 on HP Laptop on XP Pro Since installing Office 97 on my new (replacement) laptop I find the mouse scroll button does not work in the Module window - works everywhere else in Access ok? Any ideas appreciated. Piri yeah don't use ancient software, kid "Piri" <wiremu.pareiha@hotmail.com> wrote in message news:1178247184.740213.222110@y5g2000hsa.googlegroups.com... > Access97 on HP Laptop on XP Pro > Since installing Office 97 on my new (replacement) laptop I find the > mouse scroll button does not work in the Module window - works > everywhere else i...

Selecting Pics in Explorer, then "Send via e-mail" doesn't work...
I'm sure this has been addressed, but I haven't found it in a search. I'm running Outlook 2007 and it worked fine with XP - since I've upgraded to Windows 7, I can't select *any* files in explorer and send them via e-mail. Nothing happens, then I can't get Outlook to start under any circumstance unless I reboot. I need to create a new e-mail, then select the files...and it works fine, but it's a giant PITA. Any help: Sounds like it is caused by a corruption or an incompatible add-in. Which add-ins do you have installed? Tools-> Trust Center-&g...

HQ Options not working
Hi! When I open the HQ Configuration window, there is a list of Checkboxes for "Enabled" - "Disabled" for : - Automatically create inter-store receive inventory order - Automatically create inter-store issue inventory order and other! This window doesn't work, and when accepting changes, and open the window again, all checkboxes are clear!! And of course, those options do not work neither! I'm working by lauching inter-store trasnfers from HQ, but would like to have stores deciding this by its own. Anybody know something about this. Maybe how to set this featu...

Button form control does not work
I have spent over 5 hours trying to get the button wizard to do a simple thing. Open a form and show data in that form that matches the name in the form where the button is but when i follow the wizard the button allways go to the first entary in the second form eg i have the form (form1) open on emma (record 3) and want to open a second form that also contains emma the wizard puts the following in to a macro form2, Form, , "[Full Name]=" & [Full Name], , Normal but when i test the button it open the second form with the first record which is not emma why d...

will RMS 2.0 work with Windows 7
Has anyone tried using RMS 2.0 with Windows 7? What version RMS are you using? Yes 2.0.0126 -- Maurice Gordon Maurice@americanretailsupply.com Sales/Support 1-800-426-5708 XT 1307 "Larry" <Larry@discussions.microsoft.com> wrote in message news:0E0D2035-1FB0-497E-8F73-AF5FDA947E64@microsoft.com... > Has anyone tried using RMS 2.0 with Windows 7? What version RMS are you > using? Hi Larry, RMS 2.0 SP2 working fine with windows 7 Vijay "Larry" wrote: > Has anyone tried using RMS 2.0 with Windows 7? What version RMS ar...

saved passwords not working
I have 4 email accounts in outlook 2002. Everytime Outlook is started, it prompts me for my passwords on each account, even though they are saved. It only does this when it is started for the first time and it saves the passwords for the rest of the session. But, the next time Outlook is restarted, it prompts me for the passwords again. I have SP2 installed and have searched the support KB for this problem. Anyhelp would be appreciated. Carley@CarleyCapers.com ...

Counting in a Report
I have the following entered for the Control Source in a group header text box on my report: =Abs(Sum([CountOfGoal])) Now in the footer of my report I want to add the result of those text boxes in another text box. What should I enter for the Control Source of that text box? Thanks, CEV "CEV" <chadv@advancebkg.com> wrote in message <evr8e50dpb@enews1.newsguy.com>: > I have the following entered for the Control Source in a group header > text box on my report: > > =Abs(Sum([CountOfGoal])) > > Now in the footer of my report I want to add the re...

Scrolling not working in spreadsheet (Excel 2000)
Good afternoon. We have Excel 2000 running on Windows 2000. I have a spreadsheet with a column that is filtered by Status - Open or Closed. If I am showing the 'Open' items, I can page up and down, use the up/down arrows and the mouse to scroll up and down in the document. If I show the 'Closed' items, none of the scrolling options work. The scroll bar on the right indicates that I'm 'moving' down in the document, and the cell reference in the upper left corner changes (as does the display of the cell contents) but all I see on the screen is the first page. An...

Formula to display Count of Days in given month, using list of Start and End Dates
I have a large number of rows that contain a start date and end date. I am attempting to generate a count of days within that date range that are in a particular month, for example, Jan of 2011. Any suggestions for a formula that could be used to display the amounts shown in Column C would be helpful. Layout is: A B C Start Date End Date Count of Days in Jan2011 1/10/11 4/7/11 21 2/6/11 3/1/11 0 12/20/10 2/15/11 31 Etc. Dave, I think the best way is to create a table f...

TimerInterval not working
I have an access db application which needs to call the macro on startup. The macro is to call the function opening two forms on startup and set the TimerInterval of two forms. Public Function auto_login() DoCmd.OpenForm "frm_A" Forms![frm_Import&Export].TimerInterval = 300000 DoCmd.OpenForm "frm_B" Forms![frm_TREATS_ftp].TimerInterval = 120000 End Function However only the Timer for form B (at the toppest) works. It works for form A but not B if I open form B first and then A later. Is there any way to solve the problem? Regards Caris Caris, This seems a litl...

Re: Suggest Name Now Not Working
"Karl Burrows" <kfb1@spambellsouth.net> wrote in message news:... > My bad, it's the automatic name checking I use, not the suggest name. I get > confused between the two. Name checking is the one you get the red or green > underline squiggle to right-click and complete the email address. > > It's been a long week! > > PS Just got your Outlook Programming book. > > "Sue Mosher [MVP-Outlook]" <suemvp@outlookcode.com> wrote in message > news:OIBsc8XgEHA.3864@TK2MSFTNGP10.phx.gbl... > > Did you check your setting in T...

On-line quotes server does not work
OK - there are numerous posts in this Newsgroup stating that the on-line update to stock quotes and news does not work - everyone getting the "unable to connect to quotes server" error. Is it working for anyone? I switched from Quicken to Money and I'm about to switch back - at least Quicken can do something as simple as downloading quotes from a server reliably. Anyone home at Microsoft? On Tue, 3 Feb 2004 21:40:12 -0800, "Anyone home at Microsoft? Hello?" <fbella@aol.com> wrote: > >OK - there are numerous posts in this Newsgroup stating >that...

does any one know how to convert a scanned sheet to a work sheet
does any one know how to convert a scanned sheet to a workable sheet in excel You could use OCR (optical character recognition) software to convert the image to text that could then be loaded into cells. My experience with OCR software has not been entirely satisfactory. Unless this is a large sheet, it may be easier to retype. Jerry bigjoe44 wrote: > does any one know how to convert a scanned sheet to a workable sheet in excel ...

One of the System Attendant's task is blocked. Function: CMonitoringTask::Work
Hi All, Any help on this is very much appreciated!! Right, here goes! I have two exchange servers which are both running on Win 2k3 SP1. Exchange is 2003 SP1. One of my exchange servers comes to a halt and the only way to get it back up and running is by hard resetting the server! When this has been rebooted the server works fine and reports no errors in the logs after the reboot. However, I am getting the following event logs showing errors prior to reboot: Event Type: Error Event Source: MSExchangeSA Event Category: General Event ID: 1031 Date: 16/10/2006 Time: 1:35:46 AM User: N/A Com...

Need 5 Business Days...
Hi everyone! Using A02 on XP. I have a query that is pulling all records ">=[StartDate] and < [EndDate]+1" I also have a field [TurnTime] that is a calculation of [DateMailed]-[Complete]. My problem is that our Turn Time Standard is five business days. I can't identify business days here. Is there something I can do to this field or should I have another field identify how many business days exist in the list of dates between [StartDate] and [EndDate]? Then use that field for setting the criteria to >5? I would really, really appreciate any help or advice! ...

CRM V1.2 and SBS 2003 Not Working installation issues
Greetings and Happy New Year My Question is why doesn't CRM behave correctly? Environemnt is SBS2003 Premium Exchange Configred and running correctly. ISA running using 2 Nics. Domain with active directory running ok. SQL 2000 with service packs 3a applied. IIS running ok default websites all accessable. Firstly i followed the IG for SBS2003(Fresh Install with all updates) all prerequisites were ticked off prior to installation. Install procedure was followed to the letter. Results :( http://localhost causes IE to spit up You do not have permissions to view this Web Site. What ...