Conditional formatting a cell colour between specific times

[D4V33]

Hi there, Im trying to create a spreadsheet that is filled out at work at the end of each 12 hour shift. Its basicly just an ongoing table of Brewery tanks and contents etc. I have attached a very basic example of the spreadsheet (the actual spreadsheet is huge, with much more info,, but its at work and im now at home trying to get this worked out).

All I want is for a cell some where on the current shifts spread sheet to colour green to indicat that this is the sheet the operator should be filling out, so that its a little harder to accidentaly fill out a sheet for the wrong day. I have purposly coloured a cell at the top of the screan as an example of how i would like it to look (although it doesnt have to be the date cell)

Im hopeing that this can be done and that at 7am tomorrow morning the cell for the next days day sift will automaticaly go green, indicating that we are now to start using the sheet for the next day.

Can this be done?

I thought I had it when I set the cell to "format only cells with dates occouring today", but as the shift run from 7am-7pm and 7pm-7am etc this does not work.


Many thanks in advance!

David

[Richard Buttrey]

Hello and welcome to the forum

Use a Conditional Format.
Pick the 'Formula' option and enter

Please Login or Register  to view this content.

where A1 is a cell containing a date.

[etaf]

you should be able to use a conditional format formula to test the date and time
and compare to NOW()

BUT you would need to add the times into the cells

Night Shift - 07:00pm
i assume this starts a 7:00 PM to 7AM

Day Shift - 07:00am
and 7am to 7pm

you could include the time in the cell and just display the date
and then compare with a conditional format

i will setup and EDIT here with a sample

[D4V33]

Hello and welcome to the forum

Use a Conditional Format.
Pick the 'Formula' option and enter

Please Login or Register  to view this content.

where A1 is a cell containing a date.

I tried this, but then realised that it didnt work as the shifts run from 7am-7pm and 7pm-7am etc

[D4V33]

Yes that would be great!

I wouldnt really matter if its was the date cell thats highlighted or not, it just needs somthing to make it a little more dummy proof.

Thank you

[etaf]

one of the issue is its a merged cell that holds the date
so its only A2 and that causes an issue

still looking into - but website crashes

[D4V33]

Keeps crashing on me too.

It desnt have to be a merged cell, I have just did this as an example of how the spreadsheet is laid out.

You can happily add a new row somwhere at the top of the page with or without the date.

[etaf]

i think this rule should work

=AND(A2<>"", A2<=NOW(), H2>=NOW())

But you need to put the times into the cell

so 25/1/17 07:00 for the day shift
25/1/17 19:00 for the night shift

can still just display the date and not the time

may not want the = in both as it may highlight both cells at 7am or 7pm

=AND(A2<>"", A2<=NOW(), H2>NOW())

=now()
includes the date and time as the system time
BUT it will only work , when you open the spreadsheet

a recalculation is needed to update the NOW

so if they open at 06:58
it will highlight the night shift
and then a calc is needed to get the new system time
otherwise nothing changes at 07:02 or any time until a recalc is started

try it - put
=NOW()
into any cell - and see that you need to edited a cell to calc or F9

you need VBA to refresh , without touching the spreadsheet

hope that works

[D4V33]

Thank you very much, I will give this a try!!

[etaf]

i have edited my previous post - you may want to re-read

[D4V33]

Thats works perfect. Didnt need to change a thing. I altered the times to a couple of minutes ago and it worked a treat!!

Thank you very much, it is very much appreciated! I have spent hours googling and trying different formulas before thinking "this is over my head" lol

Cheers again
Dave

[etaf]

you are welcome

just in practice - watch the sheet is updated and calc F9 used

as mentioned

if they open the sheet at 6:50 in prep to add data - it will use the wrong section - if the next shift opens - ie day shift

maybe look at the process used and timings