Excel IF Function Help

December 3, 2010 at 13:07:05
Specs: Windows XP
I'm trying to create and IF function but I'm not sure if this is possible...

At the top of the spreadsheet I will have January 2011, February 2011, etc... going across the top

On the left side of the spreadsheet going down I will have various dates

I want a return of IF the date on the left is 15 days after then this happens

Example:

January 2011 February 2011
12/31/2010 (DUE)

The DUE would show up because 15 days is would occur on January 15th (15 days after 12/31/10)

Make any sense?


See More: Excel IF Function Help

Report •


#1
December 3, 2010 at 13:30:20
Make any sense? No

What is the relationship between all the dates?
Is the 15th of the month special in some way?

Explain what your doing.

MIKE

http://www.skeptic.com/


Report •

#2
December 3, 2010 at 13:37:32
Hard to explain...

The dates are Quarter Ending Dates

15 days after the Quarter Ending Date in special because a Report is due

I'm trying to set up a spreadsheet where if a Quarter Ending date is 12/30/2010 then the January 2011 column will show a value because 15 days after 12/30/2010 falls in January 2011


Report •

#3
December 3, 2010 at 13:48:00
I'm still not getting the whole picture.

I want a return of IF the date on the left is 15 days after then this happens

After what happens?

How and when is the date in column A entered?

When do you want to see the TEXT string "Due" appear?

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
December 3, 2010 at 13:52:12
re: "The dates are Quarter Ending Dates"

Can we assume that there can only be 4 dates in Column A - March 31, June 30, September 30 and December 31 - since those are end dates for "standard" quarters?

Or can any date show up in Column A and you want Due to show up in the column for the month in which the date plus 15 days would occur?

e.g.

With February 2 in Column A , Due would show up in the column for February.
With July 22 in Column A , Due would show up in the column for August.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#5
December 3, 2010 at 13:58:57
Any date can show up in Column A.

Your examples are correct, Derby.


Report •

#6
December 3, 2010 at 14:52:01
Try this:

If your data looks like this:

        A        B              C
	    January 2011   February 2011
12/31/2010      Due	
01/01/2011      Due	
01/19/2011                     Due
02/12/2011                     Due


In the January ( 1st month) Column B enter the formula:

=IF(MONTH(A2+15)=1,"Due","")

In the February ( 2nd month) Column C enter the formula:

=IF(MONTH(A2+15)=2,"Due","")

In the March ( 3nd month) Column D enter the formula:

=IF(MONTH(A2+15)=3,"Due","")

All the way out to December

=IF(MONTH(A2+15)=12,"Due","")

Then just drag the formula down as many rows as needed.

MIKE

http://www.skeptic.com/


Report •

#7
December 4, 2010 at 06:49:08
To make life easier and eliminate typing/pasting, you can try this:

If January is indeed in Column B, instead of this:

=IF(MONTH(A2+15)=1,"Due","")

use this:
=IF(MONTH($A2+15)=COLUMN()-1,"Due","")

Since B is Column 2, COLUMN()-1 will return 1.

Now you can just drag that across until you get to Column 13 (M) which should be December.

Drag it across and then drag them all down as Mike suggested.

Posting Tip: Before posting Data or VBA Code, read this How-To.


Report •

#8
December 4, 2010 at 06:56:11
Thanks guys I'll give this a shot

Report •


Ask Question