SOLVED

New Contributor

Apply a specific cell color within a time range

So what I really wanted to do is there is a timeframe. The only data that i need to use in the given sample data is the time not the date. I wanted to color the specific cell to YELLOW if the time is between 0700 - 1459 then color ORANGE when the time is between 1500-2359.

Is there a way for this to be done in conditional formatting or this needs to be coded in vba?

 received time 9/15/22 0715 9/15/22 1715 9/15/22 2315

Thank you in advance for all of the help that will be commented here.

4 Replies
best response confirmed by alexisnamaneh (New Contributor)
Solution

Re: Apply a specific cell color within a time range

@alexisnamaneh You could use the following Conditional Formatting rules:

For yellow:

``=MOD(B3,1)=MEDIAN(MOD(B3,1),TIME(7,0,0),TIME(14,59,59))``

For Orange:

``=MOD(B3,1)=MEDIAN(MOD(B3,1),TIME(15,0,0),TIME(23,59,59))``

Re: Apply a specific cell color within a time range

I really wanted to thank you for this!

I wanted to maximize this opportunity to ask another question same with this scenario but what i want to happen is when the cell color is yellow. What i wanted to happen next is the whole row of B3 will be color yellow.

Still thank you for your help Riny_van_Eekelen!

Re: Apply a specific cell color within a time range

@alexisnamaneh Change the rule to:

``=MOD(\$B3,1)=MEDIAN(MOD(\$B3,1),TIME(7,0,0),TIME(14,59,59))``

and apply it to, for instance, to =\$A\$3:\$I\$3 or to =\$3:\$3 if you rely want to apply it to the entire row (i.e. across 16000+ columns).

Re: Apply a specific cell color within a time range

got it! thank you so much for the help