Date Format

ScamIam

New Member
Joined
Feb 19, 2004
Messages
9
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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
WELCOME TO THE BOARD!

Try using conditional formating with this formula:

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

ScamIam

New Member
Joined
Feb 19, 2004
Messages
9
Thanks :eek: for the fast response!! So I would substitute A1 with each cell number? I will try this now.

ScamIam
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
ScamIam said:
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

New Member
Joined
Feb 19, 2004
Messages
9

ADVERTISEMENT

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

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
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
ABCD
11/1/2004----1/20/2004
21/21/2004----2/8/2004
32/9/2004----2/29/2004
43/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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454

ADVERTISEMENT

ScamIam said:
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
ABCD
1Jan1---Jan20
2Jan21---Feb4
3Feb5---Feb17
4Feb17---Feb22
5Feb-23--Mar7
Sheet1


The Conditional Formating formula for Cells A1:A5 is ...
Code:
=AND(--(TRIM(LEFT(A1,6)))<TODAY(),--(TRIM(RIGHT(A1,6)))>TODAY())
Is this what you are looking for?
 

phantom1975

MrExcel MVP
Joined
Jun 3, 2002
Messages
3,962
Yogi, you are such a show off!!! Nice work on that formula. I never think to use the TRIM function.
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi Billy:

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

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
OK - enough with the mutual masterbation you two - you're both MVPs, so I expect more bitching from now on :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,216
Messages
5,594,886
Members
413,947
Latest member
gizmolucy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top