# IF AND statement for analysing a time range

#### dezinsektor

##### New Member
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 time waiting time injection time start end start end start end start end start end start end start end 9:30:00 4:00:00 0:03:30 9:30:00​ 9:33:30​ 13:33:30 13:37:00 17:37:00 17:40:30 21:40:30 21:44:00 1:44:00 1:47:30 5:47:30 5:51:00 9:51:00 9:54:30

The result in V4 should be reset time in G4.

Thank you.

Cheers

#### dezinsektor

##### New Member
@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 time waiting time injection time start end start end start end start end start end start end start end start end buš 1 9:30:00 4:00:00 0:03:30 9:30:00 9:33:30 13:33:30 13:37:00 17:37:00 17:40:30 21:40:30 21:44:00 1:44:00 1:47:30 5:47:30 5:51:00 9:51:00 9:54:30 13:54:30 13:58:00 0.39583 0.16667 0.00243 0.39583 0.39826 0.56493 0.56736 0.73403 0.73646 0.90313 0.90556 1.07222 1.07465 1.24132 1.24375 1.41042 1.41285 1.57951 1.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
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
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
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

Replies
2
Views
145
Replies
1
Views
145
Replies
1
Views
180
Replies
1
Views
95
Replies
5
Views
128

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.

### Which adblocker are you using?

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

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