Another date formula

N

nibbana

Here's my data:

A B
1 1-Jun-08 1-Jul-08
2 30-Jun-08 31-Jul-08

I need a formula in B1 and B2 that will:

If col A date is first day of month then return first day of next month; or
If col A date is last day of month then return last day of next month.

Thanks!
 
M

macropod

Hi nibbana,

For A1/B1:
=DATE(Year(A1),MONTH(A1)+1,1)
For A2/B2:
=DATE(Year(A2),MONTH(A1)+2,0)
 
J

JBeaucaire

In B1 and copied down:

=IF(MONTH((DATE(YEAR(A1),MONTH(A1),DAY(A1))+1))>MONTH(A1),
DATE(YEAR(A1),MONTH(A1)+2,1)-1,
DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)))
 
J

JBeaucaire

We don't have to work so hard on the IF test:

=IF(MONTH(A1+1)>MONTH(A1),
DATE(YEAR(A1),MONTH(A1)+2,1)-1,
DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)))
 
N

nibbana

Turns out I don't need the if scenario. so these simple ones worked great,
thanks.

macropod said:
Hi nibbana,

For A1/B1:
=DATE(Year(A1),MONTH(A1)+1,1)
For A2/B2:
=DATE(Year(A2),MONTH(A1)+2,0)

--
Cheers
macropod
[MVP - Microsoft Word]


nibbana said:
Here's my data:

A B
1 1-Jun-08 1-Jul-08
2 30-Jun-08 31-Jul-08

I need a formula in B1 and B2 that will:

If col A date is first day of month then return first day of next month; or
If col A date is last day of month then return last day of next month.

Thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Conditional lookup 2
index_match 5
Counting the # of values in a range ??? 8
To Calculate the qty b/w two dates 3
Lookup date between 2 dates 1
Help with year to date comparison 4
Date 2
2 criterias in lookup 1

Top