round up of dates

  • Thread starter Thread starter kurt
  • Start date Start date
K

kurt

From a given date, I need to calculate 6 months out in the
next cell. I need a conditional formula that will look at
the result and change it to July 1 if the result is
earlier than July 1, or change it to January 1 if the
result is later than July 1. But, also adjusting the year
accordingly. For example: I type in 3/3/2004. I want
the final result cell to read 1/1/2005. If I type in
11/11/2004, the final result cell should read 7/1/2005.

I have tried and if statement, but to no avail.

THANKS!
Kurt
 
First you say if the date is earlier than 7/1, change it to 7/1, if later,
change to 1/1 (presumably of the next year).

But in your examples, a date of March 3 results in a new date of 1/1, rather
than July 1. Which one do you want?

Assuming you want January, with the date in A1, this formula gives the results
you show in your examples, i.e. 3/3/2004 -> 1/1/2005

=DATE(YEAR(A1)+1,IF(MONTH(A1)<7,1,7),1)
 
OTOH, if you want dates earlier than 7/1 to round up to 7/1 of the same year,
and dates 7/1 and after to round up to 1/1 of the next year, this formula does
that:

=DATE(YEAR(A1),IF(MONTH(A1)<7,7,13),1)
 
From a given date, I need to calculate 6 months out in the
next cell. I need a conditional formula that will look at
the result and change it to July 1 if the result is
earlier than July 1, or change it to January 1 if the
result is later than July 1. But, also adjusting the year
accordingly. For example: I type in 3/3/2004. I want
the final result cell to read 1/1/2005. If I type in
11/11/2004, the final result cell should read 7/1/2005.

I have tried and if statement, but to no avail.

THANKS!
Kurt
Click to expand...

Kurt,

If I understand you, you want to first add six months to some date, and then
round that result up to the next January 1 or July 1.

=DATE(YEAR(A1),CEILING(MONTH(A1)+6,6)+1,1)

You do not specify what you want to happen if the result is EQUAL to July 1.
This formula will --> Jan 1 of the following year if the result is EQUAL to
July 1.


--ron
 
Back
Top