+ Reply to Thread
Results 1 to 4 of 4

Due dates formula in conditional formatting that ignores blanks

  1. #1
    Forum Contributor
    Join Date
    08-29-2011
    Location
    Wisbech England
    MS-Off Ver
    Excel 2010
    Posts
    308

    Due dates formula in conditional formatting that ignores blanks

    Hi Everyone :-)
    I need help with the following please:
    I have a training matrix with the dates of training completion I also have a copy of the same matrix but this one contains due dates so that every time someone puts the date in matrix it will automatically update due dates, this is a formula for due dates =DATE(YEAR(H6)+1,MONTH(H6),DAY(H6)) problem is when I copy this formula across it returns this date 31/12/1900 I can't figure out where this date is coming from there is no date in the matrix cell??? Probably it needs to ignore blanks? I don't know.....
    Also I want any cell that is over due base on due date to go red I've tried this but again when copy formula all blank cells in the matrix turned red.

    I will appreciate your help
    Thank you.
    Attached Files Attached Files

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Due dates formula in conditional formatting that ignores blanks

    Perhaps change it to..

    =IF(H6="","",DATE(YEAR(H6)+1,MONTH(H6),DAY(H6)))
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Forum Contributor
    Join Date
    08-29-2011
    Location
    Wisbech England
    MS-Off Ver
    Excel 2010
    Posts
    308

    Re: Due dates formula in conditional formatting that ignores blanks

    Will try this
    Thanks :-)

  4. #4
    Forum Contributor
    Join Date
    08-29-2011
    Location
    Wisbech England
    MS-Off Ver
    Excel 2010
    Posts
    308

    Re: Due dates formula in conditional formatting that ignores blanks

    This worked!! Thank you:-)

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Conditional formatting, with dates and blanks
    By jjsweeney in forum Excel General
    Replies: 1
    Last Post: 10-07-2013, 04:40 AM
  2. Transfer based on drop down menus that ignores blanks
    By rowlandjp in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-01-2013, 10:46 AM
  3. [SOLVED] Recording macro totally ignores Conditional Formatting
    By yelsobky in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 08-24-2012, 05:57 AM
  4. Replies: 2
    Last Post: 07-11-2012, 08:58 AM
  5. Replies: 9
    Last Post: 03-26-2012, 02:16 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1