IF function with date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Christina - 15 Jun 2006 00:06 GMT

This seems really basic, but I looked and looked and couldn't find an
answer....

A B C
Date Wage OT Calc
2/1/2000 $7.50
1/1/2000 $8.00

In column C, I want the calculation to take the wage and double it, if the
date in A1 is on or after 2/1/2000. Thanks in advance!

Reply to this Message

Biff - 15 Jun 2006 03:30 GMT

Hi!

A couple of things:

> 1/1/2000

That's obviously Jan 1 2000.

>if the date in A1 is on or after 2/1/2000

Is that Jan 2 2000 or Feb 1 2000 ?

Also, based on your little table wouldn't that date be in A2?

Try this:

=IF(AND(COUNT(A2:B2)=2,A2>=DATE(2000,?,?)),B2*2,"")

Replace the ?'s with the month and day.

Biff

> This seems really basic, but I looked and looked and couldn't find an
> answer....
[quoted text clipped - 6 lines]
> In column C, I want the calculation to take the wage and double it, if the
> date in A1 is on or after 2/1/2000. Thanks in advance!

Reply to this Message

Christina - 15 Jun 2006 18:23 GMT

Thank you very much. Just a follow up question and some clarification. Yep,
you're right, the first actual date would have been in cell A2. And, the
format I'm using is m/d/yyyy. So, your function works, but I'm a little
confused about why I need the COUNT function? If I just use

=IF(A2>=DATE(2000,2,1)),B2*2,"") rather than
=IF(AND(COUNT(A2:B2)=2,A2>=DATE(2000,?,?)),B2*2,"")

it seems to work. What am I missing? _c

> Hi!
>
[quoted text clipped - 28 lines]
> > In column C, I want the calculation to take the wage and double it, if the
> > date in A1 is on or after 2/1/2000. Thanks in advance!

Reply to this Message

Biff - 15 Jun 2006 19:06 GMT

The Count function is just to make sure that both a true Excel date and a
wage value have been entered. That way, the formula cell doesn't display an
intermediate incorrect result. For example, without the Count function, if
you enter a date that meets the criteria before you enter a wage the formula
would return 0 until you also enter the wage. With the Count function the
formula cell remains blank until both values have been entered.

Biff

> Thank you very much. Just a follow up question and some clarification.
> Yep,
[quoted text clipped - 40 lines]
>> > the
>> > date in A1 is on or after 2/1/2000. Thanks in advance!

Reply to this Message

Christina - 16 Jun 2006 13:06 GMT

You are clearly smarter than I ! Thanks for explaining, I definitely get it
now! _c

> The Count function is just to make sure that both a true Excel date and a
> wage value have been entered. That way, the formula cell doesn't display an
[quoted text clipped - 49 lines]
> >> > the
> >> > date in A1 is on or after 2/1/2000. Thanks in advance!

Reply to this Message

Biff - 16 Jun 2006 19:44 GMT

You're welcome. Thanks for the feedback!

Biff

> You are clearly smarter than I ! Thanks for explaining, I definitely get
> it
[quoted text clipped - 60 lines]
>> >> > the
>> >> > date in A1 is on or after 2/1/2000. Thanks in advance!

Reply to this Message