Forum Discussion
A formula to check whether "Start time" column is after "+12 hour" column
In the example above, the first column end time is 22:00, +12 hours column is 10:00 (this means that the employee can start work anytime after 10:00 to ensure they have gotten their 12 hour rest), and the start time was 12:00. In this example since the start time is 12, its after the rest time so this timing is acceptable).
In the second example, the end time is 16:53, +12 hours is 4:53, and the start time is 2:00. Since the start time is within the rest time, this should be amended. Since there are a lot of data and rows and columns. I want a formula that can check and compare the "+12 hours" column and "start time". If the "start time" is before "+12 hours" column, I would appreciate it if the formula could either highlight that row or indicate that it hasn't met the 12 hour hour criteria in another way.
Since there's a lot of columns and rows, if its a simple solution that doesn't require too much complication would be great, if possible.
2 Replies
Crossposted!!
https://learn.microsoft.com/en-us/answers/questions/4374459/a-formula-to-check-whether-start-time-column-is-af
part of the answer depends on how those cells are formatted/entered. For example is that 22:00 entered as just 22:00 or is it 2025-07-13 22:00. Also, will the +12 always be the next day and will the Start Time always be the next day. If yes like shown in the simple examples you can highlight which columns you want to get highlighted (e.g. just +12 hour column, or Start Time column, or the 3 columns from +12 through Start time, or maybe the whole sheet so the whole row will get highlighted). Then go to Conditional Formatting (on the Home menu tab) and select 'New Rule'. Then select "Use a formula to determine which cells to format".
Then you want to enter the formula based on the upper left corner of the range selected. In this example I assume you have +12 in column B and Start time in column D and I highlighted cells A3:Z1000
The $D and the $B means always look at those columns while the number 3 means to start looking at row 3 and move down to the corresponding row as it scans the page.
You also have to hit 'Format' and choose how to highlight the cells (I chose to fill with red)
