date function

[date function]

i'm trying to return a specific day of the month by referencing another cell.
for example, cell B3 has the date 4/28/2007. I want cell B4 to return the
15th of the month after, 5/15/2007. what is the formula to do this?

 

[Don Guillett]

try
=DATE(YEAR(H2),MONTH(H2)+1,15)

 

[Elkar]

Try this:

=DATE(YEAR(B3),MONTH(B3)+1,15)

HTH,
Elkar

 

[Peo Sjoblom]

If you always want the next month

=DATE(YEAR(B3),MONTH(B3)+1,15)

however I guess you want the next month when the date is after the 15th of
the current month so if you would have 04/14/07 in B3 that you would want
04/15/07? If that's the case use

=DATE(YEAR(B3),MONTH(B3)+(DAY(B3)>15),15)

 

[Sandy Mann]

If the date in B3 can be before the 15th the try:

=DATE(YEAR(B3),MONTH(B3)+(DAY(B3)>15),15)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
[email protected] with @tiscali.co.uk

 

[PCLIVE]

If you're date is from 4/1/2007 to 4/15/2007 and you want to return
"4/15/2007"...or if it's 4/16/2007 to 5/15/2007 and you want to return
"5/15/2007", then:

=IF(DAY(A1)>15,DATE(YEAR(A1),MONTH(A1)+1,15),DATE(YEAR(A1),MONTH(A1),15))

HTH,
Paul

 

[Sandy Mann]

I meant of course if the date can ALSO be before the 15th as well as after
the 15th.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
[email protected] with @tiscali.co.uk

 

[PCLIVE]

Never mind...I like Sandy Mann's better.

If you're date is from 4/1/2007 to 4/15/2007 and you want to return
"4/15/2007"...or if it's 4/16/2007 to 5/15/2007 and you want to return
"5/15/2007", then:

=IF(DAY(A1)>15,DATE(YEAR(A1),MONTH(A1)+1,15),DATE(YEAR(A1),MONTH(A1),15))

HTH,
Paul