IF AND statement for analysing a time range

dezinsektor

New Member
Joined
Sep 30, 2019
Messages
25
Hi, I'm trying to make a formula that will take in account two different times (hh:mm:ss) and if the value is between those times then ignore the result and write desired time called reset time.

this is my formula in cell V4 that does not work:
=IF(AND($G$4<(U4+$H$4);(U4+$H$4)<$L$4);$G$4;(U4+$H$4))

G4 H4 L4 U4 V4
reset timewaiting timeinjection timestartendstartendstartendstartendstartendstartendstartend
9:30:004:00:000:03:30
9:30:00​
9:33:30​
13:33:3013:37:0017:37:0017:40:3021:40:3021:44:001:44:001:47:305:47:305:51:009:51:009:54:30

The result in V4 should be reset time in G4.

Thank you.

Cheers
 

dezinsektor

New Member
Joined
Sep 30, 2019
Messages
25
@dezinsektor
The problem is that you are only seeing the time of day values in the cells. A Date or Time variable is really a number with both the integer and decimal parts. The integer indicates the day and the decimal is the hour. You can see from your data below that while you are only seeing the hours of the day, the hours added together actually spill into the next day. V4 isn't just checking the hour of 9:30 against the hour of 9:51 (U + H). Instead, it is checking the Date variable as a whole.

In the table below, I copied your Time values down a row and formatted them as numbers. You see that U4 isn't just 5:47:30, but it is that time a day later. You can also see that U + H is not less than L, so your If fails.

reset timewaiting timeinjection timestartendstartendstartendstartendstartendstartendstartendstartend
buš 19:30:004:00:000:03:309:30:009:33:3013:33:3013:37:0017:37:0017:40:3021:40:3021:44:001:44:001:47:305:47:305:51:009:51:009:54:3013:54:3013:58:00
0.395830.166670.002430.395830.398260.564930.567360.734030.736460.903130.905561.072221.074651.241321.243751.410421.412851.579511.58194


Try using the Evaluate Formula feature in the Formulas tab. Select V4 and run through the evaluation. You can see where the formula fails the If statement.
Thank you shknbk2, but still problem remains.
Maybe you have idea what formula to use instead this one?
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows
This might work. It uses the TRUNC formula to get the decimal part of the number so that it ignores the date, which leaves just the time.

Excel Formula:
=IF(AND($G$4<(U4-TRUNC(U4)+$H$4),((U4-TRUNC(U4)+$H$4)<$L$4)),$G$4,(U4-TRUNC(U4)+$H$4))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,562
Office Version
  1. 365
Platform
  1. Windows
Another option would be to change your start formula to
Excel Formula:
=IF(AND((K4+$H$4)>=$G$4,(K4+$H$4)<=K4),$G$4,MOD(K4+$H$4,1))
so that it never goes over 1 day
 

dezinsektor

New Member
Joined
Sep 30, 2019
Messages
25
This might work. It uses the TRUNC formula to get the decimal part of the number so that it ignores the date, which leaves just the time.

Excel Formula:
=IF(AND($G$4<(U4-TRUNC(U4)+$H$4),((U4-TRUNC(U4)+$H$4)<$L$4)),$G$4,(U4-TRUNC(U4)+$H$4))
it works, thank you very much!
Cheers!

dezinsektor
 

Forum statistics

Threads
1,141,715
Messages
5,708,038
Members
421,540
Latest member
quocbinh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top