+ Reply to Thread
Results 1 to 2 of 2

Google Sheets Conditional Formatting based on Date and Time cells

  1. #1
    Registered User
    Join Date
    03-11-2021
    Location
    Netherlands
    MS-Off Ver
    Google Sheets
    Posts
    7

    Google Sheets Conditional Formatting based on Date and Time cells

    I have a list of appointments wich consist of a appointment date cell, an appointment time cell, a last name cell and a phone number cell.

    I want a row to turn green when the time is past on the appoinment date and orange when the appointment date is todays date and the time is within an hour from the current time.

    Example:
    - Appointment is 11-03-2021 at 12:15. When the appointment is past, like 11-03-2021 at 12:16, the entire row has to turn green and stay that way.
    - Appointment is 11-03-2021 at 13:15 and the current date/time is 11-03-2021 at 12:16, the row has to turn orange, because the appointment is within an hour.

    If possible, I want to keep the date and time cells seperately.

  2. #2
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Google Sheets Conditional Formatting based on Date and Time cells

    Hello htevents and Welcome to Excel Forum.
    If this were being done in Excel then it might go something like this.
    Current date displayed in cell D2 using: =TODAY()
    Current time displayed in cell E2 using: =MOD(NOW(),1)
    The appointments are listed in columns A:B
    For Green: =AND($A2<=$D$2,$B2<$E$2)
    For Orange: =AND($A2>=$D$2,$B2<SUM($E$2,1/24))
    Note that the order of the conditional formatting rules is important.
    Don't know that this translates directly to google sheets, but perhaps it is at least a start.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

+ 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. [SOLVED] Google Sheets: Conditional Formatting with multiple cells
    By JohnDoh in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 01-21-2021, 09:15 PM
  2. [SOLVED] Google Sheets Conditional Formatting based on another cell value
    By Badvgood in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 3
    Last Post: 12-16-2020, 12:07 PM
  3. [SOLVED] IF statements based on conditional formatting? Google sheets
    By rayted in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 3
    Last Post: 09-28-2018, 02:41 PM
  4. [SOLVED] Google sheets: Conditional formatting (same concept, different cells)
    By rayted in forum For Other Platforms(Mac, Google Docs, Mobile OS etc)
    Replies: 1
    Last Post: 08-03-2018, 10:40 AM
  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. Replies: 13
    Last Post: 07-09-2014, 04:10 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