Date Problems in Excel

[LongBeach]

Hi Everyone,

I am having problems trying to do something, when it seems so simple.

I have two excel files. One excel file has all the date values and th
other excel file, link to cells from the first excel file. The cells i
links to are date values. I can the 2nd file to the first file. Not
problem. But some problems are occuring.

For example, if I don't have a date value in the first file I get th
value 1/11/1900, which I don't get why that is there?

Also, after I get the date value, I want to compare it to anothe
value, then set the cell to a particular color. So if the date in th
first file cell is 10/15/04 and the due date was suppose to b
10/14/04, the cell gets turned to red. I hope that makes sense.

I will really appreciate any help in this. Thank you

 

[Nick Hodge]

LongBeach

You may want to check out how Excel holds dates and times here

http://www.cpearson.com/excel/datetime.htm

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)

 

[Guest]

The reason for the 1/11/1900s: Are you sure it isn't 1/0/1900? It has to do
with the way Excel deals with blank cells when you use them in formulas. If
you use a blank cell in a formula that is looking for a number, it becomes
equivalent to zero. In the Excel date scheme, all dates are really numbers
that tell how many days it has been since 1/0/1900 (yeah, a wierd date). So
in a formula (or link) that converts to a date, zero becomes 1/0/1900. If
you are really getting 1/11/1900 there must be something in your link that
ends up equalling the number 11.

For the second issue: you just need to use conditional formatting. From the
format menu choose conditional formatting. For the condition use "Formula
is" and enter something like this (assuming you are in cell A1 and the due
date is in B1):
=(A1>B1)
If you use your cell references carefully (absolute vs. relative) you can
then copy this cell and paste formats into other cells to recreate the
conditional formatting without needing to duplicate the formula in each cell.

HTH...
K Dales

 

[Gord Dibben]

Long

Replies in-line.

Hi Everyone,

I am having problems trying to do something, when it seems so simple.

I have two excel files. One excel file has all the date values and the
other excel file, link to cells from the first excel file. The cells it
links to are date values. I can the 2nd file to the first file. Not a
problem. But some problems are occuring.

For example, if I don't have a date value in the first file I get the
value 1/11/1900, which I don't get why that is there?

A cell linked to a blank cell returns 0, which is Jan 01, 1900

Either choose not to show zero values through Tools>Options>View or trap the
zero and turn to a blan-looking character.

=IF(sheet1!A1="","",sheet1!A1)

Also, after I get the date value, I want to compare it to another
value, then set the cell to a particular color. So if the date in the
first file cell is 10/15/04 and the due date was suppose to be
10/14/04, the cell gets turned to red. I hope that makes sense.

Use Conditional Formatting for this.

Select the cell with 10/15/04 and Format>CF>Formula is:

=cellref>othercellref

OR select the cell and Format>CF>Value is "greater than" othercellref.

Click on Format>Pattern. Pick Red color and OK your way out.

Gord Dibben Excel MVP