highlighting cells which have dates later than...

I would like to highlight date cells in a different color which occur for 
example after 1/1/2000.

The dates will be all on a colum, some before 1/1/2000 and some after.

In addition to just highlighting the cell itself, is there a way to 
highlight adjacent cells.

Any help or pointers would be greatly appreciated.

Regards, Rory
0
2/2/2005 2:35:07 PM
excel.misc 78881 articles. 5 followers. Follow

6 Replies
562 Views

Similar Articles

[PageSpeed] 47

Use format>conditional formatting (after selecting the cell(s)), formula is 
and 

=A1>DATE(2000,1,1)

click the format button the change the format
to include adjacent cells refer to the same formula but use absolute
references =$A$1>DATE(2000,1,1)

where in this case A1 would be the cell you want to highlight

Regards,

Peo Sjoblom


"Rory Carroll" wrote:

> I would like to highlight date cells in a different color which occur for 
> example after 1/1/2000.
> 
> The dates will be all on a colum, some before 1/1/2000 and some after.
> 
> In addition to just highlighting the cell itself, is there a way to 
> highlight adjacent cells.
> 
> Any help or pointers would be greatly appreciated.
> 
> Regards, Rory
0
PeoSjoblom (789)
2/2/2005 2:57:05 PM
No joy,

This is what I did.

-Highlighted the column (don't think this is necessary)
-clicked fotmat, conditional format
-in condition 1 I selected "formula is"
-Used the cell selection too to select which cells to use (in my case : 
"=$D$3:$D$17")
-clicked the same button again (to signal my selection end)
-put in ">DATE(2004,7,1)" to give me the line "=$D$3:$D$17>DATE(2004,7,1)"
-clicked format and gave it a color

The cells containg information like this:
Sep  1 2004  4:57AM
Sep  2 2004  3:59AM
Jun 28 2004  8:16AM
....etc

So I expected the sep cells to be highlighted.

Thanks for your input

Rory



"Peo Sjoblom" wrote:

> Use format>conditional formatting (after selecting the cell(s)), formula is 
> and 
> 
> =A1>DATE(2000,1,1)
> 
> click the format button the change the format
> to include adjacent cells refer to the same formula but use absolute
> references =$A$1>DATE(2000,1,1)
> 
> where in this case A1 would be the cell you want to highlight
> 
> Regards,
> 
> Peo Sjoblom
> 
> 
> "Rory Carroll" wrote:
> 
> > I would like to highlight date cells in a different color which occur for 
> > example after 1/1/2000.
> > 
> > The dates will be all on a colum, some before 1/1/2000 and some after.
> > 
> > In addition to just highlighting the cell itself, is there a way to 
> > highlight adjacent cells.
> > 
> > Any help or pointers would be greatly appreciated.
> > 
> > Regards, Rory
0
2/2/2005 4:51:04 PM
Highlight D3:D17 and with the activecell in D3
use Peo's formula (slightly modified)

=D3>DATE(2000,1,1)

Excel will adjust the formula for each cell in the selection.

Rory Carroll wrote:
> 
> No joy,
> 
> This is what I did.
> 
> -Highlighted the column (don't think this is necessary)
> -clicked fotmat, conditional format
> -in condition 1 I selected "formula is"
> -Used the cell selection too to select which cells to use (in my case :
> "=$D$3:$D$17")
> -clicked the same button again (to signal my selection end)
> -put in ">DATE(2004,7,1)" to give me the line "=$D$3:$D$17>DATE(2004,7,1)"
> -clicked format and gave it a color
> 
> The cells containg information like this:
> Sep  1 2004  4:57AM
> Sep  2 2004  3:59AM
> Jun 28 2004  8:16AM
> ...etc
> 
> So I expected the sep cells to be highlighted.
> 
> Thanks for your input
> 
> Rory
> 
> "Peo Sjoblom" wrote:
> 
> > Use format>conditional formatting (after selecting the cell(s)), formula is
> > and
> >
> > =A1>DATE(2000,1,1)
> >
> > click the format button the change the format
> > to include adjacent cells refer to the same formula but use absolute
> > references =$A$1>DATE(2000,1,1)
> >
> > where in this case A1 would be the cell you want to highlight
> >
> > Regards,
> >
> > Peo Sjoblom
> >
> >
> > "Rory Carroll" wrote:
> >
> > > I would like to highlight date cells in a different color which occur for
> > > example after 1/1/2000.
> > >
> > > The dates will be all on a colum, some before 1/1/2000 and some after.
> > >
> > > In addition to just highlighting the cell itself, is there a way to
> > > highlight adjacent cells.
> > >
> > > Any help or pointers would be greatly appreciated.
> > >
> > > Regards, Rory

-- 

Dave Peterson
0
ec357201 (5290)
2/2/2005 11:07:21 PM
No Joy.

Could it be that it doesn't understand the cell as "Sep  1 2004  4:57AM" 
(without the quotes)?

what I did:
-selected cells D3 down as far as D17 (D3 is shown in the menu bar 
(activecell I guess))
-went to format, conditional formatting
-put in the formula =D3>DATE(2004,7,1)
-changed the format to bold
-clicked ok
-expected the cell Jun 28 2004  8:16AM to not be in bold, but all the cells 
were in bold

here is the colum D from row 3 to 17

Sep  3 2004 12:15AM
Sep  3 2004  3:55AM
Sep  1 2004  4:57AM
Sep  2 2004  3:59AM
Sep  3 2004  8:45AM
Sep  1 2004  1:21AM
Jun 28 2004  8:16AM
Sep  3 2004  6:42AM
Sep  5 2004  1:14PM
Aug 13 2004  5:29AM
Sep  2 2004  8:48AM
Sep  3 2004  5:58AM
Aug  3 2004 12:49AM
Sep  3 2004  6:02AM
Sep  3 2004 12:22PM

Thanks,

Rory

"Dave Peterson" wrote:

> Highlight D3:D17 and with the activecell in D3
> use Peo's formula (slightly modified)
> 
> =D3>DATE(2000,1,1)
> 
> Excel will adjust the formula for each cell in the selection.
> 
> Rory Carroll wrote:
> > 
> > No joy,
> > 
> > This is what I did.
> > 
> > -Highlighted the column (don't think this is necessary)
> > -clicked fotmat, conditional format
> > -in condition 1 I selected "formula is"
> > -Used the cell selection too to select which cells to use (in my case :
> > "=$D$3:$D$17")
> > -clicked the same button again (to signal my selection end)
> > -put in ">DATE(2004,7,1)" to give me the line "=$D$3:$D$17>DATE(2004,7,1)"
> > -clicked format and gave it a color
> > 
> > The cells containg information like this:
> > Sep  1 2004  4:57AM
> > Sep  2 2004  3:59AM
> > Jun 28 2004  8:16AM
> > ...etc
> > 
> > So I expected the sep cells to be highlighted.
> > 
> > Thanks for your input
> > 
> > Rory
> > 
> > "Peo Sjoblom" wrote:
> > 
> > > Use format>conditional formatting (after selecting the cell(s)), formula is
> > > and
> > >
> > > =A1>DATE(2000,1,1)
> > >
> > > click the format button the change the format
> > > to include adjacent cells refer to the same formula but use absolute
> > > references =$A$1>DATE(2000,1,1)
> > >
> > > where in this case A1 would be the cell you want to highlight
> > >
> > > Regards,
> > >
> > > Peo Sjoblom
> > >
> > >
> > > "Rory Carroll" wrote:
> > >
> > > > I would like to highlight date cells in a different color which occur for
> > > > example after 1/1/2000.
> > > >
> > > > The dates will be all on a colum, some before 1/1/2000 and some after.
> > > >
> > > > In addition to just highlighting the cell itself, is there a way to
> > > > highlight adjacent cells.
> > > >
> > > > Any help or pointers would be greatly appreciated.
> > > >
> > > > Regards, Rory
> 
> -- 
> 
> Dave Peterson
> 
0
2/3/2005 9:13:05 AM
Ok, I'm almost sure that the data there is not in date format, that it has 
been pulled from data (ie sept is sep, then there is the two spaces after it. 
I'm posting a new post in programming)

I'll now need to change sep  1 2004  6:42AM
to
1/9/2004

I'll need a macro or something?

regards, Rory


"Rory Carroll" wrote:

> No Joy.
> 
> Could it be that it doesn't understand the cell as "Sep  1 2004  4:57AM" 
> (without the quotes)?
> 
> what I did:
> -selected cells D3 down as far as D17 (D3 is shown in the menu bar 
> (activecell I guess))
> -went to format, conditional formatting
> -put in the formula =D3>DATE(2004,7,1)
> -changed the format to bold
> -clicked ok
> -expected the cell Jun 28 2004  8:16AM to not be in bold, but all the cells 
> were in bold
> 
> here is the colum D from row 3 to 17
> 
> Sep  3 2004 12:15AM
> Sep  3 2004  3:55AM
> Sep  1 2004  4:57AM
> Sep  2 2004  3:59AM
> Sep  3 2004  8:45AM
> Sep  1 2004  1:21AM
> Jun 28 2004  8:16AM
> Sep  3 2004  6:42AM
> Sep  5 2004  1:14PM
> Aug 13 2004  5:29AM
> Sep  2 2004  8:48AM
> Sep  3 2004  5:58AM
> Aug  3 2004 12:49AM
> Sep  3 2004  6:02AM
> Sep  3 2004 12:22PM
> 
> Thanks,
> 
> Rory
> 
> "Dave Peterson" wrote:
> 
> > Highlight D3:D17 and with the activecell in D3
> > use Peo's formula (slightly modified)
> > 
> > =D3>DATE(2000,1,1)
> > 
> > Excel will adjust the formula for each cell in the selection.
> > 
> > Rory Carroll wrote:
> > > 
> > > No joy,
> > > 
> > > This is what I did.
> > > 
> > > -Highlighted the column (don't think this is necessary)
> > > -clicked fotmat, conditional format
> > > -in condition 1 I selected "formula is"
> > > -Used the cell selection too to select which cells to use (in my case :
> > > "=$D$3:$D$17")
> > > -clicked the same button again (to signal my selection end)
> > > -put in ">DATE(2004,7,1)" to give me the line "=$D$3:$D$17>DATE(2004,7,1)"
> > > -clicked format and gave it a color
> > > 
> > > The cells containg information like this:
> > > Sep  1 2004  4:57AM
> > > Sep  2 2004  3:59AM
> > > Jun 28 2004  8:16AM
> > > ...etc
> > > 
> > > So I expected the sep cells to be highlighted.
> > > 
> > > Thanks for your input
> > > 
> > > Rory
> > > 
> > > "Peo Sjoblom" wrote:
> > > 
> > > > Use format>conditional formatting (after selecting the cell(s)), formula is
> > > > and
> > > >
> > > > =A1>DATE(2000,1,1)
> > > >
> > > > click the format button the change the format
> > > > to include adjacent cells refer to the same formula but use absolute
> > > > references =$A$1>DATE(2000,1,1)
> > > >
> > > > where in this case A1 would be the cell you want to highlight
> > > >
> > > > Regards,
> > > >
> > > > Peo Sjoblom
> > > >
> > > >
> > > > "Rory Carroll" wrote:
> > > >
> > > > > I would like to highlight date cells in a different color which occur for
> > > > > example after 1/1/2000.
> > > > >
> > > > > The dates will be all on a colum, some before 1/1/2000 and some after.
> > > > >
> > > > > In addition to just highlighting the cell itself, is there a way to
> > > > > highlight adjacent cells.
> > > > >
> > > > > Any help or pointers would be greatly appreciated.
> > > > >
> > > > > Regards, Rory
> > 
> > -- 
> > 
> > Dave Peterson
> > 
0
2/3/2005 10:27:03 AM
Maybe.

But I'd try this first.

Are all those "dates" in one column?
And do you need to keep the time portion?

If yes, then insert a new column to the right of that date column.
select the date column
data|text to columns
fixedwidth and erase any lines that excel guessed
But put a line in front of the time

Tell the wizard that the first portion is a date (mdy) and the second portion is
general.

Plop the results in the original column and the new column to the right.

But the times aren't times yet.

select that times column
edit|replace
what:  AM  (just AM--nothing else)
with: _AM  (underscore represents a spacebar)
replace all

do the same with PM to _PM (SpaceBar PM)

And format your dates & times the way you want.

If you don't need the time, then tell the wizard to skip (do not import) that
field.




Rory Carroll wrote:
> 
> Ok, I'm almost sure that the data there is not in date format, that it has
> been pulled from data (ie sept is sep, then there is the two spaces after it.
> I'm posting a new post in programming)
> 
> I'll now need to change sep  1 2004  6:42AM
> to
> 1/9/2004
> 
> I'll need a macro or something?
> 
> regards, Rory
> 
> "Rory Carroll" wrote:
> 
> > No Joy.
> >
> > Could it be that it doesn't understand the cell as "Sep  1 2004  4:57AM"
> > (without the quotes)?
> >
> > what I did:
> > -selected cells D3 down as far as D17 (D3 is shown in the menu bar
> > (activecell I guess))
> > -went to format, conditional formatting
> > -put in the formula =D3>DATE(2004,7,1)
> > -changed the format to bold
> > -clicked ok
> > -expected the cell Jun 28 2004  8:16AM to not be in bold, but all the cells
> > were in bold
> >
> > here is the colum D from row 3 to 17
> >
> > Sep  3 2004 12:15AM
> > Sep  3 2004  3:55AM
> > Sep  1 2004  4:57AM
> > Sep  2 2004  3:59AM
> > Sep  3 2004  8:45AM
> > Sep  1 2004  1:21AM
> > Jun 28 2004  8:16AM
> > Sep  3 2004  6:42AM
> > Sep  5 2004  1:14PM
> > Aug 13 2004  5:29AM
> > Sep  2 2004  8:48AM
> > Sep  3 2004  5:58AM
> > Aug  3 2004 12:49AM
> > Sep  3 2004  6:02AM
> > Sep  3 2004 12:22PM
> >
> > Thanks,
> >
> > Rory
> >
> > "Dave Peterson" wrote:
> >
> > > Highlight D3:D17 and with the activecell in D3
> > > use Peo's formula (slightly modified)
> > >
> > > =D3>DATE(2000,1,1)
> > >
> > > Excel will adjust the formula for each cell in the selection.
> > >
> > > Rory Carroll wrote:
> > > >
> > > > No joy,
> > > >
> > > > This is what I did.
> > > >
> > > > -Highlighted the column (don't think this is necessary)
> > > > -clicked fotmat, conditional format
> > > > -in condition 1 I selected "formula is"
> > > > -Used the cell selection too to select which cells to use (in my case :
> > > > "=$D$3:$D$17")
> > > > -clicked the same button again (to signal my selection end)
> > > > -put in ">DATE(2004,7,1)" to give me the line "=$D$3:$D$17>DATE(2004,7,1)"
> > > > -clicked format and gave it a color
> > > >
> > > > The cells containg information like this:
> > > > Sep  1 2004  4:57AM
> > > > Sep  2 2004  3:59AM
> > > > Jun 28 2004  8:16AM
> > > > ...etc
> > > >
> > > > So I expected the sep cells to be highlighted.
> > > >
> > > > Thanks for your input
> > > >
> > > > Rory
> > > >
> > > > "Peo Sjoblom" wrote:
> > > >
> > > > > Use format>conditional formatting (after selecting the cell(s)), formula is
> > > > > and
> > > > >
> > > > > =A1>DATE(2000,1,1)
> > > > >
> > > > > click the format button the change the format
> > > > > to include adjacent cells refer to the same formula but use absolute
> > > > > references =$A$1>DATE(2000,1,1)
> > > > >
> > > > > where in this case A1 would be the cell you want to highlight
> > > > >
> > > > > Regards,
> > > > >
> > > > > Peo Sjoblom
> > > > >
> > > > >
> > > > > "Rory Carroll" wrote:
> > > > >
> > > > > > I would like to highlight date cells in a different color which occur for
> > > > > > example after 1/1/2000.
> > > > > >
> > > > > > The dates will be all on a colum, some before 1/1/2000 and some after.
> > > > > >
> > > > > > In addition to just highlighting the cell itself, is there a way to
> > > > > > highlight adjacent cells.
> > > > > >
> > > > > > Any help or pointers would be greatly appreciated.
> > > > > >
> > > > > > Regards, Rory
> > >
> > > --
> > >
> > > Dave Peterson
> > >

-- 

Dave Peterson
0
ec357201 (5290)
2/3/2005 11:39:43 PM
Reply:

Similar Artilces:

unwanted csv date conversion
I have a CSV file with the following information: "Co","Profile","Profile Name" "003","MAR4","MARK ROGERS - District or Corp Accountant" "004","MAR4","MARK ROGERS - District or Corp Accountant" Excel is automatically converting my TEXT MAR4 field to 4-Mar (3/4/2005). The only way I can find to avoid the incorrect conversion, is to change the extension to *.txt, then Import it, and on that column, change it from the default type of "General" to "Text". Is there some setting somewh...

including date and time in one function
I am trying to make a worksheet that will return a value during certain time period. For example: On Monday at 8:00-9:00, I would lik it to display a different value than the same time on Tuesday. Also, need it to show a different value later on in the same day. I canno figure out if there is a way to combine DATEVALUE and TIMEVALUE, nor a i sure if a combination of the two is what is exactly what I want, bu it is somewhere along those lines. I would appreciate any help tha anyone can give me -- Message posted from http://www.ExcelForum.com Hi you can add them. e.g. try =IF(AND(A1>=DAT...

Highlighted email changes when I sort by subject
When I sort by subject (show in groups is on) the highlighted email does not stay highlighted, instead i get moved to the very top of the email list. This is very annoying because now I have to search for the subject that I was originally looking at. I have found the suggested workaround to sort by Conversation but this does not have the same affect on the sort. I believe that this problem is related to the number of emails in the folder. I have not found the exact number at which this becomes a problem but it is somewhere between 200-400. I tired the sort on a folder with 202 items and i...

SUM of certain cells after Autofilter
Hallo, The following worksheet (as an excerpt of a bigger one) is to be analysed: x x 2 x 1 x 2 2 After Autofilter regarding x in any column the sum of VISIBLE cells with the value 2 should be calculated (e.g. 2 for the first column and 4 for the second one). The function SUBTOTAL seems not to give a proper result considering conditions). Any idea? Regards Bernd Hi Bernd Where are you putting the SUBTOTAL formula? Is it within the range of data and therefore possibly getting confused? Try inserting a row above your first row and putting the formula there. =SUBTOTAL(9,C2:C1000) a...

today's date
which formula should i use to insert today's date which update daily automatically. =TODAY() "aditya" wrote: > which formula should i use to insert today's date which update daily > automatically. "aditya" wrote: > which formula should i use to insert today's date which update daily > automatically. =NOW() In the cell were you want the date, but don't forget to format it, so it shows up as a date, not a number. Hi, =Today() "aditya" wrote: > which formula should i use to insert today's date which update daily &g...

Transaction Date VS Business Date
Our accounting department needs/wants to have all RMS SO reporting by business date opposed to transaction date. Usually these are the same unless you have sales and batches that close after midnight. For our store I would like to set 3 a.m. as the business day closing time. So when I run any type of reports I would like to get the information for a business date (range) starting at 3 a.m. till 3 a.m. the next day. I guess I could accomplish this by subtracting 3 hours from all the transaction (date and) times but I would have to do this for every single report which would be a lot of ...

avg formula and blank cells
What is the proper way when using the AVG function with cells formatted as numbers, to fill a blank cell so it won't make the avg lower, but rather be ignored in the AVG function? Thanks! --Randy Starkey __________ Information from ESET NOD32 Antivirus, version of virus signature database 4080 (20090515) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com Excel ignores blanks cells when averaging. If you mean ignore cells uncertain a certain value. In Excel 2007, use AverageIf. In earlier versions you need to construct the formula using SumIf ...

Posting date on worksheet
I have a number of worksheets in a workbook where I save different scenarios. I would like to place on the worksheet the last date that it was modified. I can tell when I last worked on the workbook from the file description but I'd like to know the last time I modified a worksheet. Any ideas? Thanks. Michael Have a look at http://www.mcgimpsey.com/excel/timestamp.html Charlie O'Neill >-----Original Message----- >I have a number of worksheets in a workbook where I save >different scenarios. I would like to place on the >worksheet the last date that it was modified...

Counting dates within groups
Hi Is it at all possible to calculate or count groups of dates in a lis when they have a gap of 2 days between them? It's kind of hard to explain The attachment has the data I'm trying to work from including description of the result. Cheers Joe Attachment filename: timesheet test.xls Download attachment: http://www.excelforum.com/attachment.php?postid=56391 -- Message posted from http://www.ExcelForum.com ...

Add System Date to Chart
Hi All, Is there any way of display the system date, either within the title of the chart, or anywhere else? Cheers. Bill. Hi, You could link the chart title to a cell which contains the system data. Once you have added the chart title to link it to a cell enter the full cell address in the formula bar. =Sheet1!$A$1 http://www.andypope.info/tips/tip001.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Bill" <bill@bill.com> wrote in message news:%23onkuRxFIHA.4584@TK2MSFTNGP03.phx.gbl... > Hi All, > > Is there any way of displa...

Date conversion, PLEASE HELP
I am importing data from an OBDC datasource. One of the fields is a date field that imports in this format "20040818", as a number. I am trying to convert this value to a date format and have had no luck. If anyone can provide assistance, I am grateful... MyDate = DateSerial(Left([ImportedDate], 4), Mid([ImportedDate], 5, 2), Right([ImportedDate], 2)) -- Ken Snell <MS ACCESS MVP> "shawn" <shawn.abbott@mrgasket.com> wrote in message news:8ac901c4854e$3aa483e0$a601280a@phx.gbl... > I am importing data from an OBDC datasource. One of the &g...

workflow manager not opening
I get the following error when trying to open any of the workflow tools ----------------------------------------------------------------------------------------------- An error occurred while loading the organisation's date, time, and number format settings. Those settings are incompatible with the formats already used in Workflow Manager. To sync them, you can make changes to System Settings in the System Customisation area of Microsoft CRM Server. ----------------------------------------------------------------------------------------------- No-one has changed this settings, but our...

date difference in mm/yyyy format
I have two date colums StartDate ReadDate I want to find all the readdates that are in months prior to the startdate Eg if(readdate<startdate,"yes","no") the problem is that if the readdate is in the same month (or any later date) as the startdate I want a "no' returned. Is there any way to compare to dates in a mm/yyyy format? Hi Ian =if(Month(readdate)<month(startdate),"yes","no") -- Regards Roger Govier Ian wrote: > I have two date colums > StartDate > ReadDate > > I want to find all t...

Wrong Due Date on APAYs
My APAYs show the wrong due date on the Bills Summary page. For example, a monthly mortgage payment shows the next due date being Mar 2007. A monthly car payment, with only five payments left, shows the next due date being in 2011! The next payment on the Bill Calendar is also missing -- sometimes one or more -- not necessarily related to the missing payment dates from looking at the Bill Summary. This was a problem in Money 2003 before I upgr^H^H^Hchanged to Money 2007. After I installed M2007, I did the File Repair and also the Remove All Bills Data options. This seemed to correct the p...

only average cells w/numbers?
I have a column (L) that my data begins in Row 9 and ends in Row 55. Need to have average of the numbers in those cells (L9-L55). However, some cells contain #VALUE, because information for the row ha not been entered. In L56, I'd like to have the average of only those cells L9-L55 tha contain a number. Thank you. MAG -- Message posted from http://www.ExcelForum.com Hi one way: =SUMPRODUCT(--(ISNUMBER(L9:L55)),L9:L55)/SUMPRODUCT(--(ISNUMBER(L9:L55) )) -- Regards Frank Kabel Frankfurt, Germany > I have a column (L) that my data begins in Row 9 and ends in Row 55. > Need to h...

limited No. of functions in one cell ?!
i want to have 30 IF functions in one cell but I can only have 9-10 otherwise I get an error message. Thanks for helping! -- andreas prucker ------------------------------------------------------------------------ andreas prucker's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28424 View this thread: http://www.excelforum.com/showthread.php?threadid=480168 Even if you could put 30 IF functions in a formula (you can't, you're limited to 7 levels of parens), the formula would be nearly unreadable and unmaintainable. Most likely, there is a better w...

Dates and Cell Values
How can I get the minimum value from column C for all 8/24/2009 dates in Column A? I tried some index and match functions but still doesn't work. Column A Column B Column C Column D 8/24/2009 12:12:56 AM 113 904 8/24/2009 12:52:56 AM 114 908 8/24/2009 1:32:56 AM 114 907 8/24/2009 2:12:56 AM 112 897 8/24/2009 2:52:56 AM 113 902 8/25/2009 12:13:57 AM 82 654 8/25/2009 12:53:57 AM 81 650 8/25/2009 1:33:57 AM 81 650 8/25/2009 2:13:57 AM 81 650 8/25/2009 2:53:57 AM 81 649 8/25/2009 3:33:57 AM 81 647 8/25/2009 4:13:57 AM 81 646 8/25/2009 4:53:57 AM 81 643 8/...

Drop down list & reference cells...
Three questions: If I create a spreadsheet with two values, say item and time (to complete task), can I set a cell in another spreadsheet, same workbook, to pick from the item column? Now, if that is possible... how can I set it up so that when I pick an item the time cell is updated with the time for the item picked. For example: I select, Daily Checklist, and the field to the right is updated based on the value in the spreadsheet I mentioned above. Make sense...??? Finally, Is it possible display a list of those where are not selected, so I have have a "remaining" items list...

Conditional Format Cell
I have excel 200 and am having issues doing this. I need to create a cell that when empty would be yellow but when its typed in I need it the backgroud to change to white. I tried a conditional format from the menu (IF Cell Value equals "" then yellow).How can I do this? Am I messing something up on the Conditional FOrmat or do I need to try something else. Please Advise. Thanks Hi in the conditional format dialog chosse "Formula" in the listbox. Then enter the following formula (assuming that your taget cell is A1): =A15="" and set the background to yell...

Highlight Rows
Is there a way to program an expression close to... If cell in column E equals string of text "Pending", select entire row and change background color/patern to yellow? Hi, Conditional formatting. Select the row(s) to which this applies then Format|Conditional format - Select 'Formula is' from the dropdown and enter the formula =$E2="Pending" Click Format and on the 'Pattern' tab and choose yellow. OK Note that you should change the 2 to the top row of your selection -- Mike When competing hypotheses are otherwise equal, adopt th...

function returning Text to cell
First thanks for taking the time to help a poor soul out. Here is my issue. I have a list of vendor names. The vendor names are some what of an abbreviation of the actual name. I want to write a function that will take in the range of Vendor abbreviations and replace it with the full name. I thought a 'Select Case' would be easiest. I also would like to return the name to the cell itself not another cell. The cell is not turning red either. Here is my code : Function ConvertVenName(Ven As Range) As String Dim xlCalc As XlCalculation Dim savScrnUD As Boolean savS...

Date Subtraction #2
Hi I run Win2K with Excel 2K. I would like to enter a date in a cell (eg Oct-05) and have the preceding 11 cells automatically put the previous months in. Example: In cell A15 = Oct-05 (entered) In cell A14 = Sep-05 (automatically) In cell A13 = Aug-05 (automatically) In cell A12 = Jul-05 (automatically) etc etc etc Is there a formula that can do this? Any help will be much appreciated...thanks ! John On Wed, 9 Nov 2005 16:34:57 -0800, "John Calder" <JohnCalder@discussions.microsoft.com> wrote: >Hi > >I run Win2K with Excel 2K. > >I would like to ent...

% query based on dates.
Hi, I need to run a query to find out % of site visits made within 10 days of receipt of a case. Both fields are of the date type and the site visit date needs to contain no NULL values. The fields are: - DateRecd = date of receipt of case DateR = date of site visit Anyone able to help? Thanks, Andy. abyron@solihull.gov.uk -- Message posted via http://www.accessmonster.com Sorry, the DateR field is a text field and not a date field for some reason. Andy. abyron@solihull.gov.uk -- Murdoc Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-queri...

save document which a date and deleted the old document
I like to save an excel document with the name which is in A1 and the date of today which is in B1 The old document must be deleted Who can help Thanks Peter Here's a way: Sub SaveAndDate() Dim lStr_CurFileName As String With ThisWorkbook lStr_CurFileName = .FullName .SaveAs .Path & "\" & Range("Sheet1!A1") & " " & Format(Range("Sheet1!B1"), "yyyymmdd") & ".Xls" End With Kill lStr_CurFileName End Sub -- Regards Andy Wiggins www.BygSoftware.com Home of "Save and BackUp...

automatically advance to next cell without hitting enter or tab
does anyone know of a way to have Excel move automatically to the next cell after entering one digit (without hitting enter every time?) If there is some sort of script or something that anyone is aware of that already exists that I would need, can you please point me to it and tell me how to set it up? Hi AFAIK this is not possible as you can't run macros while in Edit mode -- Regards Frank Kabel Frankfurt, Germany corby wrote: > does anyone know of a way to have Excel move automatically to the next > cell after entering one digit (without hitting enter every time?) > > I...