+ Reply to Thread
Results 1 to 11 of 11

Cell color change base off of date and time

  1. #1
    Registered User
    Join Date
    10-07-2017
    Location
    connecticut, usa
    MS-Off Ver
    2016
    Posts
    6

    Thumbs up Cell color change base off of date and time

    Hello,

    So I have read through a few different threads here but cannot seem to find one that fits the problem that I have exactly.

    The goal is to get cells A-D (for the row with data being entered) to change color based off the date/time entered in cell B.
    I have it set up that the date and time automatically appears in cell B after a selection is made in cell A from a drop down list.

    if the date/time in cell B is between today 6am and 630pm then I would like cells A-D in that row to turn a shade of yellow
    if the date/time in cell B is between today 631pm and (tomorrow technically) 559am then I would like cells A-D in that row to turn a shade of blue.

    Its pretty much information entered for a matinee shift between 6am and 630pm would be yellow and information entered in for an evening shift between 631pm and 559am need to be blue. I will attach my spreadsheet if it will help. I was able to get it to work for the evening but not the matinee using a CF.

    Any exact formula I can enter in to use would be greatly appreciated!!
    Attached Files Attached Files
    Last edited by greenishsheep; 10-09-2017 at 02:29 PM.

  2. #2
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,335

    Re: Cell color change base off of date and time

    Hi,

    It would have been a little more obvious if you had unhidden the Notes sheet! - I assume that's the one you're talking about.

    What about times between 00:00 today and 6:00 today?
    Richard Buttrey

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  3. #3
    Registered User
    Join Date
    10-07-2017
    Location
    connecticut, usa
    MS-Off Ver
    2016
    Posts
    6

    Re: Cell color change base off of date and time

    ah! so sorry. I have it hidden so my coworkers don't start messing with it before its ready to go.

    I'm not all that formula savvy so you would have to help me out a little with what exactly I should try.

    Do you mean to set the CF as cell value between ="$b"=today(00:00) and ="$b"=today(06:00) ?

  4. #4
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,335

    Re: Cell color change base off of date and time

    No, I was merely asking what colour (if any) you wanted for times TODAY before 6:00.

    You've told us about 6:00 - 18:30 today and between 18:30 and 6:00 TOMORROW but it's not clear whether you want to paint a cell that has a time of say 4:00 TODAY.

  5. #5
    Registered User
    Join Date
    10-07-2017
    Location
    connecticut, usa
    MS-Off Ver
    2016
    Posts
    6

    Re: Cell color change base off of date and time

    If the date/time from cell B is from today 6:00am and 6:30pm then I would like cells A-D to all be yellow for that row.
    If the date/time from cell B is from today 6:31pm and (tomorrow) 5:59am, I would like cells A-D to all be blue for that row.

    It's business days that I am working with. So a matinee shift would be from 6am-630pm and an evening shift from 631pm to 559am on the following day.
    Last edited by Richard Buttrey; 10-07-2017 at 08:15 PM.

  6. #6
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,335

    Re: Cell color change base off of date and time

    Yes I understand that, but what about the time slot from midnight to 6:00 TODAY, or would a time in that slot never appear?

  7. #7
    Registered User
    Join Date
    10-07-2017
    Location
    connecticut, usa
    MS-Off Ver
    2016
    Posts
    6
    Quote Originally Posted by Richard Buttrey View Post
    Yes I understand that, but what about the time slot from midnight to 6:00 TODAY, or would a time in that slot never appear?
    That would fall under the evening timeframe so blue.

  8. #8
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,335

    Re: Cell color change base off of date and time

    Hi,

    The Yellow CF is

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    and the blue
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  9. #9
    Registered User
    Join Date
    10-07-2017
    Location
    connecticut, usa
    MS-Off Ver
    2016
    Posts
    6

    Re: Cell color change base off of date and time

    Hey Richard.

    So I plugged in your formula and the only thing is the blank cells all turned blue, but so far, once I make my selection from A and the date/time generates in B, it turns yellow. I wont know for sure if the evening hours will work accordingly until after 630.

    I tried adding a standard CF to keep the blank cells white but it was not working. Any insights on that?

    Thanks for your assistance so far.

  10. #10
    Forum Moderator Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    28,335

    Re: Cell color change base off of date and time

    Just add a logical AND condition. i.e.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    10-07-2017
    Location
    connecticut, usa
    MS-Off Ver
    2016
    Posts
    6

    Re: Cell color change base off of date and time

    That worked perfectly. Thanks for your help!!

+ 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. Change font color base on a value
    By jasond1992 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-20-2016, 02:45 AM
  2. Cell Color Change after time passes from a certain date
    By Ruggyz in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 02-26-2015, 12:28 PM
  3. Macro to Change color of cell after cross referencing date with a given date.
    By liajet24 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-24-2013, 12:33 PM
  4. [SOLVED] Macro to automatically change tab color to red base on a cell date
    By Chaunceycat in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 08-10-2012, 07:53 PM
  5. [SOLVED] Change cell color base on criterias from multible comboboxes !
    By Petter120 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-05-2012, 05:34 AM
  6. [SOLVED] change background row color with change of date in a cell
    By Urszula in forum Excel General
    Replies: 5
    Last Post: 05-17-2006, 03:00 AM
  7. [SOLVED] Change color of font base on time range
    By deathzorro in forum Excel General
    Replies: 2
    Last Post: 01-10-2006, 04:10 AM

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