How to tell excel that time 0000 is greater than 2300?

[Editor333]

Need: A schedule in excel is required for a large team. Schedule gets changed every month and people are rotated (mid person will do the night and night will be moved to morning etc). There are five shifts in a day, each shift having 5 teams and each team having at least 8 members.


Structure: First column contains the name, second the start time, third the end time and then there are 24 columns after that, each representing the hour of the day starting form (7:00) in 24 hours format. Now if there is a person whose shift starts from 7 A.M. and ends at 4:00 P.M. I would like to have the 9 cells in that row in columns 7 8 9 10 11 12 13 14 15 to go red or any color. I can do that by conditional formatting without any problem for shifts that are starting between 00:00 and 1500 however when it comes to shift that starts at 1700 and ends at 02:00 then excel only highlight the 7 cells instead of 9 and in some cases it highlight 10 cells instead of 9. Please see [Sheet Snapshot][1]


What I have done to get what I want: Highlight if the value in Start time column is equal to or greater than value in column D (700) and is also equal to or less than value in column E (800). It works fine other than the shifts that I just mentioned above because excel does not know that 0000 in this case is bigger than 2300 hence doesn't highlight the cell based on that.


Question: Is there a way I can tell excel ,through formulas, exactly what I want to do?


[1]: https://i.stack.imgur.com/rkQAV.png

 

[thisoldman]

To return TRUE or FALSE, try this in D2 and copy downward and to the right:

=IF($B2>$C2, OR($B2<=D$1, $C2>D$1), AND($B2<=D$1, $C2>D$1))


To return 1 or 0, try this in D2 and copy downward and to the right:

=($B2>$C2)*(($B2<=D$1)+($C2>D$1)) + ($B2< $C2)*(($B2<=D$1)*($C2>D$1))

 

[thisoldman]

I don't know how you have applied your conditional formatting. This is how I would do it.

After entering the formula in the cells, I hide the results by using custom cell formatting.

Select the cells with the formulas and bring up the "Format Cells" dialog by pressing Ctrl+1. On the Number tab, select "Custom". In the "Type:" box enter ";;;"—three semicolons with no spaces and without the quotation marks. This hides any display in the cell, but the formula is still visible in the formula bar.

For the conditional formatting, with the first formula, all you have to do is highlight the cells that are equal to TRUE. If you choose the second formula, highlight the cells that are equal to 1.

 

[Editor333]

The whole day today, I had been studying the array formulas hoping that might have the solution for my problem and learned a lot yet I couldn't figure out how to solve the problem at hand.. It is 6:00 A.M. here, I have been up whole night trying to get it solved and all of a sudden I thought to check if my question got attention of anybody and here you are, the star!

You Rock thisoldman. It did the magic. I don't really have words to thank you for such a quick turnaround and I really appreciate you taking the time to understand the problem (I was a bit skeptical as in If I was able to make my point across) and coming up with exactly what was required.

Below is what I got;

https://i.stack.imgur.com/8jDwT.png

Thanks a quattuordecillion!

To return TRUE or FALSE, try this in D2 and copy downward and to the right:

=IF($B2>$C2, OR($B2<=D$1, $C2>D$1), AND($B2<=D$1, $C2>D$1))


To return 1 or 0, try this in D2 and copy downward and to the right:

=($B2>$C2)*(($B2<=D$1)+($C2>D$1)) + ($B2< $C2)*(($B2<=D$1)*($C2>D$1))

 

[Editor333]

I saved excel some memory and pasted your suggested formula in conditional formatting under "User a formula to determine which cells to format" and it did it.

Thanks once again!

I don't know how you have applied your conditional formatting. This is how I would do it.

After entering the formula in the cells, I hide the results by using custom cell formatting.

Select the cells with the formulas and bring up the "Format Cells" dialog by pressing Ctrl+1. On the Number tab, select "Custom". In the "Type:" box enter ";;;"—three semicolons with no spaces and without the quotation marks. This hides any display in the cell, but the formula is still visible in the formula bar.

For the conditional formatting, with the first formula, all you have to do is highlight the cells that are equal to TRUE. If you choose the second formula, highlight the cells that are equal to 1.

 

[thisoldman]

You're welcome. I'm happy to have helped.