if time is between times

[Sixters]

In cell V3 I have a time validation cell that allows the user to enter end shift time between 18:00 (6:00 pm) all the way to 17:59 (5:59 pm). Beginning before midnight is necessary because my shift is overnight that starts at 18:00. The shift can end anytime there after. Sometime the shift ends before 23:00 the same day and other times the shift will last till 6:00 am. Choosing validation till 17:59 is arbitray (THIS WORKS)

In cell L2 I have a date AND time that is gathered from a different sheet called updated data. This cell shows when the report was last run.
L2 is formatted as( mm/dd/yy hh:mm )

Cell V7 takes the date only from cell L2 using integer and ADDS cell V3. Cell V7 displays END OF SHIFT.
V7 is formated as ( mm/dd/yy hh:mm )

Cell X7 shows hours till end of shift by taking cell V7 - L2. I format this cell to hh:mm.

Everything works fine IF user enters time AFTER midnight in V3.

The only part I am having problem is, is with V7

Here is the formula that WORKS AFTER MIDNIGHT
=IF(L2-INT(L2)>0.75,INT(L2)+1+V3,INT(L2)+V3)

This formula takes in consideration of when the report what last ran (L2) and what time the user enters for end time V3.
I need this to work if V3 is BEFORE midnight AND AFTER midnight.

screen shot is attached.
(screen shot shows Libre Office but at work I use Excell 2013)
There areas in black are blacked out on purpose.

Any help would be greatly appreciated

(sorry thought I could send screen shot from my computer, I'm a noob on this forum)

 

[Andrew Poulsom]

Welcome to MrExcel.

For V7 try:

=INT(L2)+V3+(V3<("18:00"+0))

 

[Sixters]

Welcome to MrExcel.

For V7 try:

=INT(L2)+V3+(V3<("18:00"+0))

I need v7 to add a day if v3 is after midnight and if L2-int(L2) is between .75 and .999 but dont add 1 if v3 is before midnight or if v3 is after midnight and if L2 is after midnight.

My formula works correctly if V3 is after .0000. I can limit V3 data validation between .0000. And .74999 but that's not ideal since end shift (v7) can end between .75 and .9999 on same day as int L2.

Right now data validation for v3 is between .0000 and .99999 which basically allows for anytime.

 

[Andrew Poulsom]

I need v7 to add a day if v3 is after midnight ...

Aren't all times after midnight? The formula I posted adds a day if the time in V3 is less than 18:00 (the start of the shift).

 

[Sixters]

Aren't all times after midnight? The formula I posted adds a day if the time in V3 is less than 18:00 (the start of the shift).

I tested your formula

v7=INT(L2)+V3+(V3<("18:00"+0))

It gave me some wrong data but I ended up fixing that. I posted that formula at the end
wrong info:

---------------------------------
cell l2= 12/4/2015 01:00

cell v3= 02:00

cell v7 reads 12/5/2015 02:00

cell x7 reads 25.00
----------------------------------------------
---------------------------------------------
what it should read:

L2 = 12/4/2015 01:00

V3= 02:00

V7 should read 12/4/2015 02:00

X7 should read 1.00
--------------------------------------

your formula add 1 day even if L2 progresses past midnight. Your formula does work properly if Ls does NOT progress past midnight.

Here is the formula that I used
=IF(L2-INT(L2)>0.75,INT(L2)+V3+(V3<(0.75+0)),INT(L2)+V3+(V3<(0.75+0))-1)

This formula takes what you did but if L2 progresses past midnight then it subtracts 1 so this is what it gives me

Situation 1
--------------------------------------------------
L2= 12/5/2015 01:00

v3 = 02:00

v7=12/5/2015 02:00

x7= 1 hr
---------------------------------------------------

Situation 2
--------------------------------------------------
L2= 12/4/2015 23:00

v3 = 1.0

v7 = 12/5/2015 02:00

x7 = 3.0
-----------------------------------------------------

However that formula above is very confusing. I actually don't fully understand it except that it works.
Maybe you can clean it up for me.

=IF(L2-INT(L2)>0.75,INT(L2)+V3+(V3<(0.75+0)),INT(L2)+V3+(V3<(0.75+0))-1)

 

[Andrew Poulsom]

It looks like you need:

=INT(L2)+V3+(V3<0.75)-((L2-INT(L2))<0.75)

The V3<0.75 returns TRUE if V3 is less than 18:00, otherwise FALSE. When arithmetic is applied (by adding or subtracting) TRUE/FALSE is coerced into 1/0.

 

[Sixters]

Success, i tested all possibilities and they all work.

Thank you, much appreciated.