Time, calculcated times and IF statements?

ChetShannon

Board Regular
Joined
Jul 27, 2007
Messages
126
Office Version
  1. 365
Platform
  1. Windows
I have a question about time, calculated time and if statements. I have set A of data that starts with a constant time say 3:00 in the first cell and going downward vertically I add "00:01" to the first cell and this continues going further down in set A getting higher in time in a calculated fashion. I have another set B which is actual constant time values (not a formula) in format hh:mm. My problem / question is when I perform logical IF statements on set A and set B where two cells are visibly equal the IF statement is coming up as false even though both times are viewed as equal.

Why might this be?

Example below:

Set A=== Set B=== Set C===
3:00 A1 a constant 2:00 = B1 100
3:01 A2=A1+"00:01" 2:30 = B2 95
3:02 A3=A2+"00:01" 3:01 = B3 50
3:03 A4=A3+"00:01" 3:09 = B4 45

Then when I logically compare A2 with B3 I get a FALSE. Why is that? It seems that the answer should be TRUE. I am trying to bring the values of C into a target column D (not shown) where the value in set A corresponds to the item in set B. In other words D2 would have a value of 50 because A2 = B3.

Thanks,
Chet
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
Hi Chet

I cannot replicate your problem.

For me
=A2=B3
returns TRUE

Check if B3 is a number or text. Try this
=ISNUMBER(B3)

M.
 

exceldevs

Active Member
Joined
Aug 4, 2013
Messages
255
Hi Chet,

I've simulated successfully your steps without issue, try format column A to hh:mm and press F2 then Enter for A1:A4 (to reflect new formatting).
ABC
103:0002:00FALSE
203:0103:01TRUE
303:0202:30FALSE
403:0303:09FALSE

<tbody>
</tbody>

Spreadsheet Formulas
CellFormula
C1=A1=B1
A2=A1+"00:01"
C2=A2=B2
A3=A2+"00:01"
C3=A3=B3
A4=A3+"00:01"
C4=A4=B4

<tbody>
</tbody>

<tbody>
</tbody>
 

ChetShannon

Board Regular
Joined
Jul 27, 2007
Messages
126
Office Version
  1. 365
Platform
  1. Windows
Hi Marcelo,
I checked both with isnumber and they are both True. The logical IF turns out false though. Any other way you can think to see why it comes out false?
Thanks,
Chet
 

ChetShannon

Board Regular
Joined
Jul 27, 2007
Messages
126
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Hi Exceldevs I tried what you said and am still coming up with the logical comparison false. I'm stumped so far....
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,407
Hi Marcelo,
I checked both with isnumber and they are both True. The logical IF turns out false though. Any other way you can think to see why it comes out false?
Thanks,
Chet

Please check

When you click in
Do you see in Formula Bar?
A1
03:00:00
A2
=A1+"00:01"
B3
03:01:00

<TBODY>
</TBODY>

M.
 

exceldevs

Active Member
Joined
Aug 4, 2013
Messages
255
Hi Exceldevs I tried what you said and am still coming up with the logical comparison false. I'm stumped so far....
Hi Chet,

Maybe start a new spreadsheet and try:
1. format column A, B with hh:mm
2. A1=3:00, A2=A1+"00:01" ...
3. B1=2:00 ...
4. C1=A1=B1 ...

Does it works?
 

Watch MrExcel Video

Forum statistics

Threads
1,122,514
Messages
5,596,610
Members
414,080
Latest member
penguin23

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