Forum Discussion
incorrect formulation
NikolinoDE I didn't see any change in your conditional formatting formula. I implemented the last suggestion I made in the attached sheet and added more so it will check what day it is and auto-index down on the Jan sheet so 1 conditional formatting formula will work for every day in that month (I added the 2nd to the range and leave it to you to add the rest). You still need to tweak the formula for each category (MA1, MA2, etc...) since each needs its own color.
I also changed the formula to >= the start time but < end time because the don't actually work that hour/ half-hour period.
I also noted and corrected that your 1st 06:00am was actually 06:00am the next day and your 06:00am at the end was 05:59.999999999am (floating point error). You may want to look at the rest of the times and days for those and similar errors also.
Will be able to deal with it further tomorrow, if someone thinks of something by then it is very welcome.
In our conversation shows that it is better to change the date after midnight to the next day ... is there a formula where a date is taken from a cell and a fixed time can be in one cell? ..so I could ... just a thought..the day in the fixed time that comes from another cell...or am I in the wrong line of thought ... however, any additional help is welcome ... thank you again and will give you a message about the result.
Thx
Nikolino
NikolinoDE If you want to encourage or force entry to properly include (or not include) the additional day you can use conditional formatting (encourage by highlighting 'wrong' values) or data validation which can either give warning message or error message. Basically the condition will be that the value entered >= the value in the column before it (or no value can be >1 to not include the additional day). That formatting/validation could be applied to that whole table.
The last solution I sent should work for any 3rd shift time range entered as either same date or next day values (i.e. times in columns G and H should be evening of same day or morning hours the next day).