# Cell color change base off of date and time

1. ## 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!!

2. ## 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?

3. ## 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. ## 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. ## 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.

6. ## 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. Originally Posted by Richard Buttrey
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. ## Re: Cell color change base off of date and time

Hi,

The Yellow CF is

Formula:
`Please Login or Register  to view this content.`

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

9. ## 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. ## Re: Cell color change base off of date and time

Just add a logical AND condition. i.e.

Formula:
`Please Login or Register  to view this content.`

11. ## Re: Cell color change base off of date and time

That worked perfectly. Thanks for your help!!

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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