Having trouble with =IF formula

CLRead

New Member
Joined
Mar 16, 2018
Messages
7
Hello All,
Would like to see if someone can help me with the following issue:

I am comparing the information typed into cells D61, 62, 63, 64, 65 to the static information in cells C61, 62, 63, 64, 65.

This is how the static information appears in the C cells:
C61 has 2:00
C62 has 3:00
C63 has 4:00
C64 has 8:30
C65 has 10:30

I used =IF(D61<=C61,"YES","NO") in the equivalent E cells and copied it down to E65 so if the manually entered info in the D cells was equal to or less than the C cells I would get YES and if it was greater than I would get a NO.

Works perfectly on all except when comparing D65 to C65. As long as anything between 10:00 and 10:30 is entered I get a YES but if anything else is entered I get a NO even when it is less than 10:30. All info is formatted as text.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

CLRead

New Member
Joined
Mar 16, 2018
Messages
7
Sorry, that last sentence should read I get a NO even when anything less than 10:00 is entered. Again, 10:00 to 10:30 returns YES. From 9:59 back and 10:31 forward returns NO.
 

Marcílio_Lobão

Well-known Member
Joined
Oct 7, 2013
Messages
808
Office Version
  1. 2007
Platform
  1. Windows
CLRead, Good afternoon.

Are you sure that you formulas ask for <= instead = only?

Take a look at your formula again.

I hope it helps.
 

CLRead

New Member
Joined
Mar 16, 2018
Messages
7
CLRead, Good afternoon.

Are you sure that you formulas ask for <= instead = only?

Take a look at your formula again.

I hope it helps.

Yes I believe they do. All five formulas are the same =IF(D""<=C"","YES","NO")

All work as they should except for E65 comparing D65 and C65.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
Hi,

The problem is, you said All info is formatted as Text, comparing Text with the > >= < <= expressions are Not going to work correctly.
 

Marcílio_Lobão

Well-known Member
Joined
Oct 7, 2013
Messages
808
Office Version
  1. 2007
Platform
  1. Windows
CLRead,

"...All info is formatted as text...."
Perhaps the question is due to this fact.

Do all cells in column C and column D have the same format?

Save your spreadsheet template on a free website www.sendspace.com and place the download link here.

So we can check what is happening.

I hope it helps.
 

CLRead

New Member
Joined
Mar 16, 2018
Messages
7
Hi,

The problem is, you said All info is formatted as Text, comparing Text with the > >= < <= expressions are Not going to work correctly.

I'm a little slow with formulas so bear with me.......how does the formula work with the other four cells when all five are all formatted the same?
 

CLRead

New Member
Joined
Mar 16, 2018
Messages
7
CLRead,

"...All info is formatted as text...."
Perhaps the question is due to this fact.

Do all cells in column C and column D have the same format?

Save your spreadsheet template on a free website www.sendspace.com and place the download link here.

So we can check what is happening.

I hope it helps.

I would love to do that but unfortunately the spreadsheet contains company information that is sensitive and can't be shared or altered.
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
7,245
Office Version
  1. 2016
Platform
  1. Windows
I'm a little slow with formulas so bear with me.......how does the formula work with the other four cells when all five are all formatted the same?

Hit and Miss. It didn't actually "Work", it just so happens it yielded the result you wanted by Luck.

Basically, it's like =IF("THIS"<="THAT","YES","NO")
 
Last edited:

CLRead

New Member
Joined
Mar 16, 2018
Messages
7
So what would be the best formula to get the desired results on for all cells?
 

Forum statistics

Threads
1,170,977
Messages
5,873,102
Members
432,961
Latest member
ABG123

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