+ Reply to Thread
Results 1 to 15 of 15

conditional formatting by date

  1. #1
    Forum Contributor
    Join Date
    02-18-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    356

    conditional formatting by date

    Hi Guys and thanks in advance to you more knowledgable friendly people.

    Column E is a date column of first enquiry
    what i need to be able to do in column F is add two days on
    i could do =E3+2 and drap down the column but when i drag it down the column in preparation for any data entered in column E then it puts the date in as 02 January 1900 where as i would like the cell to remain blank.

    also when the date in column f is reached ie today for example i would like the cell to go red.

    Your help would be much appreciated

  2. #2
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: conditional formatting by date

    Use an IF() formula...

    =IF(E3>0, E3+2, "")

    Use Conditional Formatting to highlight but unsure which cell you want to highlight. Regardless, use something like

    =INT(F2)>=INT(NOW())

    as the conditional format formula adding the Format you want.
    Last edited by cytop; 05-19-2017 at 08:42 AM.

  3. #3
    Forum Contributor
    Join Date
    02-18-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    356

    Re: conditional formatting by date

    hi Cytop

    Thank you for the formula that is superb
    It is Column F im looking for cells to go red when the date is hit

  4. #4
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: conditional formatting by date

    Like I said, just use the CF formula in my first post.

  5. #5
    Forum Contributor
    Join Date
    02-18-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    356

    Re: conditional formatting by date

    ive tried that cytop and dragged it down the column
    but its not working

    I have put =IF(E3>0, E3+2, "") in cell F3 and i have conditionally formatted the cell F3 with formula =INT(F2)>=INT(NOW()) and the cell to go red.

    i input 27/05/17 into cell E3 so cell F3 should read 29/05/17 ( which it does )
    but its flagged up red even though its ten days away

    btw appreciate your help

  6. #6
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: conditional formatting by date

    cell F3 with formula =INT(F2)>=INT(NOW())
    Would have thought that would be =INT(F3)... but maybe I misunderstand.

  7. #7
    Forum Contributor
    Join Date
    02-18-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    356

    Re: conditional formatting by date

    i actually meant to change that as i knew thats what you meant so it is that already

  8. #8
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: conditional formatting by date

    Would need an image of your conditional formatting rules or (better) upload your workbook (Go advanced> Manage Attachments) to determine why the CF is not working.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  9. #9
    Forum Expert
    Join Date
    02-14-2009
    Location
    .
    MS-Off Ver
    ................
    Posts
    2,840

    Re: conditional formatting by date

    If you mean you only want to highlight today's' date in Col F, then remove the '>' from the Conditional Format

  10. #10
    Forum Contributor
    Join Date
    02-18-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    356

    Re: conditional formatting by date

    Hi Cytop
    sorry got collared by a customer
    Please see attached
    Attached Files Attached Files

  11. #11
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: conditional formatting by date

    Select your range (i.e. F3:F96)
    Conditional Formatting>New Rule>Use Formula

    =AND(ISNUMBER(F3), INT(F3)>TODAY())
    Is that what you are after?
    Attached Files Attached Files

  12. #12
    Forum Contributor
    Join Date
    02-18-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    356

    Re: conditional formatting by date

    i dont know if im doing anything wrong but it doesnt work
    ive downloaded the attached and it still doesnt work
    if i put the 17th may in E3 it should then read 19th May TODAY in F3 and therefore go red

  13. #13
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: conditional formatting by date

    Sorry, I set the condition to > TODAY(). Add an equals sign >= TODAY()

  14. #14
    Forum Contributor
    Join Date
    02-18-2014
    Location
    United Kingdom
    MS-Off Ver
    Excel 2010
    Posts
    356

    Re: conditional formatting by date

    hi chemist , that works well so thank you very much

    if id have thought a bit harder as in what i would like it to do i would have suggested this.
    is there a way that once the cell hits todays date ( you know what i mean by that as its done ) that it can stay red until the corresponding cell in column G has a yes in it
    so if someone doesnt deal with it on the day it flags up then it stays red until the corresponding cell in g reads yes ?

    I really appreciate your help

  15. #15
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: conditional formatting by date

    =AND(ISNUMBER(F3), INT(F3)>TODAY(), G3<>"Yes")

+ 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. Formatting Cells with Date or Text Values in a Conditional Formatting Formula
    By Phil Hageman in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-15-2014, 09:36 AM
  2. Replies: 5
    Last Post: 04-07-2014, 06:02 AM
  3. Replies: 3
    Last Post: 11-23-2013, 06:21 AM
  4. Replies: 2
    Last Post: 09-19-2013, 10:34 AM
  5. Conditional Formatting on another cells date value to the current cell date
    By jennys83 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-28-2012, 12:55 PM
  6. Replies: 5
    Last Post: 08-22-2012, 05:06 PM
  7. Replies: 9
    Last Post: 05-01-2012, 09:52 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