Excel If function based on a date

[chocoearl]

How do I make an If-Then formula to add the value of two cells only if
another cell contains a date entry?

Earl

 

[Luke M]

Technically, any non-negative number is a form of expressing a date (0 =
1/0/1900, 39862 = 2/14/09)

If you really mean to ask if a cell contains a number, you could do
=IF(ISNUMBER(A1),B1+C1,"")

 

[Pete_UK]

You can't specifically test for a date very easily, as this is just a
number to Excel, but you could do something like this:

=IF(AND(ISNUMBER(D1),D1>35000),A1+B1,"")

where A1 and B1 are the cells you want to add and D1 is the cell that
may have a date in it. The value 35000 elates to 28th October 1995, so
the date would need to be later than that.

Hope this helps.

Pete

 

[Sheeloo]

Excel stores dates and times as a number representing the number of days
since 1900-Jan-0, plus a fractional portion of a 24 hour day: ddddd.tttttt
[see http://www.cpearson.com/excel/datetime.htm#SerialDates for details]

Today's date is stored as 39862

As a result any cell containing a number will pass the test of containing a
date. If you know the possible date range then you can test for the numbers
in that range..

You can do something like
=IF(AND(A1>39000,A1<40000),B1+C1,"")

 

[chocoearl]

OK, thanks. I made this formula and it worked: =(IF(AND(ISNUMBER
(F4),F4>35000),G4+B4,"")-U4) but I would also like it to look at the
date in H4 and add I4 as well to B4. Basically, I want it to add the
values in G4 and H4 to B4 if either has a date >35000.

Earl

You can't specifically test for a date very easily, as this is just a
number to Excel, but you could do something like this:

=IF(AND(ISNUMBER(D1),D1>35000),A1+B1,"")

where A1 and B1 are the cells you want to add and D1 is the cell that
may have a date in it. The value 35000 elates to 28th October 1995, so
the date would need to be later than that.

Hope this helps.

How would I incorporate that in to this formula?

The formula I want to enter in cell V4 (column labeled "New Total") of
my 'Savings Breakdown'! tab would be the equivalent of this:

V4 =If F4 or H4 has a date entered, then SUM(B4+G4+I4), then subtract
the value in cell U4 from that sum, but IF F4 or H4 does not have a
date entered, then value in V4 =B4-U4.

 

[chocoearl]

Excel stores dates and times as a number representing the number of days
since 1900-Jan-0, plus a fractional portion of a 24 hour day:   ddddd.tttttt
[seehttp://www.cpearson.com/excel/datetime.htm#SerialDatesfor details]

Today's date is stored as 39862

As a result any cell containing a number will pass the test of containinga
date. If you know the possible date range then you can test for the numbers
in that range..

What if I needed it to look at the date in two columns and add the
value in the next cell for each one that is true?
Basically V4 =If F4 or H4>35000, then SUM(B4+G4+I4), then subtract the
value in cell U4 from that sum, but IF F4 and H4 <35000, then value in
V4 =B4-U4.

I made one that looks at just one of the date columns and add the $
amount
=(IF(AND(ISNUMBER(F4),F4>35000),G4+B4,"")-U4)

I want it to add the $ amount from two columns if both are true,
either one if only one is true, and neither if both are false.

B4 = Previous Balance
F4 & H4 = Date
G4 & I4 = $ Amount
U4 = Withdraw
V4 = New Total