If statement to compare times.

Hi there, I need help with a sheet I've been working on. I need a formula to do the following: if cell j2 falls between j5 and j6, then output h6. if not, then output h3. I've been toying around with nested if statements for 25 minutes now and just can't get it working. Would someone lend a hand? thanks!

Maybe something like this:

Please Login or Register  to view this content.

abousetta

The way your data is set up will never yield the value of H6.

In J2 you have used =NOW() which gives you the actual date and time.You formatted it as time but it is the underlying value that is compared in the IF statement, not the formatted value
This value will always be larger than any time value

If you need the actual time use =MOD(now(),1)

NOW is a volatile function and whenever anything else is calculated in the sheet , even having nothing to do with your formula, NOW will recalculate

Hi all,

I tried a combination of both replies, however, I am unable to get the "10" to return in cell H6 (current time is 11:30 PM):

\1

Any ideas? thank you

As I do not know what you are trying to do, not quite sure.
However there are some strange things in your sheet

C6 is a difference of two moments in time you then use in F6;
C6 is not a number of hours but a day fraction as XL sees it. So if you need the number of hours in decimal you have to multiply by 24

In H6 you have J5-1. Do you want to subtract 1 hour from J5 ? In that case you must subtract 1/24

I amended this ( see blue patterns) but do not know what your formula in H6 is supposed to do, so, no idea if the result is correct

My apologies, let me try again to explain what I'm trying to accomplish. Please see this picture:

\1

Between 10PM and 6AM I have no shifts running so I want my number to be a whole number; like right now it's 3:55 AM Monday and there are 5 * 2 shifts left for the week, so there are 10 shifts remaining, currently H6 is correct with 10 shifts.

However during 1st and 2nd shift hours (6am - 10pm) I want cell H6 to display the remaining shifts as a decimal. For example Monday at 10AM there are 9.5 shifts remaining; I'd like H6 to stay current in decimal format with the remaining shifts.

I have accomplished this with my 3 shift lines on cell H3, it's more difficult with 2 shift lines and this is where I'm stuck...

How do I accomplish this? Thank you.