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
 

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows
I'm not sure what your problem is. Other than changing the semicolons in your formula to commas, I seem to get the G4 value.

(By the way, your data above would have been easier to understand if you would have used the XL2BB table export feature like below)

Book1
GHIJKLMNOPQRSTUVW
3reset timewaiting timeinjection timestartendstartendstartendstartendstartendstartendstartend
49: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:30:009:54:30
Sheet1
Cell Formulas
RangeFormula
V4V4=IF(AND($G$4<(U4+$H$4),(U4+$H$4)<$L$4),$G$4,(U4+$H$4))
 

dezinsektor

New Member
Joined
Sep 30, 2019
Messages
25
thank you, but semicolon is not an issue, this is regional and language setting. It is interesting that works fine on your computer.
?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,924
Office Version
  1. 365
Platform
  1. Windows
Check that all your values are real times & not text.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,924
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Which of those cells do you think is wrong?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
63,924
Office Version
  1. 365
Platform
  1. Windows
Why? U4:H4 is greater than L4
 

shknbk2

Active Member
Joined
Mar 5, 2016
Messages
386
Office Version
  1. 365
Platform
  1. Windows
@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.
 

Forum statistics

Threads
1,144,241
Messages
5,723,198
Members
422,482
Latest member
MacSapper

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