+ Reply to Thread
Results 1 to 5 of 5

Conditional Formatting based on TIME, regardless of Date?

  1. #1
    Registered User
    Join Date
    09-14-2021
    Location
    USA
    MS-Off Ver
    10
    Posts
    4

    Conditional Formatting based on TIME, regardless of Date?

    I'm trying to format only cells in columns AB and AQ that contain the time 00:00, regardless of the date. Is there a way to do this without adding additional columns?
    Attached Files Attached Files

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,604

    Re: Conditional Formatting based on TIME, regardless of Date?

    Select the cells in column AB, then click on Conditional Formatting | New Rule | Use a formula... , then put this formula in the dialogue box which pops up:

    =AND(MOD(AB6,1)=0,AB6<>"")

    Click the Format button | Fill tab and choose your colour, then OK your way out.

    Repeat, but for column AQ, and amend the cell references in the formula to AQ6.

    Hope this helps.

    Pete

  3. #3
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Conditional Formatting based on TIME, regardless of Date?

    Please try this CF rule with AB6:AQ10 selected:
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    EDIT: @Pete_UK: we collided again! - at least we have slightly different formulas
    Last edited by GeoffW283; 10-13-2021 at 08:42 PM.
    Geoff

    Did I help significantly? If you wish, click on * Add Reputation to say thanks.
    If your problem has been resolved please select ?Solved? from the Thread Tools menu

  4. #4
    Registered User
    Join Date
    09-14-2021
    Location
    USA
    MS-Off Ver
    10
    Posts
    4

    Re: Conditional Formatting based on TIME, regardless of Date?

    Perfect!!! Thank you so much. You're a lifesaver.

    **I should clarify, you are BOTH lifesavers!

  5. #5
    Forum Expert
    Join Date
    10-15-2018
    Location
    MA, USA
    MS-Off Ver
    2010, 2019
    Posts
    1,616

    Re: Conditional Formatting based on TIME, regardless of Date?

    Glad to help - thanks for the feedback and rep.

+ 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. Google Sheets Conditional Formatting based on Date and Time cells
    By htevents in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 03-16-2021, 10:39 AM
  2. Conditional Formatting based on time of day, regardless of date
    By YeahWhoDuh in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 09-10-2019, 04:08 PM
  3. Replies: 2
    Last Post: 07-19-2018, 04:04 AM
  4. [SOLVED] Conditional formatting based on date and time
    By Jalex5134 in forum Excel General
    Replies: 6
    Last Post: 10-26-2017, 05:55 PM
  5. Replies: 0
    Last Post: 06-15-2017, 06:28 PM
  6. [SOLVED] Conditional Formatting of Cells based off of date and time criteria
    By rhett7660 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 12-16-2015, 03:14 PM
  7. [Solved] Conditional Formatting based on date and time
    By HackerJL in forum Excel General
    Replies: 5
    Last Post: 06-08-2011, 06:58 PM

Tags for this Thread

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