Time in Excel

Hi Guys,

In Excel I want to write a formula involving times for conditional formatting, what I need is:

If B6 has a value > 7:00am AND < 10:00am, OR B6 has a value > 1:00pm AND < 3:00pm then F6 is coloured Green, if not F6 is coloured Red.

I have tried the 'Insert Function' but all I managed to get is more confused.

Appreciate any help,

Phil

 

What version of Excel are you using?

 

Hi Usafveteran,

I am using 2002 with SP3,

Phil.

 

Code:

OR(AND((HOUR(B6)>6);(HOUR(B6)<10));AND((HOUR(B6)>12);(HOUR(B6)<15)))

This formula is TRUE 07:00-09:59:99 and 13:00-14:59:59

Use conditional formatting of F6.

Hope this helps.

 

Hi Gabethebabe,

Thanks for the formula, I am supposed to put this into the cell arn't I? I can't get it to work, every time I put it into the cell, preceded with '=' it comes up with 'Invalid' for : '(HOUR(B6)>6);(HOUR(B6)<10)'.

I have rechecked that I have inserted it correctly, the times in B6 are not whole hours, but hours and minutes, I am entering as 07:15 etc.

 

I think you just need to change the punctuation:
=OR(AND((HOUR(B6)>6),(HOUR(B6)<10)),AND((HOUR(B6)>12),(HOUR(B6)<15)))

 

Thanks GCWesq,

That's sorted it out.

 

Now, you just need to add the conditional formatting, right?

 

Hi Guys,

Me again, that formula worked great, but it does mean that the column is coloured when there is no data in B6, well I tried modifying it so that the column is not coloured until there is data in B6:

=IF(B6="",(OR(AND((HOUR(B6)>6),(HOUR(B6)<10)),AND((HOUR(B6)>12),(HOUR(B6)<15)))),0)

Can you guys tell me why this doesn't work please, and what I need to do to make it work,

Thanks.

 

You need two separate formulae (each condition can only have a True or False value).
Keep the original formula in your first condition:

=OR(AND((HOUR(B6)>6),(HOUR(B6)<10)),AND((HOUR(B6)>12),(HOUR(B6)<15)))

Click on Add in the Conditional Formatting panel, and put the following under Condition 2 (using Formula Is... again):

=B6<>""

Give this the fill colour you want when the cell has a value, but it's outside the times you specified.

 

Thanks GCWesq,

I have now learnt something else about Excel, I didn't realise you could use Conditional Formating with the formulas like that.

Works a treat!

 

Excellent!