Need to generate a date if cell is not bold...

Hello,

I need to generate a date in cell V2 that is 5 days prior to the date in 
cell S2 IF the date in S2 IS NOT bold. (I have conditional formats in S2 that 
will bold its date in certain circumstances).

Hope this isnt too confusing. Any help is appreciated.

Regards
Max
  
0
Utf
2/17/2010 9:53:01 PM
excel.misc 78881 articles. 5 followers. Follow

3 Replies
494 Views

Similar Articles

[PageSpeed] 40

This will require a macro. Formulas cannot check the format of a cell.

To do it with a formula, simply replicate the same conditions as your 
conditional formatting. So V2 would look like:
=if(yourconditions,v2-5,whatever you want when V2 is bold)

Regards,
Fred

"Max" <Max@discussions.microsoft.com> wrote in message 
news:A8485C20-EE1C-4647-965D-2D952D9959B1@microsoft.com...
> Hello,
>
> I need to generate a date in cell V2 that is 5 days prior to the date in
> cell S2 IF the date in S2 IS NOT bold. (I have conditional formats in S2 
> that
> will bold its date in certain circumstances).
>
> Hope this isnt too confusing. Any help is appreciated.
>
> Regards
> Max
> 

0
Fred
2/17/2010 10:13:29 PM
Fred,

I'm afraid I'm not following you. You note that this will require a macro, 
as formulas cannot check the format of a cell....then you describe how to do 
it with a formula? Seems like a contradiction. Sorry if I'm misunderstanding 
you. 

"Fred Smith" wrote:

> This will require a macro. Formulas cannot check the format of a cell.
> 
> To do it with a formula, simply replicate the same conditions as your 
> conditional formatting. So V2 would look like:
> =if(yourconditions,v2-5,whatever you want when V2 is bold)
> 
> Regards,
> Fred
> 
> "Max" <Max@discussions.microsoft.com> wrote in message 
> news:A8485C20-EE1C-4647-965D-2D952D9959B1@microsoft.com...
> > Hello,
> >
> > I need to generate a date in cell V2 that is 5 days prior to the date in
> > cell S2 IF the date in S2 IS NOT bold. (I have conditional formats in S2 
> > that
> > will bold its date in certain circumstances).
> >
> > Hope this isnt too confusing. Any help is appreciated.
> >
> > Regards
> > Max
> > 
> 
> .
> 
0
Utf
2/18/2010 2:34:04 PM
You asked for a formula to check to see if a cell was bold. This cannot be 
done with a formula.

However, there is a workaround. As you are bolding the cell based on a 
conditional format, then simply use that formula in the If statement.

For example, supposed your conditional formula is:
=s2>today()

Your If statement would look like:
=if(s2<=today(),s2-5,"whatever you want when S2 is not bold")

Regards,
Fred

"Max" <Max@discussions.microsoft.com> wrote in message 
news:004B5EDB-3CBD-458A-AD39-0404D57F03C0@microsoft.com...
> Fred,
>
> I'm afraid I'm not following you. You note that this will require a macro,
> as formulas cannot check the format of a cell....then you describe how to 
> do
> it with a formula? Seems like a contradiction. Sorry if I'm 
> misunderstanding
> you.
>
> "Fred Smith" wrote:
>
>> This will require a macro. Formulas cannot check the format of a cell.
>>
>> To do it with a formula, simply replicate the same conditions as your
>> conditional formatting. So V2 would look like:
>> =if(yourconditions,v2-5,whatever you want when V2 is bold)
>>
>> Regards,
>> Fred
>>
>> "Max" <Max@discussions.microsoft.com> wrote in message
>> news:A8485C20-EE1C-4647-965D-2D952D9959B1@microsoft.com...
>> > Hello,
>> >
>> > I need to generate a date in cell V2 that is 5 days prior to the date 
>> > in
>> > cell S2 IF the date in S2 IS NOT bold. (I have conditional formats in 
>> > S2
>> > that
>> > will bold its date in certain circumstances).
>> >
>> > Hope this isnt too confusing. Any help is appreciated.
>> >
>> > Regards
>> > Max
>> >
>>
>> .
>> 

0
Fred
2/19/2010 2:52:32 PM
Reply:

Similar Artilces:

Problem with dates after conversion
I converted a large Lotus 123 document into excel, now whatever date I enter returns a 1/00/00 date, in whatever format I choose..Jan 0 1900..1-0-00 etc. the correct date is displayed up on the F line but in my sheet is says Jan zero of the year 1900...It has successfully driven me nuts. Help please... Go to the Tools menu, choose Options then the Transition tab. Uncheck "Transition Formula Evaluation" and "Transition Formula Entry". When Entry is checked, Excel will treat 3/9/2005 not as a date but as division, and the result is less that zero, hence when formatte...

HELP: Need macro for excel to get data from server database and use results to populate an Access Database
Hi all, Is there a quick way to do a query on an SQL Server database and have the output put direct into an Access Database? I know how to do it by grabbing the records and then inserting each result one by one, but this takes forever to complete and I am after a quick solution, if it exists. Is anybody able to assist ? is it possible? Thanks in advance for any help! Regards Clint you could link to the SQL Server table(s) from Access, write a SELECT query to pull the records you want, and then turn the query into either an Append query to add the data to a native table in the Acces...

How to make a flexible cell reference?
Does anyone know how to make a reference to a cell flexible. I.e. I want a formula to get data from a cell, but want the cell to be dependant on some other value. E.g. =C"x+1" where "x+1" is a column number dependant on the value of x. I sure hope anyone can help me with this! Mark =INDIRECT("C"&x+1) -- HTH Bob Phillips "Mar Vernooy" <Mar Vernooy@discussions.microsoft.com> wrote in message news:40D873BD-1E6A-4412-BFCD-6A8B57B73E6F@microsoft.com... > Does anyone know how to make a reference to a cell flexible. I.e. I want a >...

need a nice price letter format
http://officeupdate.microsoft.com/templategallery/default.asp I would suugest you search for Word Templates. Excel is not a very good word processor. Gord Dibben MS Excel MVP On Tue, 27 Apr 2010 13:09:01 -0700, mdixon <mdixon@discussions.microsoft.com> wrote: ...

How do you write 2 lines of text in 1 cell rather than use 2 cells
I am using Excel 98 and want to know how to get 2 lines of text in 1 cell rather than space it out over 2 cells. See below for example if you dont get it..... This is what (cell no.1) I mean. (cell no. 2) I want to be (cell no.1) able to do this. (cell no.1) Please help.....thanks Type part, then Alt-Enter, then the rest. -- HTH Bob Phillips "!!!help!!!" <!!!help!!!@discussions.microsoft.com> wrote in message news:33CF64B2-E51F-4E8F-9680-1B495146C134@microsoft.com... > I am using Excel 98 and want to know how to get 2 ...

how do you add a new number to each cell in a column
for instance in column c from row 5 to row 455 I want to add .27 to every number that is already in each cell example c5 has 9.09 already in it, I want to add .27 for a new total of 9.36 I then want to copy that formula so that it does the same thing all the way to c455 Hi JCE, Select a Cell and Type =C5+0.27 where C stands for column and 5 stands for row drag the active cell downward to see the next row result then you may copy the formula to other worksheets or cells buy using copy and specialpaste. Hope this helps. "JCE" wrote: > for instance in column c from row 5 ...

Due Date in Purchasing
When entering Transactions in Purchasing we only have a Document Date field in the Transaction Entry windows. However, when looking at Smart Lists, Cash Requirements, there is s Due Date field. Is it possible to have both Document Date and Due Date as separate fields or are they always one and the same? -- Phil Gilbert Free Radical, LTD. Phil, On Purchasing transactions the Payment Terms are taken from the setup of the Vendor (Cards > Purchasing > Vendor > Options). You can change them in the following ways: 1. On a Purchase Order: click on the blue arrow button next to ...

How to get Cell contents in scrollbar mouseover, not cell#
Hello all - I have a large Xcel 2000 file with last names in column 'A'. I'd like to use the scroll bar and see the actual cell value rather than the cell# in the mouseover, as the cell# is not terrifically valuable information. Any hints or insights would help. Thanks and regards, Bob ...

Convert Text to Date 02-12-10
I have a text field with date like data in it but its text data. THe field [End_date] looks like 31Dec2009 - how can i reformat that so its a recognized date field that I can do calculations on? Ulitmately, I'd like the field to be 12/31/2009. Assistance is greatly appreciated. Not a VB expert so doing it in a query would work better for me. Thanks in advance!! Jeff -- Message posted via http://www.accessmonster.com On Fri, 12 Feb 2010 03:15:57 GMT, jfredel via AccessMonster.com wrote: > I have a text field with date like data in it but its text data. THe fiel...

Copy from merge cell
When I copy (Cell B2) from a merge cell for example A1 to S1 the result is ######### When I delete the range and copy from A1 it work, but when merge cell (A1 to S1) is blank "0" appear is B2. How to get rid of the "O" Try formatting that receiving cell (B2?) as General. Cells formatted as Text have problems when the length of the string is between 255 and 1024. MK wrote: > > When I copy (Cell B2) from a merge cell for example A1 to S1 the result is > ######### > When I delete the range and copy from A1 it work, but when merge cell (A1 to > S1) is blan...

Copy/Cut/Paste of Merged Cells in Shared Protected Workbooks
Hi I have a shared protected workbook into which comments and data are input on a daily basis to merged cells. In some cases the same information just has to be copied and pasted into a new position in the same worksheet. When the cell is copied and pasted the cell de-merges in the new position. Is there anyway of preventing this? The only alternative I can currently see is to copy or cut the text in a cell from the formula bar and then paste this into the new cell. Is this my only option? Any advice much appreciated Dave ...

how do I assign a cell equal to another cell
Hai , I facing a problem in Office 2003 , when I pres = then go to the cell that I want , it will come out a formula , anyway to let it come out as cell name (ie =D5 at D20 cell) Thanks Hi try tools / options / view and untick formulas the other option is that your cells have been formatted as text select a blank cell in a new worksheet / workbook and copy it select the misbehaving cells and choose edit / paste special - add cheres JulieD "yl" <yl@discussions.microsoft.com> wrote in message news:CDE3D991-0530-4B36-9BEF-DA1EF8AF8D11@microsoft.com... > Hai , I facing...

Count cells with data
I receive large databases each month. I have been able to format the data using PROPER. Though when I follow the instuctions from "3 formatting shortcuts" it calls for dragging the fill handle to the end of the new column to show all the converted cells. My databases are hundreds of names long! Is there a way to count the cells with text in them in advance - without scrolling down to the bottom - and then put the range into the =PROPER (range of cells with data) command? -- Thank you kindly You are using a second column to convert to Proper? You can Double-Click the Fill...

store some of the information from one cell into another
I would like to store some of the information from one cell into another. For instance, information in one cell has StampPDF V 2.3. I would like to cut 2.3 into a different cell. I was wondering if anyone new of the quickest way to do this. I have about 2000+ cells to work with. If the information you want from the cell is always at the end, and is always the last 3 characters you could use =RIGHT(A1,3) assuming that the original information is in cell reference A1 If you actually want to remove the 2.3 from the original cell then "bsantona" wrote: > I would like to st...

how can I get a cell to change color by date to indicate expiery t
I'm trying to get the cell color to change from green to yellow to red as a date entered in the cell gets closer to that date plus 12 months or 36 months. ie some task was completed on 1 Jan 08 and will last for 24 months so I want the cell to read green if the date is still good turn yellow a month or 2 before it expires and turn red and clear the date if expired. So if the cell is empty it should show red. But I want to be able to update the date the task was completed ie it was completed again on the 15 Dec 10 before it expired so I want the countdown to expiration to restart f...

Can we use the datediff function and put the answer in a cell?
what we want to do is return the difference of this function into a cell in a table, can we do it? On Sat, 12 Dec 2009 11:34:01 -0800, timmone <timmone@discussions.microsoft.com> wrote: Yes, but you shouldn't. Because that would violate an important relational database design principle that says "no calculated fields in the database". Rather you would calculate the value on the fly in a query: select DateDiff("d", myStartDate, myEndDate) as DaysBetween from myTable -Tom. Microsoft Access MVP >what we want to do is return the difference o...

how can i make paragraphs in side one cell in Excell?
Hi press ALT & ENTER when you want a new line Cheers JulieD "ashraf" <ashraf@discussions.microsoft.com> wrote in message news:E508A2C8-CC79-4107-93EE-5D607A53E8BA@microsoft.com... > or, format cell / alignment / wrap text ...

Cell Formatting #12
Entering a bank sort code into a cell i.e 16-00-04, however it keeps changing to a date range. I have tried converting the field to text and then change the format, doesn't work. I have also tried copying the format from the cell above and then overtyping it, this doesn't work. The only way I could get round it was to start the string with a '. Thanks for the help. Hi try formating the cell PRIOR to entering data in it as 'Text' >-----Original Message----- >Entering a bank sort code into a cell i.e 16-00-04, >however it keeps changing to a date range....

How to add a space after each text value in all the cells in a row
I want to add a space (or any character(s)) after each text value in all the cells in a row or in a column For example, cell A2 contains "ABC", cell B2 contains "DEF", and so on. I want to make all the cells in row 2 to have a space so cell A2 contains "ABC ", cell B2 contains "DEF ",... try going to an empty part of the sheet (say row a4) & type =a2&" " or "[any character]" then copy that across the row for your range. after calc you can copy the range & paste special (values) back into range a2 ...X2 &am...

Money needs update
I started Money 2007 this evening to get some expenses caught up and a dialog box popped up saying that in order for Money to continue working properly, an update was required. I clicked OK and it looked like a download started followed by an install that went only part way. Another dialog popped up saying that the installation could not close Money (even though Money had disappeared from view) and I needed to restart Money to complete the installation. I did this and a dialog popped up saying that in order for Money to keep working properly, an updated was required. I clicked OK. ...

Need to get a file please
I need to update Office 2002, and the installation stops, asking for the file 'pubret.msi'. I don't have the Publisher CD anymore, can anyone please send me the file, or direct me to a link. Thanks. -- Magnus Jungbeck Best Computer Hi Magnus Jungbeck (MagnusJungbeck@discussions.microsoft.com), in the newsgroups you posted: || I need to update Office 2002, and the installation stops, asking for || the file 'pubret.msi'. I don't have the Publisher CD anymore, can || anyone please send me the file, or direct me to a link. Thanks. You cannot update without the CD. In ...

I need help with a list box
I have a table called "tblSheets" that contains sheet numbers as follows: A-001 A-002 B-001 For each sheet there can be comments made in a table called "tblComments". I have a form which has a list box that shows all sheets from tblSheets regardless to whether they have comments existing in the tblComments or not. I need to show the user which items on the list box that have comments entered in the tblComments without having the user to click on each item in the list box to find out if there were comments entered or not. Is there a way to bold or color items in the li...

OWA Generate CSR warning
Hi all, Tried to generate a CSR on OWA so we can get an SSL certificate but it already has a certificate issued by itself - is it safe to renew this to create a CSR or might this cause problems with OWA? TIA I had this problem as well. If you already have a self-generated certificate trying to create a CSR to get a new one will wipe out the old one. Additionally, as it takes a little while for the company issuing your certificate to verify your identity before issuing the certificate then your OWA web server will be in an intermediate state, waiting for the second part of the certific...

Date format 06-02-06
Hi; Is it possible to manipulate the date format? Actually, I want to be able to put july, 15 2006 instead of 07/15/2006. Can anyone help me? Thax! Mehdi Hi MA, Where do you want to do this? In the UI? If so, you can add some script to format the date. You'll need some help from a script like this one at CodeProject: http://www.codeproject.com/jscript/dateformat.asp HTH -- Michael Sanford SynchedUp Solutions http://www.synchedup.com "MA" <mamor@NOPSPAM.com> wrote in message news:871f874a343947d29d6611d45ae77cc9@ureader.com... > Hi; > > Is it possible...

I would like to extend hyperlinks across cells
I want to extend the hyperlinks across all cells in one worksheet..... like cell A1 in sheet 1 should point to cell A1 in sheet2 and so on..... i have 500 cells in both sheets.... Select Cell>right click>hyperlink>in this file>sheet>cell ref. Hope this is what you mean. Pat F "venkat" wrote: > I want to extend the hyperlinks across all cells in one worksheet..... like > cell A1 in sheet 1 should point to cell A1 in sheet2 and so on..... i have > 500 cells in both sheets.... Ya this will enable hyperlink for one cell... i would like to drag this acro...