Microsoft Excel - conditional formatting date field
Asked By Steve
09-Aug-11 05:16 PM
In Excel 2007
I have multiple cells which generate a DATE value using a WORKDAY formula. Sample formula: =WORKDAY(P19,-N19-K19,AE5:AE13)
When the relevant formula cells are empty, the result cell displays "01/00/00", the default Jan 1, 1900 date. I would like to hide that value by changing the font to white whenever that value occurs but have been unable to write a conditional formula to recognize /distinguish it from all other dates.
Any help would be greatly appreciated.
Thanks.
Steve
wally eye replied to Steve
Microsoft actually stores dates as numbers, the easiest way to set this up would be:
=a2=0
Where a2 is the cell you want to hide. You would then apply the format to the entire range of cells to possibly be hidden.
Pichart Y. replied to Steve
Hi Steve,
use conditional format...with this setting then copy to all the rest cells.
Hope this help.
Pichart Y.
Devil Scorpio replied to Steve
Hi Steve,
You can achieve by using IF function.
Request u to use this formula
=IF(WORKDAY(P19,-N19-K19,AE5:AE13)="01/00/00","",WORKDAY(P19,-N19-K19,AE5:AE13))
Note:- Check the format of cell, then replace the cell value(01/00/00) as per the format.
Vickey F replied to Steve
Conditional formatting enables you to highlight cells with a certain color, depending on the cell’s value.
For example, highlighting positive values with green background color, and negative values with red.
(actually not only the color can be set, but every aspect of cell formatting can be applied this way - font style, size, bold, underline etc.)
Follow these links-
http://www.contextures.com/xlcondformat03.html
http://www.cpearson.com/excel/cformatting.htm
http://www.free-training-tutorial.com/conditional-formatting.html
Hope this will help you.
Steve replied to Steve
Wow. how simple it can often be. I had tried various number values before, such as for the day of the week, but never guessed that "01/00/00" would be valued as zero.
thank you to everyone who answered.
Regards,
Steve
Enregistrer dbf sous excel 2007 Excel Bonjour la Communaut??, Comment r??ussir ?? enregistrer un format dbf sous excel 2007? Merci pour vos conseils Bernie Excel - French Discussions Microsoft Excel (1) Excel 2003 (1) Excel 2007 (1) DBASE (1) Enregistrer (1) Communaut (1) Conseils (1
What are the differences between excel 2000 & excel 2007? Excel We're trying to decide if it would be beneficial to upgrade from excel 2000 to excel 2007. What are the differences between excel 2000 & excel 2007? Excel Miscellaneous Discussions Microsoft Excel (1) Excel 2007 (1) BA597438D5D2 (1) Previews
Conflict with Valid Range Reference Error in Microsoft Excel 2007 Excel Hi, While converting the Macros enabled Microsoft Excel 2003 to Microsoft Excel 2007 format, I am getting the following Conflict with Valid Range Reference Error. After clicking
Deleating All Selected Data Excel 2007 Excel Excel 2007 (What worked for Excel 2003 makes 2007 crash). I have run Microsoft Office Diagnostics and it can not identify any problems) When I try to delete data
SQL plus de Inclure les noms de champ dans SQL EXcel 2007 Excel Bonjour, Nous venons de miger de Microsoft Excel 2003 vers Microsoft Excel 2007. Dans la version Excel 2003, on avait avec clic droit sur une plage de donnÃ