Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Problems in Excel
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 -- LongBeac ----------------------------------------------------------------------- LongBeach's Profile: http://www.excelforum.com/member.php...fo&userid=1539 View this thread: http://www.excelforum.com/showthread.php?threadid=27005 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Problems in Excel
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): =(A1B1) 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 "LongBeach" wrote: 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? 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. I will really appreciate any help in this. Thank you. -- LongBeach ------------------------------------------------------------------------ LongBeach's Profile: http://www.excelforum.com/member.php...o&userid=15390 View this thread: http://www.excelforum.com/showthread...hreadid=270059 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Date Problems in Excel
Long
Replies in-line. On Mon, 18 Oct 2004 01:17:16 -0500, LongBeach wrote: 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 ToolsOptionsView 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 FormatCFFormula is: =cellrefothercellref OR select the cell and FormatCFValue is "greater than" othercellref. Click on FormatPattern. Pick Red color and OK your way out. Gord Dibben Excel MVP I will really appreciate any help in this. Thank you. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel date problems | New Users to Excel | |||
date problems | Excel Discussion (Misc queries) | |||
Date problems, is it a bug??? | Excel Discussion (Misc queries) | |||
Date problems | Excel Programming | |||
Problems with Find and Date Fields in Excel macro | Excel Programming |