count days in a date range

I am trying to count the number of days within a given 
date range but leave out saturday's and Sunday's.  Is 
there a way to do this?  Using the function Days360 seems 
to be the right path, and counts all the days between the 
range, but I am not sure how to customize from there, if 
it is even possible.

cp
0
anonymous (74722)
5/19/2004 4:17:01 PM
excel.newusers 15348 articles. 2 followers. Follow

6 Replies
401 Views

Similar Articles

[PageSpeed] 24

Hi
if your dates are in a and B1 use
=NETWORKDAYS(A1,B1,list_of_holidays)

Note: the Analysis Toolpak Add-in has to be installed for this


-- 
Regards
Frank Kabel
Frankfurt, Germany


cp wrote:
> I am trying to count the number of days within a given
> date range but leave out saturday's and Sunday's.  Is
> there a way to do this?  Using the function Days360 seems
> to be the right path, and counts all the days between the
> range, but I am not sure how to customize from there, if
> it is even possible.
> 
> cp
0
frank.kabel (11126)
5/19/2004 4:25:12 PM
Do you know where I can download the toolpak? I use Excel 
2000 on a Windows XP operating system. I did find a site 
on Microsoft at:
http://www.microsoft.com/downloads/details.aspx?
FamilyID=01902294-9e3f-487f-b7b0-
e310fe2d5362&DisplayLang=en

but was not positive it was what you were refering to.

Also, will this function be able to calculate a date 
range and leave out just Sunday, vs. the entire weekend?

cp
>-----Original Message-----
>Hi
>if your dates are in a and B1 use
>=NETWORKDAYS(A1,B1,list_of_holidays)
>
>Note: the Analysis Toolpak Add-in has to be installed 
for this
>
>
>-- 
>Regards
>Frank Kabel
>Frankfurt, Germany
>
>
>cp wrote:
>> I am trying to count the number of days within a given
>> date range but leave out saturday's and Sunday's.  Is
>> there a way to do this?  Using the function Days360 
seems
>> to be the right path, and counts all the days between 
the
>> range, but I am not sure how to customize from there, 
if
>> it is even possible.
>> 
>> cp
>.
>
0
anonymous (74722)
5/19/2004 5:54:02 PM
Hi
it's included on your installation CDs. Goto 'Tools - addin' and check
this Add-in

--
Regards
Frank Kabel
Frankfurt, Germany


cp wrote:
> Do you know where I can download the toolpak? I use Excel
> 2000 on a Windows XP operating system. I did find a site
> on Microsoft at:
> http://www.microsoft.com/downloads/details.aspx?
> FamilyID=01902294-9e3f-487f-b7b0-
> e310fe2d5362&DisplayLang=en
>
> but was not positive it was what you were refering to.
>
> Also, will this function be able to calculate a date
> range and leave out just Sunday, vs. the entire weekend?
>
> cp
>> -----Original Message-----
>> Hi
>> if your dates are in a and B1 use
>> =NETWORKDAYS(A1,B1,list_of_holidays)
>>
>> Note: the Analysis Toolpak Add-in has to be installed for this
>>
>>
>> --
>> Regards
>> Frank Kabel
>> Frankfurt, Germany
>>
>>
>> cp wrote:
>>> I am trying to count the number of days within a given
>>> date range but leave out saturday's and Sunday's.  Is
>>> there a way to do this?  Using the function Days360 seems
>>> to be the right path, and counts all the days between the
>>> range, but I am not sure how to customize from there, if
>>> it is even possible.
>>>
>>> cp
>> .

0
frank.kabel (11126)
5/19/2004 6:03:02 PM
Thank you. Also, will this function be able to calculate 
a date range and leave out just Sunday, vs. the entire 
weekend?

>-----Original Message-----
>Hi
>it's included on your installation CDs. Goto 'Tools - 
addin' and check
>this Add-in
>
>--
>Regards
>Frank Kabel
>Frankfurt, Germany
>
>
>cp wrote:
>> Do you know where I can download the toolpak? I use 
Excel
>> 2000 on a Windows XP operating system. I did find a 
site
>> on Microsoft at:
>> http://www.microsoft.com/downloads/details.aspx?
>> FamilyID=01902294-9e3f-487f-b7b0-
>> e310fe2d5362&DisplayLang=en
>>
>> but was not positive it was what you were refering to.
>>
>> Also, will this function be able to calculate a date
>> range and leave out just Sunday, vs. the entire 
weekend?
>>
>> cp
>>> -----Original Message-----
>>> Hi
>>> if your dates are in a and B1 use
>>> =NETWORKDAYS(A1,B1,list_of_holidays)
>>>
>>> Note: the Analysis Toolpak Add-in has to be installed 
for this
>>>
>>>
>>> --
>>> Regards
>>> Frank Kabel
>>> Frankfurt, Germany
>>>
>>>
>>> cp wrote:
>>>> I am trying to count the number of days within a 
given
>>>> date range but leave out saturday's and Sunday's.  Is
>>>> there a way to do this?  Using the function Days360 
seems
>>>> to be the right path, and counts all the days 
between the
>>>> range, but I am not sure how to customize from 
there, if
>>>> it is even possible.
>>>>
>>>> cp
>>> .
>
>.
>
0
anonymous (74722)
5/19/2004 6:14:11 PM
Hi
for this you may use the following array formula (entered with
CTRL+sHIFT+ENTER):
=B1-A1-SUM(IF(WEEKDAY(A1-1+ROW(INDIRECT("1:"&TRUNC(B1-A1)+1)))=1,1,0))

--
Regards
Frank Kabel
Frankfurt, Germany


cp wrote:
> Thank you. Also, will this function be able to calculate
> a date range and leave out just Sunday, vs. the entire
> weekend?
>
>> -----Original Message-----
>> Hi
>> it's included on your installation CDs. Goto 'Tools - addin' and
>> check this Add-in
>>
>> --
>> Regards
>> Frank Kabel
>> Frankfurt, Germany
>>
>>
>> cp wrote:
>>> Do you know where I can download the toolpak? I use Excel
>>> 2000 on a Windows XP operating system. I did find a site
>>> on Microsoft at:
>>> http://www.microsoft.com/downloads/details.aspx?
>>> FamilyID=01902294-9e3f-487f-b7b0-
>>> e310fe2d5362&DisplayLang=en
>>>
>>> but was not positive it was what you were refering to.
>>>
>>> Also, will this function be able to calculate a date
>>> range and leave out just Sunday, vs. the entire weekend?
>>>
>>> cp
>>>> -----Original Message-----
>>>> Hi
>>>> if your dates are in a and B1 use
>>>> =NETWORKDAYS(A1,B1,list_of_holidays)
>>>>
>>>> Note: the Analysis Toolpak Add-in has to be installed for this
>>>>
>>>>
>>>> --
>>>> Regards
>>>> Frank Kabel
>>>> Frankfurt, Germany
>>>>
>>>>
>>>> cp wrote:
>>>>> I am trying to count the number of days within a given
>>>>> date range but leave out saturday's and Sunday's.  Is
>>>>> there a way to do this?  Using the function Days360 seems
>>>>> to be the right path, and counts all the days between the
>>>>> range, but I am not sure how to customize from there, if
>>>>> it is even possible.
>>>>>
>>>>> cp
>>>> .
>>
>> .

0
frank.kabel (11126)
5/19/2004 6:55:05 PM
Why don't you count ALL the days (easy enough), divide by 7 to get weeks,
and take 2 x [Number of weeks] off the total days ? You question further on
about only Sundays - take 1 x [Number of weeks] off. If you're worried about
part weeks, check first to see what day the period starts with, and work
from there.

Rob

"cp" <anonymous@discussions.microsoft.com> wrote in message
news:f4ff01c43dbc$b754f200$a001280a@phx.gbl...
> I am trying to count the number of days within a given
> date range but leave out saturday's and Sunday's.  Is
> there a way to do this?  Using the function Days360 seems
> to be the right path, and counts all the days between the
> range, but I am not sure how to customize from there, if
> it is even possible.
>
> cp


0
lepperrj (8)
5/20/2004 12:27:06 AM
Reply:

Similar Artilces:

Money Changes Transaction Dates
Hi all, How can I stop Money2004 from changing my transaction dates that I have already entered into the register? This happens when I accept/match downloaded transactions from my bank. Thanks! Well, I think I just answered my own question. I found a check box to untick under online options to fix this, but I will post back if necessary. "rustyfender04" <rustyfender1@hotmail.com> wrote in message news:eY3zaLITHHA.2124@TK2MSFTNGP06.phx.gbl... > Hi all, > > How can I stop Money2004 from changing my transaction dates that I have > already entered into t...

adding months to an inputted date
I need a function that will take a date that a user has typed in a different cell and will then add two months to the date. For instance, if I type "2/12/05" in B1, then I want C2 to be: "4/12/05". Thank you for any help that you may be able to give. Logan =DATE(YEAR(B1),MONTH(B1)+2,DAY(B1)) however what do you want the date to be in C2 if B1 is 01/30/05? Regards, Peo Sjoblom "BLW" wrote: > I need a function that will take a date that a user has typed in a different > cell and will then add two months to the date. For instance, if I type >...

Testing a range of cells in an IF FUNCTION
Trying to test a range of cells in an IF Function. I would like the function to look at 15 cells in a single row, find a value within those cells, and then return another value if TRUE/FALSE. As of now, I can only apply the "logical test" in the IF formula for ONE CELL ONLY. I would like it to test a RANGE of cells in one row, find if any of the values match my logical test, and then return a value. Anyone understand or know how to do this? I tried apply "lookup" function, but I don't think it will work Please help Aaro aaronplange at hotmail.com Hi maybe someth...

Convert date to months
I have a database that has multiple dates in it. What I am trying to do is in one field I have a date and I am want that date to convert to months in another field. Can anyone help me on this. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queries/200707/1 are you trying to find months between dates? or convert 4 to April? Look at format function for ways to display months, datediff function for months difference.. "bohon79 via AccessMonster.com" <u35329@uwe> wrote in message news:74ac2f3f40ce9@uwe... >I have a database ...

Static Chart Data Range
Excel-2007, simple line chart: I have a chart that displays several columns of data for rows 2:84. Whenever I insert a new row #2, the new data range for the chart is now 3:85. How do I keep a static data range for my chart (2:84) whenever I insert new rows into the workbook? I have tried editing $A$2:$E$84 to A2:E84 and then saving the changes ... but it is an excercise in futility, it always reverts back to $A$2:$E$84. I would recommend creating a blank row 2, and hiding it. Then, when you insert a new row, you'll be inserting within the boundaries, instead of moving ...

Last day of posting via Microsoft server
Well, I guess this is the last day of posting via the Microsoft server. http://www.teranews.com/ Your own free account ($3.95 one time setup fee) that allows posting or use public.teranews.com without an account (no posting & speed capped). You can use any standard news client you choose to read and post to any newsgroup. Or Google it: http://groups.google.com/groups/search?hl=en&q=microsoft.public.win98.gen_discussion&qt_s=Search God Bless America, Bill O|||||||O mailto:BillHughes@billhughes.com http://www.billhughes.com/jeep_bookmark.htm "D...

Name Range with using Data Form
I first created a variable with A1:D1, four columns with named variable Data. How to expand this Data range to A1:D2 after using the DataForm to add one row data in the worksheet?? The same, the Data range should be A1:D3 when adding one more data row... million thanks Hi use the following formula in the name definition dialog =OFFSET($A$1:$D$1,0,0,COUNTA($A:$A)) >-----Original Message----- >I first created a variable with A1:D1, four columns with named variable >Data. How to expand this Data range to A1:D2 after using the DataForm to add >one row data in the worksheet?? The...

Re: Function to copy data from a variable range?
I have a sheet with about 20,000 lines of data. From that sheet I am looking to group the data based on a parameter, into multiple different sheets. ie: If the category is "1" copy all that lines data into sheet A, If the category is "2", copy all the lines into sheet B. The problem is, the number of lines for each acatgeory type is not always the same. Some categories may only have 1 or two lines, other may have 200-300. Is there any type of function for this? Thanks! Doable but why not just use data>filter>autofilter -- Don Guillett Microsoft MVP Excel SalesA...

Range of an Excel Sheet
I want to open a linked Excel Sheet with the appropriate range. The excel filename / range I have is the following: C:\Documents and Settings\aparmar\Desktop\Book1.xslx!Sheet!R1C1:R5C2 I'm opening the following by stripping of the extra information : C:\Documents and Settings\aparmar\Desktop\Book1.xslx How do I select the Range in Excel using !Sheet!R1C1:R5C2 ? thanks, Amrit Command lines to open workbooks do not include specific sheets and ranges. To open to a specific sheet and range selection you must use VBA Name the range then add this code to Thisworkbook module of Book1...

Calendar/Dates Help
Hi All, I have an excel spreadsheet that lists every date in the year, with a particular code in the next cell. IE: Monday 3/01/2005 11M 22M 32M Tuesday 4/01/2005 11T 22T 32T Wednes 5/01/2005 11W 22W 32W Thursday 6/01/2005 11H 22H 32H Friday 7/01/2005 11F 22F 32F Saturday 8/01/2005 11S 22S 32S Sunday 9/01/2005 11N 22N 32N Monday 10/01/2005 11M 21M 33M Tuesday 11/01/2005 11T 21T 33T Wednes 12/01/2005 11W 21W 33W Thursday 13/01/2005 11H 21H 33H Friday 14/01/2005 11F 21F 33F What I need is to be able to search by the code eg "33T" and have all the dates listed for ...

Date comparison better method
Select col1, col2 from TableName where DateColumn BETWEEN '2010-06-01' and '2010-06-17 23:59:59.997' Select col1, col2 from TableName where WHERE DateColumn >= '2010-06-01' AND DateColumn < '2010-06-17 23:59:59.997' I am seeing in a project both the above methods of data range filering is happening in different SPs. I am trying to understand which is the better method of comparing two date values and why? [Btw i know BETWEEN considers both the upper and lower limit] Regards Pradeep I would say the following is the better approach: ...

Extracting Time from a cell that has both the date and the time
Hi Folks, I could do with some help here please. I am trying to extract the time only from a cell that has both the date and the time. Can anyone suggest a solution? Thanks in advance. :confused: -- Hani Muhtadi ------------------------------------------------------------------------ Hani Muhtadi's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26794 View this thread: http://www.excelforum.com/showthread.php?threadid=466177 If you just wish the time to display, set the cell format to Time. If you wish to use the time portion then =A1-Int(A1) will give you...

Add range options to Rec Dist report
Add additonal range options to the Receivings Distribution report - only by Audit Trail is allowed ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/default.mspx?mid=b0268d6b-4b6a-4805-9b52-e...

access a cell in a range
I have variables: dim myrange as Range dim myString as String myrange specifies a specific column. How can I specify the cell in row 1 of myRange so I can set it's contents to myString? Hi there keyser soze, (Fan of the movie, eh? ;) <g> ) Like so .. myString = myRange(1).Value HTH -- Regards, Zack Barresse, aka firefytr, (GT = TFS FF Zack) <keyser_Soze@usa.com> wrote in message news:1128697509.320303.12550@g49g2000cwa.googlegroups.com... >I have variables: > > dim myrange as Range > dim myString as String > > myrange specifies a specific ...

Get Start date of Week number and Year
I’d like to build the following expression in my query GetStartWeekNumber(DatePart("ww",[EnteredDate]), Year([EnteredDate])) So if EnteredDate = 11/3/2009 the function would return 11/1/2009 But GetStartWeekNumber does not exist as an Access Built-In Function. Is there another way to do this as an expression in a query? I’m not familiar with creating my own functions. Thanks. That would depend on how you define the start of the week... One option would be to get the day-of-week number of the date (in my system/setup, Monday is day 2), then subtract one less than that...

Sheet outline range.
How can I determine in vba the range occupied by a sheet? The edges of the sheet may be "ragged" ie. there will be blank cells within the range and at its edges - I want to determine the overall rectangular occupied outline to use in a Range statement. TIA Chris Depending on what you want, you may want to try .usedrange. msgbox worksheets("Sheet1").usedrange.addresss (for example) Be aware that excel keeps track of the usedrange in a way you probably don't--it remembers the last used cell, even if you've cleared that cell. Debra Dalgleish...

How do I sort data by date excluding time
I would like to sort a database by date and transaction type. The problem is the data sorts by time, even though the field is formatted to show date only, with the transaction type not sorting within the date because it shows up in the time order. How do I get rid of the time? If date/time column is A then you have to use an empty helper column filled with formula =INT(A2) and sort by it. -- Regards! Stefi „markd” ezt írta: > I would like to sort a database by date and transaction type. The problem is > the data sorts by time, even though the field is for...

Range error
Why does this return an error? Dim Unknown As Range Unknown = Range("K24") oldjay Range is an object and so you need to use the Set Statement which assigns an object reference to a variable or property. Refer help on Set statement.... Dim rngTemp As Range Set rngTemp = Range("K24") 'will refer to the activesheet cell K24 -- Jacob "oldjay" wrote: > Why does this return an error? > > Dim Unknown As Range > Unknown = Range("K24") > > oldjay Thanks I never know where to look in help. I think Help is...

how can i use countif function inside a filtered range
How can I use countif function inside a filtered range Hi, Not much detail here, so this is the general idea: =SUMPRODUCT(SUBTOTAL(3,OFFSET(C13:C18,ROW(13:18)-13,0,1)),--(C13:C18="Red")) -- If this helps, please click the Yes button Cheers, Shane Devenshire "jayin" wrote: > How can I use countif function inside a filtered range Shane Devenshire wrote on 02/24/2009 02:05 ET : > Hi, > > Not much detail here, so this is the general idea: > > =SUMPRODUCT(SUBTOTAL(3,OFFSET(C13:C18,ROW(13:18)-13,0,1)),--(C13:C18=&quot;Red&quot;)) > > ...

How do I use the "Correlation" dialog box with multiple ranges?
I'm using data in an Excel 2003 in a workbook, trying to see if there is any correlation between specific offices in which people work and the length of time they have been employed, their age the amount of sickleave taken and/or the amount of sickleave balance they have. I'm attempting to use Data Analysis and the Correlation dialog box. Although the dialog description tells me that I can use multiple comparison ranges, I don't see how to do it. Thanks for any help. Paulf6 - Arrange your data in list (database) format, i.e., names in the top row (Time Employed, Age, ...)...

Prevent excel from changing numeric data ranges into dates?
I want to prevent excel from changing my numeric data into dates (ie. 10-12, into 12-Oct). I want to import specific data ranges, not a date values. Whenever I paste or type a possible date, like 10-12, excel assumes I am writing Oct. 12, 2004. Then, Excel formats the cell into a Custom d-mmm format (see scrn shot @ www.vd4.org/excel_issue.gif). If I change the Custom format to General, excel converts the date into the DATE'S value (ie. 12-Oct, into 38272; which I think is the # of days since 1900?). How can I *prevent* excel from changing my data ranges into dates (ie keep 10-12 ...

Counting dates
I am trying to count the number of dates in a column that appear b month and year. For instance, I some that are 11/23/2003 and 11/24/200 and 11/21/2002. How can I makew a formula that will count the instance of 11/2003 -- Message posted from http://www.ExcelForum.com =SUMPRODUCT((YEAR(A1:A100)=2004)*(MONTH(A1:A100)=11)) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "jdavis3874 >" <<jdavis3874.14770b@excelforum-nospam.com> wrote in message news:jdavis3874.14770b@excelforum-nos...

Cannot view inbox by date
After a successful test of an Exchange 2003 High Availability product last night a few users have reported issues in Outlook this morning. They cannot view/arrange their Inbox by Date. We have tried /cleanviews switch to no avail. All other views work fine. Any ideas so I can prevent this happening again? Thanks PocketJacks <dallano@hotmail.com> wrote: > After a successful test of an Exchange 2003 High Availability product > last night a few users have reported issues in Outlook this morning. > They cannot view/arrange their Inbox by Date. What happens when they try? -- ...

Recognise a payee with date and time in it
Hi Everyone Each time I withdraw cash from my bank account, it puts it in my statement as: 21Mar 10.13 Well't Sq for example. So each time I withdraw cash, the date and time are going to be different. I want money to recognise on my downloaded statements that any statement line with Well't Sq in it, means a cash withdrawal - i.e. ignore the date and time as obviously that will be different everytime I take cash out. Is there anyway of getting Money to recognise part of the payee rather than thinking all my cash withdrawals are different payees? Thanks! Hannah Quicken doe...

Summing with a range of number
Hey Everyone, I have a question about summing via ranges. I am trying to write macro and have hit a bump. Here is the scenario. I have a 2 columns of data (A&B). Column B i sorted in ascending order. Now I have ranges of data in column A that need to sum. Lets say in column B I need all numbers from 100 to 300 From 100 to 300 I need the totals in column A to sum at the last numbe that is less than 300 but greater than 100. I would like the sum to b in colum C for the totals of column A within the ranges of column B. hope that makes sense. If not ask for clarification or I will try an...