=IF(L8+1>TODAY(),"",L8+1) to skip weekends and holiday

XL2000
Is there some way to do this is a sheet code?

1. How can I get this formula =IF(L8+1>TODAY(),"",L8+1) to skip weekends
and holidays  in the column
2. How can I get this formula =IF(L8+1>TODAY(),"",L8+1) to  skip every
other cell in the column
3. When using contitional formating how would you copy an image like a
green or black star

0
7/8/2003 8:27:30 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
806 Views

Similar Articles

[PageSpeed] 2

Alan,

1. Use the Workday function, e.g.
=IF(WORKDAY(L8,1,holidays)>TODAY(),"",WORKDAY(L8,1,holidays))
where holidays is a named range that contains the holiday dates
2. don't understand.
3. CF can only set a format, font or cell, it cannot insert a picture

--

HTH

Bob Phillips

"~Alan" <mastergasfitter@bellsouth.net> wrote in message
news:3F0B2932.30F5659@bellsouth.net...
> XL2000
> Is there some way to do this is a sheet code?
>
> 1. How can I get this formula =IF(L8+1>TODAY(),"",L8+1) to skip weekends
> and holidays  in the column
> 2. How can I get this formula =IF(L8+1>TODAY(),"",L8+1) to  skip every
> other cell in the column
> 3. When using contitional formating how would you copy an image like a
> green or black star
>


0
bob.phillips (411)
7/8/2003 8:54:02 PM
2.don't understand.
 2.How can I get this formula =IF(L8+1>TODAY(),"",L8+1) to  skip every other
cell in the column
as in c5 c7 c9 c11 c13
and not c5 c6 c7 c8 c9 c10
can CF Copy from a cell as in =IF$Hh8>0copyGG22" or somthing like that

Bob Phillips wrote:

> Alan,
>
> 1. Use the Workday function, e.g.
> =IF(WORKDAY(L8,1,holidays)>TODAY(),"",WORKDAY(L8,1,holidays))
> where holidays is a named range that contains the holiday dates
> 2. don't understand.
> 3. CF can only set a format, font or cell, it cannot insert a picture
>
> --
>
> HTH
>
> Bob Phillips
>
> "~Alan" <mastergasfitter@bellsouth.net> wrote in message
> news:3F0B2932.30F5659@bellsouth.net...
> > XL2000
> > Is there some way to do this is a sheet code?
> >
> > 1. How can I get this formula =IF(L8+1>TODAY(),"",L8+1) to skip weekends
> > and holidays  in the column
> > 2. How can I get this formula =IF(L8+1>TODAY(),"",L8+1) to  skip every
> > other cell in the column
> > 3. When using contitional formating how would you copy an image like a
> > green or black star
> >

0
7/8/2003 9:47:57 PM
Also I have used named ranges before is there any peticalar format that should
be used?

~Alan wrote:

> 2.don't understand.
>  2.How can I get this formula =IF(L8+1>TODAY(),"",L8+1) to  skip every other
> cell in the column
> as in c5 c7 c9 c11 c13
> and not c5 c6 c7 c8 c9 c10
> can CF Copy from a cell as in =IF$Hh8>0copyGG22" or somthing like that
>
> Bob Phillips wrote:
>
> > Alan,
> >
> > 1. Use the Workday function, e.g.
> > =IF(WORKDAY(L8,1,holidays)>TODAY(),"",WORKDAY(L8,1,holidays))
> > where holidays is a named range that contains the holiday dates
> > 2. don't understand.
> > 3. CF can only set a format, font or cell, it cannot insert a picture
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > "~Alan" <mastergasfitter@bellsouth.net> wrote in message
> > news:3F0B2932.30F5659@bellsouth.net...
> > > XL2000
> > > Is there some way to do this is a sheet code?
> > >
> > > 1. How can I get this formula =IF(L8+1>TODAY(),"",L8+1) to skip weekends
> > > and holidays  in the column
> > > 2. How can I get this formula =IF(L8+1>TODAY(),"",L8+1) to  skip every
> > > other cell in the column
> > > 3. When using contitional formating how would you copy an image like a
> > > green or black star
> > >

0
7/8/2003 10:08:59 PM
I am getting an error #NAME?
I defined a name range holiday and inserted the dates any suggestions

=IF(WORKDAY(L8,1,holidays)>TODAY(),"",WORKDAY(L8,1,holidays))


Bob Phillips wrote:

> Alan,
>
> Bet I still haven't understood, but this is my interpretation. Put the
> formula in C5, leave C6 blank, select both cells, and drag down over the
> rest.
>
> CF can't do a copy either, it can only format font of cell.
>
> --
>
> HTH
>
> Bob Phillips
>
> "~Alan" <mastergasfitter@bellsouth.net> wrote in message
> news:3F0B3C0D.74865031@bellsouth.net...
> > 2.don't understand.
> >  2.How can I get this formula =IF(L8+1>TODAY(),"",L8+1) to  skip every
> other
> > cell in the column
> > as in c5 c7 c9 c11 c13
> > and not c5 c6 c7 c8 c9 c10
> > can CF Copy from a cell as in =IF$Hh8>0copyGG22" or somthing like that
> >
> > Bob Phillips wrote:
> >
> > > Alan,
> > >
> > > 1. Use the Workday function, e.g.
> > > =IF(WORKDAY(L8,1,holidays)>TODAY(),"",WORKDAY(L8,1,holidays))
> > > where holidays is a named range that contains the holiday dates
> > > 2. don't understand.
> > > 3. CF can only set a format, font or cell, it cannot insert a picture
> > >
> > > --
> > >
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "~Alan" <mastergasfitter@bellsouth.net> wrote in message
> > > news:3F0B2932.30F5659@bellsouth.net...
> > > > XL2000
> > > > Is there some way to do this is a sheet code?
> > > >
> > > > 1. How can I get this formula =IF(L8+1>TODAY(),"",L8+1) to skip
> weekends
> > > > and holidays  in the column
> > > > 2. How can I get this formula =IF(L8+1>TODAY(),"",L8+1) to  skip every
> > > > other cell in the column
> > > > 3. When using contitional formating how would you copy an image like a
> > > > green or black star
> > > >
> >

0
7/8/2003 10:54:59 PM
You say you named the range -- holiday
but the formula refers to  --  holidays

~Alan wrote:
> I am getting an error #NAME?
> I defined a name range holiday and inserted the dates any suggestions
> 
> =IF(WORKDAY(L8,1,holidays)>TODAY(),"",WORKDAY(L8,1,holidays))
> 
> 
> Bob Phillips wrote:
> 
> 
>>Alan,
>>
>>Bet I still haven't understood, but this is my interpretation. Put the
>>formula in C5, leave C6 blank, select both cells, and drag down over the
>>rest.
>>
>>CF can't do a copy either, it can only format font of cell.
>>
>>--
>>
>>HTH
>>
>>Bob Phillips
>>
>>"~Alan" <mastergasfitter@bellsouth.net> wrote in message
>>news:3F0B3C0D.74865031@bellsouth.net...
>>
>>>2.don't understand.
>>> 2.How can I get this formula =IF(L8+1>TODAY(),"",L8+1) to  skip every
>>
>>other
>>
>>>cell in the column
>>>as in c5 c7 c9 c11 c13
>>>and not c5 c6 c7 c8 c9 c10
>>>can CF Copy from a cell as in =IF$Hh8>0copyGG22" or somthing like that
>>>
>>>Bob Phillips wrote:
>>>
>>>
>>>>Alan,
>>>>
>>>>1. Use the Workday function, e.g.
>>>>=IF(WORKDAY(L8,1,holidays)>TODAY(),"",WORKDAY(L8,1,holidays))
>>>>where holidays is a named range that contains the holiday dates
>>>>2. don't understand.
>>>>3. CF can only set a format, font or cell, it cannot insert a picture
>>>>
>>>>--
>>>>
>>>>HTH
>>>>
>>>>Bob Phillips
>>>>
>>>>"~Alan" <mastergasfitter@bellsouth.net> wrote in message
>>>>news:3F0B2932.30F5659@bellsouth.net...
>>>>
>>>>>XL2000
>>>>>Is there some way to do this is a sheet code?
>>>>>
>>>>>1. How can I get this formula =IF(L8+1>TODAY(),"",L8+1) to skip
>>>>
>>weekends
>>
>>>>>and holidays  in the column
>>>>>2. How can I get this formula =IF(L8+1>TODAY(),"",L8+1) to  skip every
>>>>>other cell in the column
>>>>>3. When using contitional formating how would you copy an image like a
>>>>>green or black star
>>>>>
>>>>
> 


-- 
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

0
dsd (439)
7/8/2003 11:37:09 PM
On Tue, 08 Jul 2003 18:54:59 -0400, ~Alan <mastergasfitter@bellsouth.net>
wrote:

>I am getting an error #NAME?
>I defined a name range holiday and inserted the dates any suggestions
>
>=IF(WORKDAY(L8,1,holidays)>TODAY(),"",WORKDAY(L8,1,holidays))

If this function is not available, and returns the #NAME? error, install and
load the Analysis ToolPak add-in.

How?

On the Tools menu, click Add-Ins. 
In the Add-Ins available list, select the Analysis ToolPak box, and then click
OK. 
If necessary, follow the instructions in the setup program. 


Also, make sure your Holidays range is named the same in the formula (above you
have one named holiday, but in the function you wrote holidays.


--ron
0
ronrosenfeld (3122)
7/9/2003 2:57:23 AM
Reply:

Similar Artilces:

Service Pack 1 for server 2003, broke my Exchange
I have an exchange server here and I believe my problem to be more on the networking side. After updating to Service Pack 1 for server 2003 I notice that I am loosing connectivity on one of the subnets that the server has. Now I know it's the correct thing to do, but the server was configured with two IP Addresses from two different subnets; 10.0.0.0/24 and 192.168.1.0/24. This was done so that the office on the 10.0.0.0/24 network could talk to exchange and do their tasks. The 192.168.1.0/24 Address was added to allow email to be sent from the outside world to the exchange serve...

Event ID 3009 A non-delivery report with a status code of 5.1.0 appears randomly.
Exchange 2003 mail enabled Public Folder randomly don't receive e-mail from external sender, when it happens sender receives error : --------------------------------------------- Final-Recipient: rfc822;xxx@xxx.com Action: failed Status: 5.1.0 --------------------------------------------- Sometimes all work ok. Error in Application log : --------------------------------------------- Event Type: Error Event Source: MSExchangeTransport Event Category: (8) Event ID: 3009 Date: 2005.12.21 Time: 11:43:32 User: N/A Computer: MailServerName Description: The description for Event ID ( 3009 ...

Bug: (MFC 7.1) Paste into CRichEditView copied from other RichEdit control
Recently, I was helping someone figure out a problem in an MFC application recently upgraded to VC 7.1 (MFC 7.1) from VC 6.0 (MFC 6.0). While running on Windows XP, if you copied text from another CRichEditCtrl, CRichEditView, or another application using RichEdit like WordPad or Word, pasting what you've copied into a CRichEditView would paste nothing. When googling to try to find clues, most of the time there was either no response to the question, or the work around was to change the "copy" end that you controlled, and only put CF_TEXT on the clipboard. However, it ...

outlook inbox showing (-1) emails & 4294967295 unread messages
Hi Peops Have a very strange problem with one of my users on the network and have not found any thing relating to this problem anywhere on the web. Whats happening is i have a user running outlook xp and even though he has no new emails in his inbox he has a blue (-1) sign next to his inbox, and in the bottom left of the screen it says he has 4294967295 unread messages... But once a new email comes in then it goes back to normal, once the user reads the email the figure goes back to -1 again, never come across this before in 6 years, any ideas would be greatly appreciated. Cheers ...

Upgrade 1.0 to 1.2
This is a multi-part message in MIME format. ------=_NextPart_000_003C_01C3C0B8.E8BE58D0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi! Anyone know who to contact at Microsoft to upgrade my NFR copy of = 1.0 CRM to 1.2 CRM. I guess I need the upgrade to make it work with SBS = 2003. gary ------=_NextPart_000_003C_01C3C0B8.E8BE58D0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD&...

How can I print an 11x17 page on a single 8 1/2 x 11 page?
I am using an 11x17 format and printing my pages at a Kinko's on 11/17 paper. What I need is to be able to see what it looks like on a smaller sheet which my printer can handle. Hopefully, there's some method I can use to reduce the 11/17 image to 81/2x11. John W. <John W.@discussions.microsoft.com> was very recently heard to utter: > I am using an 11x17 format and printing my pages at a Kinko's on > 11/17 paper. What I need is to be able to see what it looks like on a > smaller sheet which my printer can handle. Hopefully, there's some > method I can...

=?iso-8859-1?Q?Emails_slow_to_open.?=
I'm running Outlook 2002 on a Windows XP workstation and connecting to an Exchange 2000 server in a local area network. For some reason it takes up to 10 seconds to open up a simple text message (a kb or two in size) from the Inbox, messages that other users opens instantly. There are no problems with other users and Exchange is up and running normally! My other network connections, to the file server and the DC are as fast as they should be. There is no error or warning messages in the error log either it is just very slow! Any ideas? Adde <anonymous@discussions.microsof...

put 4 postcards on an 8-1/2 x 11 in landscape
How do I put 4 postcards on an 8-1/2 x 11 in landscape mode? On the old Publisher, I found a setting in Avery that would do it. I'm so confused now. Are there Avery templates in 2007 Publisher and if so, how do I access Avery templates to accomplish this. It's probably really simple but I haven't been able to figre it out. -- Mary Virginia There is a template for Avery postcards, 4 to a sheet. When you are at "Getting Started" enter 3263 in the "search for templates" space. Hit enter. -- JoAnn Paules MVP Microsoft [Publisher] ~~~~~ How to ask a qu...

Random distribution of 1 and 0
Hi. I'd like to create an array consisting of nothing but the randomly distributed numbers 1 and 0. Well, no problem: ROUND(RAND();0) and fill the space with this. But here's the question: I want there to be i.e. 68% of 0 and 32% of 1. Is there a possibility to do this with Excel? At least the programm has to count the number of 0 and the one of 1 and correct randomly to the requested ratio of 1 to 0. Possible with Excel? I'd be glad if there is someone who can help me with this. Alex Alex - If you want the formula to return, e.g., 68% chance of 0 a...

=?iso-8859-1?Q?=EF=BB=BF_suddenly_appears_in_Signature?=
Hello, I have a problem with Outlook 2000. I had a signature working well for a while. I then created a new signature. After that, when I create a new message, the symbol appeared. I can remove it, but it is an annoyance. I tried removing the signatures and creating it again. I also tried removing office 20002 and installing it again to no avail. If anyone has ran into this problem and has any tips or solutions, please reply back. Thank you. ...

Graphing STRANGENESS
I am graphing two series of data using EXCEL using a line chart. The first series displays correctly and the second series is displayed as the SUM of the data for the first and second series. Has anyone ever seen this before? What causes this? Thanks, Barb Reinhardt Someone far wiser than I (thanks Jon) pointed out that I had a stacked line with markers. Oops! "Barb Reinhardt" wrote: > I am graphing two series of data using EXCEL using a line chart. The first > series displays correctly and the second series is displayed as the SUM of > the data for the fi...

how do I specify a date range = date(2010,4,1 thru 30)
I am trying to use a header in a formula. In A1 I have the month and year (April 2010). I want to bring in a calculation result from a second page (Daily Log) So I want to know how many guest in the month of April visited. But it has to look up April from the year list. I guess this is what I am trying to accomplish Lookup (A1) April (1-30) 2010 from April Stats sheet on Daily log sheet (A column) add guests logged in (D Column) and result to April Stats B5 I record numbers daily and want to have the totals from one page brought to another page, but need the data to be found...

Tasks in Outlook Today #2
I am trying to configure Outlook Today in Outlook 2003. The general layout as well as the display of appointments and messages are ok. But it seems that I can not configure the tasks the way I want them using the customize page: - Show tasks from today and older - Sort first according to age (new to old), then according to priority (high to low) - Optional: Do not mark (over)due tasks Any idea how to do this, even if I have to create a page manually? Best regards, Bernd very little customization is available for the outlook today page... so you'll need to make one yourself. It's n...

CRM 1.2 Licensing Question
We recently purchased CRM 1.2 Sales Module for Windows Small Business Server Premium (CRM English Lic/SA Pack OLP NL Sales Module* for WinSBSPrem, Quantity 1). We thought that we would be getting 5 licenses with this module. We needed 15 users so we purchased an additional 10 licenses (CRM English Lic/SA Pack OLP NL SalesStnd 1 User* for WinSBSPrem, Quantity 10). When I entered the license authorization number into the CRM 1.2 installation, it states that we have 10 users. Shouldn't we have 15 users? Did PC Connection sell us the wrong thing? I don't believe 5 users come with t...

Comparing data sets with +/-1 tolerance using conditional formatin
Please help! This has been puzzling me for hours!! I am using conditional formating to compare one data set with another to see if there are any discrepancies, using green cells for the same and red cells for different data. However, one set of data is rounded to the whole number, but the other set is to 2 decimal places (and has to remain that way). Therefore I need my conditional formating to compare the two data sets, but with a tolerance of plus or minus 1.00. How do I create this formula using conditional formating? CF/ Formula is: =ABS(A1-B1)<=1 -- David Biddulph ...

HELP! sum value to be displayed in sheet 2 when data is in sheet 1
How do I do this? any ideas -- Message posted from http://www.ExcelForum.com =SUM(Sheet1!A1:A100) -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "Muppet >" <<Muppet.18bcuz@excelforum-nospam.com> wrote in message news:Muppet.18bcuz@excelforum-nospam.com... > How do I do this? any ideas? > > > --- > Message posted from http://www.ExcelForum.com/ > =SUM(Sheet1!$A:$A) as an example for sum of Column A in Sheet1 -- Message posted from http://www.ExcelForum.com ...

Populate a cell if values in cell 1 and cell 2 match cell 3 and 4
I need help with a formula please. I need to search through a column of data and populate a cell if it meets a condition. For example: IF FirstWorksheet!A2 = SecondWorksheet!ColumnAx and FirstWorksheet!B2 = SecondWorksheet!ColumnBx Then populate SecondWorksheet!Cx with value from FirstWorksheet!C2 I have to match up a column of data in one worksheet with data in 2 columns in another worksheet. Help plz. Thanks T- A multi-criteria index n match (array-entered) should work here In Sheet2, Copy n paste this into C2's formula bar, then array-enter the formula by pressing CTRL+...

Numbers did not reset Jan. 1
I am using access 2003 in XPsp2 I have a log that assigns consecutive numbers preceded by a 2 digit year. (ex 10-001) On Jan. 1 the number was supossed to change and reset from 09-690 (last number of the year) to 10-001. This did not happen. The year changed but the number carried over showing 10-691. Here is the VB code that was used for this change I think. I did not write it and my VB knowledge is poor. Me.txtIncrNum.Enabled = True Me.txtIncrNum.SetFocus Me.txtIncrNum.Text = Nz(DMax("[IncrNum]", "[tblincidents]", _ "Year([datefi...

2 contacts folders with the same name, but I see just 1 of them, How to delete the 2nd?
Hi, In my outlook 2000, I managed to create a contact folder twice with the same name (I don't know how, but I did). Now both folders appear when I click on the "To" button in a new e-mail. One of them contains my contacts and the other is empty. I want to delete the empty one. The problem is that one one of those 2 folders appear under contacts and it's the one that contains my contacts (not the empty one). How can I delete the empty one? Thanks ThunderMusic Very odd. I'd love to know how you did that. You should post your mail support mode before we try anythin...

Upgrade- Remove- RMS 1.3 and SQL DESKTOP ENGINE
Hi- One last question before I upgrade- 1) Add and Remove- I need to Remove RMS 1.3 2) do I also need to Remove SQL DESKTOP ENGINE as well? Then Install RMS 2.0 Correct? Any Links on the install of RMS 2.0? Thanks Brent Brent You do not need to remove MSDE the software wizard will take you through the steps to upgrade the SQL instance. FYI it upgrades MSDE to SQL Server Express 2005. And you dont need to uninstall RMS either. Just copy the whole installation folder to some sort of backup media (External Hard disk, DVD-R, etc) Hope this helps Jere "Brent" wrote: >...

CRM 1.2 and Windows Server 2003 SP1
Hi, Has anyone tried Service Pack 1 for 2003 Server with CRM 1.2. Is is ok to apply? Or are there some issues that need to be addressed first? Thanks Tom I am looking for the same info >-----Original Message----- >Hi, > >Has anyone tried Service Pack 1 for 2003 Server with CRM 1.2. > >Is is ok to apply? Or are there some issues that need to be addressed first? > >Thanks > >Tom > > > >. > I have updated my server and now i get an error when opening CRM : HTTP 500 - Internal server error I'm investigating why Nico "Ken Foster&...

Outlook Today doesn't start with program
I have specified I want this to be the default folder opened upon startup (by checking "When starting, go directly to Outlook Today" in Customize Outlook Today), and also making sure Outlook Today is selected in Options/Other/Advanced/Startup, but the program nonetheless starts in Inbox every time. What am I doing wrong? Outlook 2002 (10.4712.4219) SP-2 Nevermind: http://groups-beta.google.com/group/microsoft.public.outlook/browse_thread/thread/a3abc531d16620ee?tvc=2&q=%22outlook+today%22+doesn%27t+default+start ...

Moving from 2 domain controllers to 1 with exchange 2003 in the m
Help!! I would like to demote one domain controller of two leaving me with only one domain controller. The problem is i have Exchange 2003 in the domain which is not necessarily a problem. I am concerned that by demoting the backup domain server that there will be a problem with connectivity with exchange. Does anyone have any suggestions or solutions? Please let me know. Note: the primary domain controller does the Global cataloging and config so this should be not problem right? Hi Rox, AD doesn't really have PDC and BDC like earlier versions of windows. You can certainly dem...

Instllation error MSCRM 1.2
Hi i am tring to install MSCRM1.2 on SBS 2000.its is giving this error: "Setup was unable to read the product language code from the Windows Installer package" i have screen shot of error , if anyone want to see i will attch later. please help me Bhaskar Thamma Where did you get v1.2? >-----Original Message----- >Hi i am tring to install MSCRM1.2 on SBS 2000.its is giving this error: >"Setup was unable to read the product language code from the Windows >Installer package" >i have screen shot of error , if anyone want to see i will attch later. >plea...

Moving SQL Server for CRM 1.2 10-30-07
Hi, We have a CRM 1.2 deployment with both CRM and SQL Server installed in the same machine. For better performance, we plan on moving the SQL server to a separate machine with better configurations. Is there any guideline published by Microsoft to do this? Check the Implementation Guide. You can basically follow the Disaster Recovery guidelines for restoring the CRM Databases to put them on a new server and then point CRM to those DBs. -- Matt Parks MVP - Dynamics CRM "appsmr" <appsmr@discussions.microsoft.com> wrote in message news:0B3D67F6-4423-4B77-BC31-643C84F...