Calculating Difference between to cells 1

[Suggie]

I've had this issue with excel for some time and can't figure it out, any help appreciated.

Issue:

Cell b1 there is a formula =month(a1)
This returns 1 for Jan which is fine and correct

Cell c1, there is a column that has dates in it but is not formatted correctly, so I have a formula that returns the first two digits, =left(f1,2)
This returns 01 for Jan and works and is fine.

The issue is when I put in an If formula into d1, =if(c1>b1,"delete me","")

This formula does not work, it returns "delete me" for everything, which is not correct because some cells are not greater than the previous cell.

I've try copy/paste special values to remove the formulas in the previous cells and this doesn't work.

Cannot it not calculate properly because it the cells have formulas in them????

TIA

 

[dhulbert]

It's not calculating properly beacuse you have different data types in the 2 fields.

You need to convert them both to the same type then do the comparison

=if((TEXT(C1,"00")) > (TEXT(B1,"00")),"delete me","")



I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[GlennUK]

It is comparing text "01" with numberic 1, which is why you are getting the results like that.

Use:
=IF(VALUE(C1)>B1,"delete me","")

which converts the "01" to a numeric value.

You might be better off converting the whole thing to work on true Excel dates, so that you don't have to do weird processing when comparing dates fom different years.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[Suggie]

Thanks folks - Glenn that worked best.

Everytime I use this site I'm always very grateful and learn a lot - this was bugging me for some time. thanks for the help!!

Mark