Excel formulas #3

I am using a workday fomula to fill in dates across my spreadsheet. The 
formula also removes holidays. I need to include Saturdays as a workday. How 
can I accomplish this?
0
7/15/2008 3:30:02 PM
excel 39879 articles. 2 followers. Follow

2 Replies
893 Views

Similar Articles

[PageSpeed] 29

=B1+SMALL(IF((WEEKDAY(B1+(ROW(INDIRECT("1:"&10))))={2,3,4,5,6,7})*
ISNA(MATCH(B1+(ROW(INDIRECT("1:"&10))),holidays,0)),ROW(INDIRECT("1:"&10))),1)

this is an arry formula, so commit with Ctrl-Shift-Enter

-- 
__________________________________
HTH

Bob

"aviationmech" <aviationmech@discussions.microsoft.com> wrote in message 
news:D014DA1C-4B9F-4285-8C28-C80436252137@microsoft.com...
>I am using a workday fomula to fill in dates across my spreadsheet. The
> formula also removes holidays. I need to include Saturdays as a workday. 
> How
> can I accomplish this? 


0
BobNGs (423)
7/15/2008 5:27:40 PM
"Bob Phillips" <Bob...@somewhere.com> wrote...
>=B1+SMALL(IF((WEEKDAY(B1+(ROW(INDIRECT("1:"&10))))={2,3,4,5,6,7})*
>ISNA(MATCH(B1+ROW(INDIRECT("1:"&10))),holidays,0)),
>ROW(INDIRECT("1:"&10))),1)
>
>this is an arry formula, so commit with Ctrl-Shift-Enter
....

Or

=B1+MATCH(1,(WEEKDAY(B1+{1,2,3,4,5,6,7},2)<7)
*(COUNTIF(Holidays,B1+{1,2,3,4,5,6,7})=0),0)

which doesn't require array formula entry.
0
hrlngrv1 (375)
7/15/2008 8:23:54 PM
Reply:

Similar Artilces:

How do I grant access to allow a user to save an Excell file to t
Where can I find info on admin functions for Excell? What do you mean "admin functions"? That phrase doesn't apply to anything built in to Excel. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "TomK" <TomK@discussions.microsoft.com> wrote in message news:3757C74B-131F-46EB-98EC-182986D1645E@microsoft.com... > Where can I find info on admin functions for Excell? I am trying to save a file to the D: drive (cd) and I am getting an error message that I need an administrator to clear me for access to t...

Can Excel graph a line from an equation? y=3x+2
Can Excel do linear programming problems? I'm wondering if Excel can graph from an equation rather than data points and can it identify points of intersection? Hi, Have a look at Stephen Bullen's ChtFrmla example. http://www.oaltd.co.uk/Excel/SBXLPage.asp#Charting To calculate intersection you will probably need to create all the points in cells in order to use formula, which sort of negates the need for the non cell based approach Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "GraphLady" <GraphLady@discussions.microsoft.com> wrote ...

Se me cuelga el excel cuando abro un determinado fichero
Hola! Tengo un problemilla! y es que excel se me cuelga cuando intento abrir un fichero. Este fichero tiene 29 hojas y ocupa 941KB. Es un fichero que tiene much�simas f�rmulas que extraen los datos tanto de archivos de las otras hojas de este archivo, como de archivos externos. Es caso es que cuando lo abro, y le digo que s� a lo de vincular el archivo con los archivos externos, (para que se actualicen las f�rmulas) se me cuelga el excel. Creo que puede ser porque es un archivo muy grande y se ralla. Como lo puedo solucionar?? Much�simas gracias! Hola! Te sugiero que poste�s tu pregunta ...

Printing Color-Coded Worksheet in Black & White Mode in Excel 2003
I work with farily complex Excel worksheets which require print outs as both hard copy and PDF. I normally keep the formatting as they are and select [Black and White] in [Page Setup] [Sheet], as this preserves the integrity of the financial model while printing out a clean copy for the enduser. However, this function basically converts everyting color-coded to black & white, including cell shading patterns; I normally accentuate certain important cells by shading them in black and using a white font color. While I want the output to be black & white, I do want to preserve this p...

Opening two separate instances of Excel
My users (who are accountants) use dual monitors and would like to open a different Excel file on each monitor for the purposes of reviewing changes between the two. I know that one method of doing this is by opening Excel twice with its shortcut, then highlighting one Excel window and opening an Excel file, then highlighting the other window and opening the other Excel file. Unfortunately, the software that my users use to organize the Excel files does not allow this to be done; it always opens every Excel file in the first Excel window no matter which instance is highlighted. We had a...

How do I link Excel chart axis scale settings to cells?
I want to link a chart's axis scale settings to cells. Is this possible? Hi, You can not directly link the scales setting to cells. Tushar has an addin which help. http://tushar-mehta.com/excel/software/autochart/index.html Cheers Andy strince wrote: > I want to link a chart's axis scale settings to cells. Is this possible? -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info Please ignore...I found the answer in another post. "strince" wrote: > I want to link a chart's axis scale settings to cells. Is this possible? ...

Copy only formulas??
Hi ! I am at work now, and I hope this procedure can be done before I leave tonight I have a column A full of values that were copied/PastedSpecialValues from other spreadsheets (no other choice than to copy/pasteSpecialValue) In this example, cell A5 is the value of the sum A1:A4...but it is a VALUE In colum B, I have a series of formulas (but no data) that I need to copy in column A, without changing the actual valid data in column A The result is that I would like to have in cell A5 =sum(A1:A4) I have tried combinations of PasteSpecialFormulaAdd and others, but does not get the resul...

formula only works when file is saved.
For some strange reason my formulas do not work until I save the sheet.I am using 2007 with a file that was in 2003. Never had a problem before. It is in compatibility mode. when I enter the data nothing happens in the cell where the result is suppose to go. If I close and save the result shows up. If I open the file back up and take out the data I put in the result is still there, where it should be a zero. Something is screwed up. Multiple people have access to this file. Sorry about the post . I found the problem. Somebody changed calculations to manual "bob" <...

Formula turns into text after entering in the first value.
This is a multi-part message in MIME format. ------=_NextPart_000_0001_01C61E01.A0698BB0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit Hello, I have an issue. I created an Invoice spreadsheet integrated with VB and using a form. What my project does is Opens an Excel spreadsheet. There is a button that says: New invoice. My macro, clears all input'd data on the sheet, changes the invoice number to the next sequential number (1400, then goes to 1401), saves the worksheet and opens the form attached. The data can be inputted easier from ...

Opening Excel Spreadsheet on a tab #2
When I say tab, I of course mean a sheet -- david.willis ------------------------------------------------------------------------ david.willis's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=14697 View this thread: http://www.excelforum.com/showthread.php?threadid=263201 One way: Put this in your ThisWorkbook code module: Private Sub Workbook_Open() Sheets(1).Select End Sub If you're not familiar with macro, see http://www.mvps.org/dmcritchie/excel/getstarted.htm In article <david.willis.1d3hhz@excelforum-nospam.com>, dav...

Open file Slow on Excel 2003
We have upgraded our Office 2000 to 2003 and after the upgrade the response time to open a file on the network or C drive is slow. Once you have click on down arrow next to the look in box it takes a few seconds to respond. All the patches have been loaded. We are running windows XP Any suggetions ...

Import into CRM 3.0 Contacts?
What's the easiest way to get Outlook Contacts imported into CRM 3.0 Contacts? Per... use this way http://www.microsoft.com/dynamics/crm/using/configure/outlookimport.mspx -- Regards, Imran MS CRM Certified Professional http://microsoftcrm3.blogspot.com Chat with me on MSN / Gmail / Skype : ID Is :.. mscrmexpert@gmail.com "Per Moeller-Olsen" wrote: > What's the easiest way to get Outlook Contacts imported into CRM 3.0 > Contacts? > > Per... > > > Thanks. Will try that. Per... "Imran-mscrmexpert" <mscrmexpert@gmail.com...

Excel Not Talking To Outlook [Email Settings] #2
This is a follow up to an older thread on the same problem. Search groups.google.com for the same subject and you'll find the previous thread (which lacked an answer). I'm just posting here for posterity. When sending email from Excel and expecting to see your Outlook contacts box come up for you but instead you see a strange box letting you choose "shared contacts" or "this identity's contacts", the answer is that Internet Explorer is set up to use Outlook Express for email instead of Outlook. Open IE, go to Tools > Internet Options > Programs tab. Chan...

saving attachments #3
I have Outlook Express and I am unable to save attachments from e-mail messages. They show up but they are greyed out and I can not open them or save them Thanks Look here for help and more info on this subject: http://www.omegageek.com/OESecurity.htm -- Jocelyn Fiorello MVP - Outlook *** Replies sent to my e-mail address will probably not be answered -- please reply only to the newsgroup to preserve the message thread. *** "Michael" <lundmich@yahoo.com> wrote in message news:032e01c34fd5$c85492c0$a601280a@phx.gbl... > I have Outlook Express and I am unable to sav...

how do I insert multiple rows in excel after every row of data
I need to insert 23 rows after every row that is currently in my spreadsheet For i = Cells(Rows.Count,"A").End(xlUp).Row To 1 Step -1 Cells(i+1,"A").Resize(23,1).Entirerow.Insert Next -- HTH RP (remove nothere from the email address if mailing direct) "grantm5" <grantm5@discussions.microsoft.com> wrote in message news:40AB897A-4D38-46AF-934A-2451496EEEC5@microsoft.com... > I need to insert 23 rows after every row that is currently in my spreadsheet ...

CRM 3.0
Hi everyone, How do I set up Services so that I can schedule service activities? I have followed the instructions on the help file (ie: Settings->Settings->Services) but I cannot find "Services" in the Settings section. Any ideas? Thanks Rael Can you check if your role has "Service Management" related privileges? If you have the right privileges, you should be able to see the Services link in Settings (right after the link to Product Catalog)... -- Jagan Peri Microsoft Business Solutions CRM This posting is provided "AS IS" with no warranties, a...

Excel 2003 calculation set to manual after an update was run
Hi I have an user which uses alot of formulas. Updates ran about 2 weeks ago and since then any spreadsheets she worked on she needs to set the calculations from manual to automatic. When she does start a new spreadsheet calculations is set to automatic, but I need fix the problem with the previous spreadsheets. This setting is saved with the workbook. Just open each of the manual workbooks, set the mode to Automatic and save the workbook. -- Gary''s Student - gsnu200735 "AntonZA2" wrote: > Hi > > I have an user which uses alot of formulas. Updates ran a...

Excel Test With Template Wizard
Hi, I�ve been asked to look at making past exam papers into online tests. I don�t know anything about programming, so the way I�ve done it is t use form controls in excel. I was thinking I could set it up as template with data tracking on the network so people could open a cop on their machine, then when they save it, it would feed back into database that could do the marking. I�m ok as far as designing the worksheet so it has option buttons etc for each question. My first problem is that, having created the worksheet, once it i protected, I can�t select any of the option buttons, even...

Unable to save excel worksheet to cd
Cannot save excel worksheet to cdrw. Error message says file is read only. "Deborah Short" <dshort@gcnetmail.net> wrote in message news:065101c35a8a$61ec03f0$a601280a@phx.gbl... > Cannot save excel worksheet to cdrw. Error message says > file is read only. Save to HD, then copy to CDRW. ...

Excel 2007 startup
How do I start excel 07 with no blank workbook opening? On previous versions one could start the excel program without opening the blank default workbook, but, I can not find how to shut this off in 2007? "RPB" <RPB@discussions.microsoft.com> wrote in message news:189FFE77-92D4-4276-BE0E-82A85B0BA1EC@microsoft.com... > How do I start excel 07 with no blank workbook opening? On previous > versions > one could start the excel program without opening the blank default > workbook, > but, I can not find how to shut this off in 2007? Well I've been using Ex...

paste special picture from Excel 2007 to Publisher 2007 doesn't work
I have a group of charts with text in surrounding cells that I want to copy and paste special picture enhanced metafile from Excel into Publisher. I have around 200 groups of these charts to paste into the Publisher file. What happens now is that the paste special command only reveals three options: new table, new text box, and HTML. When I copy paste special into Word the picture enhanced metafile is accessible and the image pastes fine. In Publisher pasting results in worksheet cells and the charts all in pieces and messy. I could copy the Word image into Publisher, but UGH!!200 TIMES. There...

Dates in Excel #8
Why does Excel assume that when you type December 2004 that you want the numeric equivalent and not a text entry? Is this just another illustration of Microsoft knowing what we need before we know it? Thanks I don't know why MS assumes this but you can precede the entry with an apostrophe and it will be text -- Regards, Peo Sjoblom "FJB" <FJB0623@aol.com> wrote in message news:1109089977.316088.311020@g14g2000cwa.googlegroups.com... > Why does Excel assume that when you type December 2004 that you want > the numeric equivalent and not a text entry? Is this jus...

Excel Chart Lines
I had 2003 and recenlty upgraded to 2007. I have a chart that the lines on the Y axis are all scrunched together to practicly make 1 bar instead of the 4 that should be see. I think it's possibly formatting... EggHeadCafe - Software Developer Portal of Choice Using the Intercepting Filter Pattern to create a Generic Reusable Processing Pipeline http://www.eggheadcafe.com/tutorials/aspnet/ce017b7f-19e5-4586-b227-c7f21cbe1b3c/using-the-intercepting-fi.aspx ...

Missing e-mails #3
I have a serious problem with e-mails. When we send e-mails to a couple of domains and they try to use the 'reply' function in their e-mail systems to reply we do not recieve the mails. I've checked our queues and logs and the mails are not even getting to the queues at the hosts of our domains. Is there anything in outlook that could foul the reply path? Test mails replied to from both Hotmail and Navigator 7.1's e-mail client are coming through fine. Thanks, Ed Handley ...

crm 3.0
We put item serial numbers in the Invoice Products and print them on the Invoices. I would like to be able to view and search ALL Invoice Products to find them based on the Serial Numbers. Product Invoice [x] box is disabled in customize entity. How can I change this to enable so that the entities can easily be part of the Services menu? Our system is highly customized so I will accept any ideas or suggestions. ...