If statement with date

[Guest]

I'm trying to return new salary information based on a hire date. The
criteria is if you were hired on or before Jan. 1, 2005, you get 3% of the
mid-point of your current salary range. I have the spreadsheet set up as
follows:

A1 B1 C1 D1 E1
Current sal hire date PG mid-point new salary
40,950.00 12/15/03 15 52,460.00 ????

I was using the formula in E1:
=If(B1>1/1/05,(D1*.03)+A1,A1)

But when I entered a hire date that was after 1/1/05, it was still adding
the 3%. What am I doing wrong?

 

[Guest]

Use B1<1/1/05 and it will work
Pat

 

[Guest]

The problem would be the way you are entering the date in the If statement.

Try to type the date in a cell, and then compare among two cells.

=If(B1>$F$1,(D1*.03)+A1,A1)

Where the cell F1 contains the date (1/1/05)

The thing is that Excel interprets 1/1/05 as a series of divisions, which
leads to the result of 1/5 (0.2)

Hope that helps. Please rate this post.

 

[Dave Peterson]

Excel sees 1/1/05 as a couple of divisions. 1 divided by 1 divided by 5.

I like this style to use with dates:

=IF(B1>DATE(2005,1,1),(D1*0.03)+A1,A1)

 

[Ron Rosenfeld]

I was using the formula in E1:
=If(B1>1/1/05,(D1*.03)+A1,A1)

But when I entered a hire date that was after 1/1/05, it was still adding
the 3%. What am I doing wrong?

for 1/1/05 to be interpreted as a date, it must either be enclosed in quote
marks or the result of a function (or cell reference).

=If(B1>"1/1/05",(D1*.03)+A1,A1)

=If(B1>DATE(2005,1,1),(D1*.03)+A1,A1)

=If(B1>$Z$2,(D1*.03)+A1,A1) 'where Z2 contains the date 1/1/05


--ron