Solved: Using months and weeks in Lookup formulae

[Sir Babydum GBE]

Hi again! I thought I'd be able to do this...

Suppose there is a date (dd/mm/yy) in A1 - say March 31, 2009. How do I:
1) Use a formula in B1 to return the third month (with year attached) (in this case, "May 2009" as April, being the second month, is missed)
2) In C1 use an If formula to return a "not due" if we are in April, "Due" if the current date falls within the first 14 days of May and "overdue" if the current date falls on the 15th day or later?

[vba]
Not code, but using VBA tags for formatting reasons

EG1: Today's date is 23/Nov/2009
A1 B1 C1
17/09/2009 Nov 2009 Overdue


EG2: Today's date is 04/Dec/2009
A1 B1 C1
19/10/2009 Dec 2009 Due


EG: Today's date is 20/Jan/2010
A1 B1 C1
30/12/2009 Feb 2010 Not Due
[/vba]
Thanks

Sir BD

[mbarron]

in B1 enter the formula

=DATE(YEAR(A1),MONTH(A1)+2,14)
This will result in the 14th of the month

Apply a custom format to the cell of "mmm yyy" to show just the month and year.

in C1 use the formula of:
=IF(NOW()<=B1,"not due","over due")

[Sir Babydum GBE]

in B1 enter the formula

=DATE(YEAR(A1),MONTH(A1)+2,14)
This will result in the 14th of the month

Apply a custom format to the cell of "mmm yyy" to show just the month and year.

in C1 use the formula of:
=IF(NOW()<=B1,"not due","over due")

Thanks Very Much mbarron

[mbarron]

My second formula does not meet your original request. It doesn't take into account the 14th or earier criteria. I'm looking at it now.

[mbarron]

The formula should be:

=IF(AND(MONTH(NOW())=MONTH(B11),YEAR(NOW())=YEAR(B11),DAY(NOW())<=14),"due",IF(NOW()<=B11,"not due","over due"))

[Sir Babydum GBE]

The formula should be:

=IF(AND(MONTH(NOW())=MONTH(B11),YEAR(NOW())=YEAR(B11),DAY(NOW())<=14),"due",IF(NOW()<=B11,"not due","over due"))

I came back to ask! You'd already answered. Thanks again