Check time is between 2 times and highlight cell
sample rota.xlsx
I am trying to set up a rota based on 24hr shifts.
When I type in a start time and end time I want the relevant time of day cell to be highlighted.
I know the first part works on its own but the later time throws the formula out.
Please help!
Hi Tetley09,
Correction in C5 cell formula
=IF(OR($A5="OFF",$B5="OFF"),0,MOD(B5-A5,1))
Drag the C5 cell formula below to the C6 cell
Try the below formula in your conditional formatting formula area
=AND(D$3>=$A$6,D$4<=$B$6,D$4<>0)
Refer the attachment file for details.
Hope that helps!
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
smaple rota2.xlsx
Brilliant except it doesn't allow for 24hr
There are some shifts that go into 02:00 and some are a night shift?
I have attached the document.
Can anyone help with the 24hr problem on the conditional formatting?
All I need is the conditional formating to work for after midnight.
Last edited by Tetley09; 10-29-2012 at 07:20 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks