returning a date 7 business day excluding holidays in the future

How would I write a formula that would do this.  If I input a date in cell A1 
and I have holidays in cells R1:R11.  Can I write a formula that would give 
me a date in cell B2 that is 7 business days excluding holiday from the date 
in A1.  

I am having a hard time getting the networkdays and holidays working if I am 
not inputing 2 dates.

Thanks
0
Utf
2/25/2010 6:09:01 PM
excel.worksheet.functions 4936 articles. 2 followers. Follow

2 Replies
1481 Views

Similar Articles

[PageSpeed] 36

>I am having a hard time getting the networkdays
>and holidays working

Use the WORKDAY function:

=WORKDAY(A1,7,R1:R11)

Format as Date

-- 
Biff
Microsoft Excel MVP


"excelrookie" <excelrookie@discussions.microsoft.com> wrote in message 
news:6B1A82D4-15C6-4FBE-9E6A-B3C2CDF7B9E4@microsoft.com...
> How would I write a formula that would do this.  If I input a date in cell 
> A1
> and I have holidays in cells R1:R11.  Can I write a formula that would 
> give
> me a date in cell B2 that is 7 business days excluding holiday from the 
> date
> in A1.
>
> I am having a hard time getting the networkdays and holidays working if I 
> am
> not inputing 2 dates.
>
> Thanks 


0
T
2/25/2010 6:19:37 PM
Thanks guys but I figured it out  workday with holidays excluded :)

"excelrookie" wrote:

> How would I write a formula that would do this.  If I input a date in cell A1 
> and I have holidays in cells R1:R11.  Can I write a formula that would give 
> me a date in cell B2 that is 7 business days excluding holiday from the date 
> in A1.  
> 
> I am having a hard time getting the networkdays and holidays working if I am 
> not inputing 2 dates.
> 
> Thanks
0
Utf
2/25/2010 6:46:01 PM
Reply:

Similar Artilces:

date taken for photos copied
date taken for photo copied, actually modified date on any documents copied in explorer tends to changed to the Now (date and time of copying). especially annoying for folders anyway to change this default behaviour? On 5/25/10, GS posted: > date taken for photo copied, actually modified date on any documents copied > in explorer tends to changed to the Now (date and time of copying). > especially annoying for folders > anyway to change this default behaviour? Use the value in the metadata (tags)? -- Gene E. Bloch (Stumbling Bloch) anyway to cha...

Help adding holidays...
Hi.. using Outlook 2000. I have added US and Christian Holidays to Outlook, but I'd like to add more Catholic Feast/ Saint days... Is there a website that has these? Or, if I add them manually, how do I do that? Would they simply be a recurring/annual event? I ask because the Holidays in Outlook are not set as recurring! Saint Days are usually on a certain annual date.. thanks! B'Regards, Vinnie See if this site has anything to help: http://www.calendar-updates.com/ Yup, I remember that Saint's days are on particular calendar days... makes it easier to do a recurr...

Return cell address
Is there a formula or function that will return the cell address? Example: using cell range B1..B10 in which one cell will have an input value of 1. If cell B2 has a value you 1, is there a formula that will look at all cells in the range and return "B2" indicating that B@ is the cell w/the value? Thanks. Hi Dan =CELL("address",INDEX(B1:B10,MATCH(1,B1:B10,0))) Cheers JulieD "Dan" <anonymous@discussions.microsoft.com> wrote in message news:170401c49c08$a7835510$a401280a@phx.gbl... > Is there a formula or function that will return the cell > ad...

Conditional formatting: How to set condition "formula" with is "date" formatted
I want to use Conditional Formatting. I opt for "formula". The condition should be that a cell F9 that is formatted to Date 14-Mar-07 (see cell format) need to be filled with a date (any date). If empty then FALSE. What is the formulah I need to fill in. DATE(F9<>0) failed for me. This is the condition that is set for cell H9. Thank you. Bart Excel 2003 =AND(LEFT(CELL("format",F9),1)="D",--RIGHT(CELL("format",F9),1)<6,LEN(CELL("format",F9))=2,LEN(F9)>0) -- --- HTH Bob (there's no email, no snail mail, but somewhere sho...

Windows 7 Newsgroup
I have tried to find the windows 7 Newsgroup in this list of Newsgroup where this one on Vista is, but I do not get a hit. Is there one in this group that I can add to my list and use to ask some questions? Also, a second question: Windows Vista has a lot of pretty and nice graphics, and pretty colors, etc, but it is very difficult to find some thing there when you are lost and the folder systems is extremely complicated sometimes. I use Vista now, have had it for about two years now, and I still have difficulty finding the logic of the folder system sometimes. I don...

Business Alerts #10
I'm getting the following message after i upgraded from 9.0 to 10.0 "The following error occurred in sql server: xp_sendmail: failed with mail error 0x80040111" I looked in the master table for xp_sendmail under extended stored procedures and there was not one called xp_sendmail and added the correct role and permission. What i don't understand is what stored procedures calls that extended stored procedure You do not say whether you are using SQL Server 2000 or 2005. Regardless, the xp_sendmail system extended stored procedure should be present in the master database....

choice from a drop down list returns a result to a different cell
in the drop-down box when you make a selection such as "repair" "customer service" or "rental" I want it to return contact info that would be different for each possible choice but to display in the adjacent cell. Seems that that this should be easier than it is turning out to be. Any suggestions??? Use a lookup formula in the cell with contact information, so it looks up the appropriate value from a table based on the selection in the dropdown. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - ...

Holidays are hiding
HI I just noticed that no holidays are showing on my calendar -- how can I make them visible again. Yes, they are installed and I actually just tried reinstalling them. Thanks for any assistance, dmp ...

labels #7
I want to be able to print a page of labels with different address. It only prints the same label on the entire page. HELP! Hi Bettie (mccaffbj@mda.state.md.us), in the newsgroups you posted: || I want to be able to print a page of labels with different || address. It only prints the same label on the entire || page. HELP! Visit the training tutorials for business cards (similar to labels) on my site as well as have a peek at my label templates. http://www.mvps.org/publisher -- Brian Kvalheim Microsoft Publisher MVP http://www.mvps.org/publisher This posting is provided "AS IS&...

Date() Not Working in 2007
Anyone know why Date() does not work in 2007 Access and what has replaced it? Thanks! I don't have ac2007 but I suspect Date() works as it always has. Do you have any reference problems? http://www.accessmvp.com/djsteele/AccessReferenceErrors.html Does it throw an error? DSmith wrote: >Anyone know why Date() does not work in 2007 Access and what has replaced >it? Thanks! -- HTH - RuralGuy (RG for short) acXP WinXP Pro Please post back to this forum so all may benefit. Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200705/1 ...

Date and Timestamp in Filename
I have a spreadsheet that is modified several times a day and by several people. Is it possible to somehow insert the date and timestamp (perhaps with a macro?) into the filename? It is very difficult to get users to use abbreviations/version numbers etc to show when the last modified spreadsheet was used - many "forget". If there was a way that automatically put the date and time into the filename then users will know which is the most recent document to use - and not have any excuses! many thanks peeps! Emily, Pretty easy ActiveWorkbook.SaveAs Filename:=sFilename &...

Comments #7
Hi, in a workbook we have a booking-list for different rooms. In columns we have weeks and in rows we have classroom and day monday - sunday. In the cells we have comments saying subject and teacher. As the system is going out of hand we�d like to copy the information from a week (one column) and paste it into a new sheet, say column A, and we also want the comment to be printed in, say column B. I have tried to record a macro but it does not work. Does someone have a solution? Regards The following code will copy the values and comments from a specific column to a new sheet: '...

X axis collapsing to date on hourly data
Hello, I have a spreadsheet I am trying to chart that has values by date and time, IE Date-Time, Value 9/17/07 20:00, 1 9/17/07 21:00, 2 9/17/07 22:00, 4 9/17/07 23:00, 1 9/18/07 00:00, 2 9/18/07 01:00, 1 9/18/07 02:00, 0 When I highlight all of the above and select "Insert Chart" and choose line chart, it creates the chart as if it had the following information: Date-Time, Value 9/17/07, 8 9/18/07, 3 It also looks more like a bar chart then a line chart. Anyone have an idea on why this would happen and how to fix it? -Jason I am mistaken, it is collapsing into all 24 series...

Carriage Return in Excel
To insert a carriage return in Excel, I press ALT+ENTER. How do I do this when using "Find and Replace"? I want to replace a given character (",") with a carriage return across a large range of cells. > I want to replace a given character (",") with a carriage return across a > large range of cells. "Find What" = "," ; tab to the "Replace With" box, then type 0010 on the Number keypad while holding down ALT. Click "Replace All". Rgds, Andy Andy Thank you! That was very helpful. Rgds Rod "Andy Brown...

Return "cell reference"
Hi. How to do the following: 1) Return the cell reference of the cell eg: Type this formula in cell A1: =return_cell_reference_of_that_cell() {The above is a fictitious formula only} The answer will be A1. Preferably, it is great for me to decide on how the reference is displayed, eg: - absolute (ie $A$1) or - column-absolute (ie $A1) or - row-absolute (ie A$1) - relative (ie A1) 2) Return cell reference(s) of the target eg: Type this formula in cell B1: =return_cell_reference(target_cell) {The above is a fictitious formula only} eg: =return_cell_reference(A3) Answer: A3 =return_cell_refe...

How do I sort by month and day
I want to sort multiples dates by day and month, but NOT year. Basically to put all of our birthdays in order for the year......... Let's assume that all your dates are some column, say column B In another column, enter the formula =MONTH(B1) and copy down. In a third column, enter =DAY(B1) and copy down. Then sort the data by month and then by day. -- Gary's Student "CVSTANFORD" wrote: > I want to sort multiples dates by day and month, but NOT year. Basically to > put all of our birthdays in order for the year......... "CVSTANFORD" wrote: > I...

Project Return should allow for use of originating currency
Transactions | Project | Returns from Project Entry should allow for use of an originating currency in multicurrency environments, rather than assuming the functional currency for all transactions. ---------------- 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.c...

SBS2003 R2 with Windows 7 Pro
Not tried connecting Windows 7 Pro to SBS 2003 ... I've just been given 4 new Windows 7 Notebooks to setup for new employeed (One of the directors has just ordered 4 Dell Notebooks with Windows 7 while I was on Holiday) Is this a question of just running connect computer? On Tue, 31 Aug 2010 10:18:07 +0100, "JohnB" <JohnB@msforums.com> wrote: >Not tried connecting Windows 7 Pro to SBS 2003 ... > >I've just been given 4 new Windows 7 Notebooks to setup for new employeed >(One of the directors has just ordered 4 Dell Notebooks with Wind...

Application.StartupPath on Windows 7
Using Visual Studio 2008, C#.NET. How come referencing Application.StartupPath on Windows 7 translates to: c:\users\superuser\Documents\Visual Studio 2008\Projects\mySolution \mySolution\bin\Debug which does not exist, instead of: c:\users\superuser\My Documents\Visual Studio 2008\Projects\mySolution \mySolution\bin\Debug which DOES exist? On 1/5/2010 8:56 PM, Joe Cool wrote: > Using Visual Studio 2008, C#.NET. How come referencing > Application.StartupPath on Windows 7 translates to: > > c:\users\superuser\Documents\Visual Studio 2008\Projects\mySolution >...

Contacts' Modified Dates Change After Opening
Hello This problem popped up 3 days ago, I don't know what triggered it. After I double click on a contact, to open it, the modified date for this contact is updated to the present date and time, although I did not modify the contact. Right after I open it, its modified date is altered. I want to avoid this as I often use a "sort by modified date" view. Please help me solve this problem Thanks Gz If you open something in OL the modified date is changed, its default/std behaviour. "grendi" <ufoREMOVErobotTHISgrendizerusTO@CONTACTyahoo.MEcom> wrote in messag...

What function returns the Nth position in a sequence of numbers?
MS Excel 2002 Hi What is the function that tells you (without doing a manual sort) which position a cell's value is within a range of values? I've used it before but I cant find it in Excel's Help ! With thanks Ship Shiperton Henethe Check out the MATCH function in Excel's help menu... Hope this helps! Shiperton Henethe Wrote: > MS Excel 2002 > > > Hi > > What is the function that tells you (without doing a manual sort) > which position a cell's value is within a range of values? > > I've used it before but I cant find it in...

Email attachments #7
When I send emails with MS Word doc attachments, my recipients often receive attached files as .dat files. When attaching a .doc file, if the attached file icon does not come up in the separate 'attachment window' below the box for the body of the email text, I am guaranteed that it will not go through as a .doc file. There does not seem to be any pattern as to why this occurs. Any suggestions? Thanks Try using Plain Text format to send your attachment. To change the format of your email: Open TOOLS | OPTIONS... | MAIL FORMAT For more info: How to Prevent the Winmail.da...

Date in forms
I would like to have a command button that I could use to insert a date in a date field. the date would be for scheduling events at the beginning of each week, hence I would like the dates to be for the beginning of each week. First date could be 04-mar-07 the next record in the date column would be 11-mar-07. Each record would have the beginning of the week date. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200703/1 ...

i need to convert from UTC time to Local -7 hours offset
i need to convert data from UTC time to my local time witch is -7 hours from UTC (GMT) Hi simply subtract (add) the appropiate time to your cells. Just nore that 1 hour in Excel is equivalent to 1/24 -- Regards Frank Kabel Frankfurt, Germany "Kevin Morris" <Kevin Morris@discussions.microsoft.com> schrieb im Newsbeitrag news:52B7A37B-CC11-4313-A450-E010421789E9@microsoft.com... > i need to convert data from UTC time to my local time witch is -7 hours from > UTC (GMT) I tried that can you give me an example I need a formula that will subtract -7 hour for a time colum...

Allowing only Date and Integer for user form
I have a user form and as part of the procedure when I click OK is the following .... 'Make sure a number is entered If TextName1 = "" Then MsgBox "You must enter a number." Exit Sub End If 'Transfer the number If Range("M5") = True Then Range("O5") = TextName1 If TextName2 = "" Then MsgBox "You must enter a date." Exit Sub End If 'Transfer the date If Range("M5") = True Then Range("P5") = TextName2 1. I would like to make sure that only an integer is...