Date Format

[ScamIam]

Sample Cells

1. Jan 1 thru Jan 20
2. Jan 21 thru Feb 1
3....



Is there a way, based on the cell dates above, to open the excel spreadsheet and have it highlighted to the date referenced in the cell to coincide with the date you are actually opening it?

TIA

ScamIam

 

[phantom1975]

WELCOME TO THE BOARD!

Try using conditional formating with this formula:

=TEXT(A1,"mmddyyyy")=TEXT(NOW(),"mmddyyyy")

 

[ScamIam]

Thanks for the fast response!! So I would substitute A1 with each cell number? I will try this now.

ScamIam

 

[Yogi Anand]

Sample Cells

1. Jan 1 thru Jan 20
2. Jan 21 thru Feb 1
3....

Is there a way, based on the cell dates above, to open the excel spreadsheet and have it highlighted to the date referenced in the cell to coincide with the date you are actually opening it?

TIA

ScamIam

Hi ScamIam:

Welcome to MrExcel Board!

It is not clear what are the entries in cells, and what is the criterion for highliting the cells. Please provide a clear description -- preferably using HTMLmaker (Colo's Cool utility downloadable fom the bottom of this MrExcel page) -- so we can see what you are working with -- and then let us take itfrom there.

 

[ScamIam]

Ok I am making a Pager duty schedule.

Cell A1 has Jan 1 --- Jan 20
Cell A2 has Jan 21 --- Feb 4

etc....


What I want is that when folks open the spreadsheet, a highlight goes to the cell range that matches the date they happen to open it on so they can quickly see who has the duty.


ScamIam


PS- Thanks for the warm welcome, I am sooooo NOOB to excel hehe

 

[phantom1975]

Cell A1 has Jan 1 --- Jan 20

The problem with that is that Excel doesn't recognize that data as being two dates. Excel figures that it is just text. Think of it as the difference between having a document with the word PHANTOM in it and a Bitmap image with the letters that spell PHANTOM. Spell check in the document will recognize the word phantom and spell check it. It can't spell check the picture because, well, it's a picture. I'm a dork.

I would recommend that you consider using two cells instead of one. Make cell A1 Jan 1 and cell B1 Jan 20. Now Excel can recognize the contents of the cells as being a numeric value instead of just text. If you really must have the hyphens, then put those in B1 and the second date in C1. You can then use conditional formatting in all of the cells that correspond with your date range. For example:

Book2
	A	B	C	D
1	1/1/2004	----	1/20/2004
2	1/21/2004	----	2/8/2004
3	2/9/2004	----	2/29/2004
4	3/1/2004	----	3/20/2004
Sheet2

The conditional formatting used in cells A1, B1, and C1 are the same:

Formula Is: =AND(A1<=NOW(),C1>=NOW())

 

[Yogi Anand]

Ok I am making a Pager duty schedule.

Cell A1 has Jan 1 --- Jan 20
Cell A2 has Jan 21 --- Feb 4

etc....


What I want is that when folks open the spreadsheet, a highlight goes to the cell range that matches the date they happen to open it on so they can quickly see who has the duty.


ScamIam


PS- Thanks for the warm welcome, I am sooooo NOOB to excel hehe

Let us see the following illustration ...

Book2
	A	B	C	D
1	Jan1---Jan20
2	Jan21---Feb4
3	Feb5---Feb17
4	Feb17---Feb22
5	Feb-23--Mar7
Sheet1

The Conditional Formating formula for Cells A1:A5 is ...

=AND(--(TRIM(LEFT(A1,6)))<TODAY(),--(TRIM(RIGHT(A1,6)))>TODAY())

Is this what you are looking for?

 

[phantom1975]

Yogi, you are such a show off!!! Nice work on that formula. I never think to use the TRIM function.

 

[Yogi Anand]

Hi Billy:

I get inspiration from nice work by you -- now Let Us Keep EXCELing!

 

[PaddyD]

OK - enough with the mutual masterbation you two - you're both MVPs, so I expect more bitching from now on