Excel If function based on a date

  • Thread starter Thread starter chocoearl
  • Start date Start date
C

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
 
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,"")
 
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
 
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,"")
 
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.
Click to expand...
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.
 
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..
Click to expand...
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
 
Back
Top