Datedif exclude weekends

Hello newsgroup gurus and users.
Your kind assistance please.


This question has probably been asked a millions times but 
I cant find it.

Does any one have a solution to this.

I want the date difference between two dates but exclude 
weekends.

Many thanks 
Paul

0
anonymous (74719)
10/16/2003 10:54:39 AM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
864 Views

Similar Articles

[PageSpeed] 8

Paul,

The NETWORKDAYS function will return the number of days, excluding weekend
and optionally holidays, between two dates. See help for NETWORKDAYS for
more information.  This function is part of the Analysis Tool Pack add-in so
you must have that add-in loaded; otherwise, you'll get a #NAME error.


-- 
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com    chip@cpearson.com

"Paul" <anonymous@discussions.microsoft.com> wrote in message
news:0d7101c393d3$e5419990$a301280a@phx.gbl...
> Hello newsgroup gurus and users.
> Your kind assistance please.
>
>
> This question has probably been asked a millions times but
> I cant find it.
>
> Does any one have a solution to this.
>
> I want the date difference between two dates but exclude
> weekends.
>
> Many thanks
> Paul
>


0
chip1 (1821)
10/16/2003 10:58:24 AM
Reply:

Similar Artilces:

formula for cycle time from date -to-date w/o weekend included
I am tracking number of days between two date points with the =L933-K933 formula. With formula it give the total days. K933 | L933 | days 10/1/03 | 10/6/03 | 5 With this example, it includes the weekend in the formula. Is there a way to tell my formula to only include business days? Steve Steve, Lookup NETWORKDAYS in help. It's part of the Analysis Toolpak addin, so you will need to install that. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Steve" <feelin_irie2001@yahoo...

Calandar weekends crunched? (mps)
I just installed Outlook to start over. but i noticed that the calander shows the weekends condenced. ie sat and sun share a space. it wasn't like this before. i can't figure out how to change it. any ideas? TIA Mike View, Current View, Customize Current View. Click the Other settings button and uncheck the box marked "compress weekend days". -- Patricia Cardoza Outlook MVP www.cardozasolutions.com Author, Special Edition Using Microsoft Outlook 2003 ***Please post all replies to the newsgroups*** "Michael Smith" <yeahright@nonya.com> wrote in messa...

Excluding a category from an autofilter
I have a spreadsheet set up with an autofilter on one of the columns. There are 6 different types of values within this column (say: a,b,c,d,e,f&g). It's easy enough to use the filter to display any one of these categories individually, but how do you set it to 'exclude' one of the values (ie: only display values a,b,c,d,e&f, but exclude g? - Thanks Change the criteria to not equals. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Grant" <grant.johnson@tgarch.co....

Exclude Row in Sort
I have some data with headers and a subtotal row. Row 1 is the headers, row 2 is the subtotal and row 3 on is the data. I can sort the data without losing the subtotal if I put the subtotal above the headers, but I don't like that layout. Is there a way to exclude the subtotal row from the sort? On Thu, 6 Oct 2011 16:13:50 -0700 (PDT), Pablo wrote: >I have some data with headers and a subtotal row. Row 1 is the >headers, row 2 is the subtotal and row 3 on is the data. I can sort >the data without losing the subtotal if I put the subtotal above the >headers, but I don't...

rounding the DATEDIF function
Is there a way I can round the DATEDIF function so that partial months are counted as a whole month. For example here is what i want my spreadsheet to say; start date end date total months 6/25/05 7/31/05 2 right now my function makes total months equal to 1. Thanks for any suggestions. -- speary ------------------------------------------------------------------------ speary's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24959 View this thread: http://www.excelforum.com/showthread.php?threadid=388479 This seems to work for your ...

Use Datedif but for future dates
I have a formula for identifying years, months, days from a past date to now. DATEDIF(C6,NOW(),"y")&" Y, "& DATEDIF(C6,NOW(),"ym")& " M, " & DATEDIF(C6,NOW(),"md") & " D" I'd like a formula that can produce the same format (years, months, days) between now and a future date. Any ideas? Thanks in advance, Bart Hi Bart Try this with the date in A2 =IF(TODAY()<=A2,DATEDIF(TODAY(),A2,"Y") & " y " & DATEDIF(TODAY(),A2,"ym") & " m","-"&...

DATEDIF and leap years
I am using this formula to get the age of someone in years and days. It works well execpt I cannot find a way of adjusting for leap years. Any ideas =DATEDIF(A1,B1,"Y")&"YEARS"&DATEDIF(A1,B1."YD")&"DAYS" What isn't adjusting for you? A1: 2/28/2001 B1: 3/1/2004 <== Leap year C1: 3 YEARS 2 DAYS A1: 2/28/2002 B1: 3/1/2005 <== Non leap-year C1: 3 YEARS 1 DAYS Years are insensitive to leap-days (except for those born on 29 February, I suppose), so only the last partial year matters. It appe...

How do I exclude a column from printing
I would like to leave out a column I have for adding tax on an invoice. Some items are taxable whereas others are not. I have it set up where if I put a T in this column it will add tax, but I don't want the customer to see this column when the invoice is printed. Any help is appreciated Hi Hide the column - right mouse click on it, choose Hide to get it back after printing select the columns both sides of the hidden one by dragging from one to the other, right mouse click and choose unhide Cheers JulieD "The Bug" <TheBug@discussions.microsoft.com> wrote in message ...

Exclude Data of Access
Dear all, I'm trying to exclude some data in my access table, but the code bellow doesn't work. I need that excel to exclude all data in the table that contains a criteria in two range of excel. It's the message error: "no value was supplied for an or more necessary parameters" For example: Table accounting_code profit_center date value salary 3 01/01/07 100 I have the range a1 for the profit_center and range a2 for the date. Then I will put in range a1 =3D 3 and a2 =3D 01/01/07, then I will click in a butto...

Question
Hello I have a question for Excel knowers I have a list of items in column A I also have a list of items in column B Some of the items in A and B are duplicates I'd like to obtain a C column sustaing of items that appear in column A but NOT appear in column B Can you help me solving my problem? Please mail me on priv playa(at)poczta.fm Kind Regards, Jakub Lenczowski Krakow, Poland Put this in C1: =if(iserror(match(a1,b:b,0)),a1,"APPEARS IN COLUMN B") and drag down the column. Then select column C and edit|copy edit|paste special|values Sort column C (include columns A...

Getting MEDIAN and PERCENTILE to exclude #ERROR values
Is there a way to get PERCENTILE or MEDIAN to exclude error values? I give as an argument a range in which some of the values are #VALUE, but rest are valid, legal numeric values, and it is those remaining non-error values that i want MEDIAN or PERCENTILE of. Thank you. Try the following formulas, which need to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER... =MEDIAN(IF(ISNUMBER(A2:A100),A2:A100)) and =PERCENTILE(IF(ISNUMBER(A2:A100),A2:A100),0.8) Adjust the references and percentile value accordingly. Hope this helps! In article <UTqfg.2573$hv1.1679@trnddc01>, &...

Exclude asset revaluations from category spending analysis?
Hello, How can I exclude occasional revaluations of car, home etc from category reports? As my car depreciates it effects net worth, but I do not regard it as "spending". But by default it shows up in reports of spending by category. RichT wrote: > Hello, > > How can I exclude occasional revaluations of car, home etc from > category reports? As my car depreciates it effects net worth, but I > do not regard it as "spending". But by default it shows up in reports > of spending by category. When you record the depreciation, say of your car, categor...

DATEDIF
Hi I found on excel helpfile the description of the functin DATEDIF but it does not exsit on excel function list. Can you help me find it? Thank you Shaul Shaul, The answer to your question can be found on Chip Pearson's site. Had you not multiposted to all the ng's, I'd have given you the direct link to what you want. Since you did, though, visit this site: http://www.cpearson.com/excel/newposte.htm then go to his home page and search for your answer. John "����" wrote: > Hi > I found on excel helpfile the description of the functin DATEDIF > but it does ...

Re-assigning weekend days in "Networkdays" Function
Hi everyone, I would like some help on the "Networkdays" funtion. I need t re-assign the default weekend days in this funtion (Saturday & Sunday to Thursday & Friday. For example: note: date format is mm/dd/yy =NETWORKDAYS(A1,A2) where A1= 01/14/2004 (day being Saturday) & A2=01/25/04 (day being Wednesday) will return the value of 8 because it subtracts the 4 default weeken days (2 Saturdays & 2 Sundays) automatically. I need it to return th value 10 for the alternative 2 weekend days (1 Thursday & 1 Friday). I want MS Excel to read Saturdays & Sunday...

networkday
Hi In Cell A1 i have 4/4/08 In cell B1 i have 4/4/08 I have tried networkdays(a1,b1) but this gives result as 1. I require result as 0. when cell B1 reads 5/4/08, will require result 1 Datedif does give the required result, but does not exclude weekends any ideas/direction all help appriciated Brian -- Message posted via http://www.officekb.com One way: =NETWORKDAYS(A1, B1) - 1 In article <825feba2f62fa@uwe>, "BNT1 via OfficeKB.com" <u19326@uwe> wrote: > Hi > > In Cell A1 i have 4/4/08 > In cell B1 i have 4/4/08 > > I have tried netw...

Exclude a cell again
Thx that will help with part of my problem but how would I make A16 variable.........for instance.....the last cell I enter could be A17 not A16 but I don't want to keep going back to my formula to amend it........ "Ardus Petus" <ardus.petus@laposte.net> wrote in message news:uBCw3W1oGHA.2444@TK2MSFTNGP03.phx.gbl... > To exclude cell A16: > =SUM(A:A)-A16 > > Not very elegant, but effcient! > > Cheers, > -- > AP > > "DF2507" <nospamthx@blueyonder.co.uk> a �crit dans le message de news: > xB6sg.51172$181.12831@fe3.news.blueyo...

datedif #2
Hello, I am working on Excel 2003 in an XP sp2 environment. I have a date in cell C7 and C6. I want to get the difference between them to use to pro-rate vacation time accrued. I have the Norman Harker Function list and was trying to use datedif. It has the function listed as =DATEDIF(start_date,end_date,unit) with units as "y" = years; "m" = months; "d" = days; "md" = days ignoring months and years; "ym" = complete months after deducting years; "yd" = days after deducting complete years. (Note: " " are required...

Distribution List Members excluded
I need to save/export my entire contacts list - used to do this easily in Outlook Express with the .wab files - all efforts in Outlook 2003 have failed to date. In Outlook 2003 - When I export my contacts to a .pst file, individual contacts are properly preserved and distribution list TITLES are saved. But the members in each distribution list are excluded. When I export my contacts to a .csv or .tsv file, individual contacts are properly preserved but distribution list titles are not even saved - everything about distribution lists is totally excluded. Help database says I should b...

DATEDIF date format
Hi Is there a way of using the DATEDIF function using the UK way of displaying dates? (dd/mm/yy) if i want to use it I have to format the dates referred to in US style. My regional settings in control panel ar all set to UK spec and the dates in other parts of Excel all display in this manner. Thanks Dave Dave, What do you mean you have to have the dates in US style? I also have UK date style, and I put dates in UK style in A1 and A2, and did a DateDif, no problems. I also tried =DATEDIF("01/08/2005",A2,"m"). no problems. It shouldn't make a difference, as date...

Counting Days between two Dates excluding weekends
I would like to count the number of working days between two date, For example a date on a Friday and a date on the following Monday will count as 1 Day not 3 Days Is there a way to do this in an query Thanks "Simon" <S.Dickson@shos.co.uk> wrote in message news:405c914a-bb14-4df6-b992-41aefc5643ff@k17g2000yqb.googlegroups.com... >I would like to count the number of working days between two date, > For example a date on a Friday and a date on the following Monday will > count as 1 Day not 3 Days > > Is there a way to do this in an query You n...

datedif; what's up?
I have found a use for this excel function but wonder about its mysterious nature (undocumented?). Does anyone else use it? Excel2003+ Chip Pearson has some very nice notes: http://www.cpearson.com/excel/datedif.aspx cate wrote: > > I have found a use for this excel function but wonder about its > mysterious nature (undocumented?). Does anyone else use it? > Excel2003+ -- Dave Peterson You might want to reconsider using the DATEDIF function. It is an undocumented (and, thus, probably an unsupported) Excel function which appears to be broken in XL2007 at Se...

Data Validation to exclude blank cells
Hi, Please help... I have a list spreadsheet with invoice details on it. SHEET 1 - Column A lists invoice numbers. Column B lists individual items on the invoice (i.e. parts sold). Further information in column C to F (invoice date, amounts, etc) On SHEET 2, I want to create a validation list which you can use to select invoice numbers. Once an invoice number is selected, vlookup will be used to pull through information in column C to F. I know how to use vlookup, and to create custom lists so there is no problem there. The problem I do have is column A has blanks in it (i...

DATEDIF(); an alternative available?
I wouldn't have a clue how to write a udf to replace this. In another post, in this group, I was informed that this undocumented excel function was broken in 2007 (in a service pak). Is an alternative available? Thank you. http://groups.google.com/group/microsoft.public.excel.programming/browse_thread/thread/51cbabedf5f2985d/f15b5e7e4a17c106#f15b5e7e4a17c106 Hi, Datedif was part of the ATP in E2003 butis now built in to E2007 but remains undocumented. have a look here http://www.cpearson.com/excel/datedif.aspx Mike "cate" wrote: > I wouldn...

Sort and exclude certain words
Hi ... I work in a public library and frequently export lists of books from our online catalog to Excel, so that I can manipulate the data in various ways. My problem in sorting in Excel is this: Our library catalog excludes words like "The" "A" or "An" at the beginning of a title when sorting. Excel, however, does not. Is there any way I can sort on a field in Excel and have it ignore certain words at the beginning of a string? Thanks! -- jds217 ------------------------------------------------------------------------ jds217's Profile: http://www.exc...

DATEDIF calculates a month off
Suppose my start and end dates are in A1 & A2 respectively and I want to count the number of months between them A1: 09/01/04 A2: 01/31/05 =DATEDIF(A1,A2,"M") returns 4, instead of the correct count of 5. What's up with that? Anybody? Thanks. M&M DATEDIF(...,"m") counts only whole months, presuming that days start at midnight. So your span is only 4 months and 30 days long, not 5 months. Of course, the whole concept of months is rather squirrelly. For instance, one month after 1/30/05 - is it 2/28/05 (29 days), 3/1/05 (30 days)?, 3/2/05 (31 days)? ...