Check if time is between start and end time, then show 1 or 0

[jaynes]

Good morning,

I have been working very long on creating an employee schedule which accounts for time zones and shift differences. I am so close, but the last comparison formula is flunking, and I have no idea why. I may be looking at this too long.

Anyway, I have weekdays broken down in .5 hour increments. I am comparing the .5 our increments to the scheduled time on the left, which can have up to two start and end times. I want a "1" to show up in the cells where the .5 hour increment is somewhere between the start and end time(s) for the shift. For some reason, the prior and now defunct formulas work on the time comparisons, but the new formula in Monday (columns AD:BY) do not work. I'm getting a false false and don't understand why.

Any assistance with this is appreciated!

 

[Luke M]

Looks like all your data got converted to text, rather than numbers/times. Thankfully, rather easy to fix. Type the number 1 into cell A1. Select the cell, copy it.
Select your time ranges (B7:AC41)
Do a Paste Special, selecting Values and Multiply. Hit Ok.
Done! All numbers stored as text have been converted, and your formulas work again.

 

[jaynes]

I don't really understand the answer. I have formatted all the times, several times from origination (another worksheet) to this worksheet which was copied as a sample. Yet the same formatting/values work for Tuesday, but not for Monday?

 

[Luke M]

Before:

After doing my technique:

you can now see that times are numbers now.

 

[jaynes]

Okay, I will go back and revisit. I was trying to exclude extraneous worksheets so copying and pasting as values may have caused an issue. The other problem is E7 is calculated correctly, however D7 is not (verified by examining the actual formulas). They are all formatted the same. Again, Tuesday by half hour and day calculates correctly, but Monday by half hour and day does not. They use the same data.