Conditional Formatting on Cell with Date and Time

  • I have a range of cells that contain Date and Time values - I need to highlight those cells whose hours are equal to or greater than 4 pm. I have included a spreadsheet sample but essentially, Column A has the date range and regardless of the date I need to highlight anything greater than 4 PM. Any suggestions - Thanks Monti


    [TABLE="width: 76"]

    [tr]


    [TD="width: 101, bgcolor: transparent"]Date Range
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent, align: right"]7/17/2012 9:00
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent, align: right"]7/23/2012 12:10
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent, align: right"]7/24/2012 12:10
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent, align: right"]7/24/2012 12:10
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent, align: right"]7/24/2012 16:30
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent, align: right"]7/24/2012 16:00
    [/TD]

    [/tr]


    [tr]


    [TD="class: xl66, bgcolor: transparent, align: right"]7/24/2012 9:00
    [/TD]

    [/tr]


    [/TABLE]

  • Re: Conditional Formatting on Cell with Date and Time


    Use conditional formatting rule formula:


    =MOD(A2,1)>TIME(16,0,0)


    where A2 is top most cell selected to format

    Where there is a will there are many ways. Finding one that works for you is the challenge!

  • Re: Conditional Formatting on Cell with Date and Time


    Wow that was quick thank you so much - however, I am trying to format the cell that contains the value falling within the time frame - I tried your formula, but I get a True/False on the B column - see below - even on the one that was 4:00 pm - Thanks


    [TABLE="width: 271"]

    [tr]


    [td]

    Date Range

    [/td]


    [td][/td]


    [/tr]


    [tr]


    [TD="align: right"]7/17/2012 9:00[/TD]
    [TD="align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="align: right"]7/24/2012 12:10[/TD]
    [TD="align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="align: right"]7/24/2012 12:10[/TD]
    [TD="align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="align: right"]7/24/2012 12:10[/TD]
    [TD="align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="align: right"]7/24/2012 16:30[/TD]
    [TD="align: center"]TRUE[/TD]

    [/tr]


    [tr]


    [TD="align: right"]7/24/2012 16:00[/TD]
    [TD="align: center"]FALSE[/TD]

    [/tr]


    [tr]


    [TD="align: right"]7/24/2012 9:00[/TD]
    [TD="align: center"]FALSE[/TD]

    [/tr]


    [/TABLE]

  • Re: Conditional Formatting on Cell with Date and Time


    If you want to include 4:00 PM formula would be:



    =MOD(A2,1)>=TIME(16,0,0)


    you only said you wanted to know which are on or after 4:00 PM... there is no other timeframe indicated...


    also the formula is meant to go in your conditional formatting so it colours the cells .... not formulas in cells...

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!