Rounding date field up to next first of month, with existing formu

  • Thread starter Thread starter Stacie2410
  • Start date Start date
S

Stacie2410

I have a spreadsheet that has the following 4 columns.

Term End Date Evg Term Days Notice Next Avail Termination Date

The column "Next Avail Termination Date" has a formula that has the current
logic applied:

If Todays Date is greater than the Term End Date minus the Days Notice, and
the Evg Term = MO, then the Next Avail Termination Date = Today plus Days
Notice. This is working fine with this formula:

=IF(AND(TODAY()>A2-C2,B2="mo"),TODAY()+C2)

However, I need to add one more piece of formula, and I can't quite figure
out how. I need it to add logic, that if the date returned from the current
formula above is any day other than the first of the month, that it will
round to the first of the next month. For instance, using the formula above,
it's currently returning the date "02/12/10", and I need it to instead return
"03/01/10".

Any help is greatly appreciated. Thanks!
 
Stacie2410 said:
I have a spreadsheet that has the following 4 columns.

Term End Date Evg Term Days Notice Next Avail Termination Date

The column "Next Avail Termination Date" has a formula that has the current
logic applied:

If Todays Date is greater than the Term End Date minus the Days Notice, and
the Evg Term = MO, then the Next Avail Termination Date = Today plus Days
Notice. This is working fine with this formula:

=IF(AND(TODAY()>A2-C2,B2="mo"),TODAY()+C2)

However, I need to add one more piece of formula, and I can't quite figure
out how. I need it to add logic, that if the date returned from the current
formula above is any day other than the first of the month, that it will
round to the first of the next month. For instance, using the formula above,
it's currently returning the date "02/12/10", and I need it to instead return
"03/01/10".

Any help is greatly appreciated. Thanks!
Click to expand...

My apologies, the columns didn't show up quite right, they are:

Column A: Term End Date
Column B: Evg Term
Column C: Days Notice
Column D: Next Avail Termination Date
 
Hi

=IF(AND(TODAY()>A2-C2,B2="mo"),DATE(YEAR(TODAY()+C2-1),MONTH(TODAY()+C2-1)+1,1))


Arvi Laanemets
 
Maybe this
=IF(AND(TODAY()>A2-C2,B2="mo"),DATE(YEAR(A2),MONTH(A2),1),DATE(YEAR(A2),MONTH(A2)+1,1))

Does that help?
 
Just think what you have to do. One way is . . .

If DAY(MyDate) = 1 then MyDate, else MyDate = DATE(YEAR(MyDate), MONTH
(MyDate) + 1, 1).

But of course December (where one has to increment year) have to be
dealt with.

But EOMONTH() function gives the date of the last day of a month, so
add one to that date.

=IF(DAY(MyDate)=1, MyDate, EOMONTH(MyDate, 1)+1)

MyDate is, of course, the date you have calculated previously.

Look up EOMONTH() in Excel Help. Also look up EDATE() function & other
date functions.

Alan Lloyd
 
I have a spreadsheet that has the following 4 columns.

Term End Date Evg Term Days Notice Next Avail Termination Date

The column "Next Avail Termination Date" has a formula that has the current
logic applied:

If Todays Date is greater than the Term End Date minus the Days Notice, and
the Evg Term = MO, then the Next Avail Termination Date = Today plus Days
Notice. This is working fine with this formula:

=IF(AND(TODAY()>A2-C2,B2="mo"),TODAY()+C2)

However, I need to add one more piece of formula, and I can't quite figure
out how. I need it to add logic, that if the date returned from the current
formula above is any day other than the first of the month, that it will
round to the first of the next month. For instance, using the formula above,
it's currently returning the date "02/12/10", and I need it to instead return
"03/01/10".

Any help is greatly appreciated. Thanks!
Click to expand...

Subtract 1 from the calculated date
Go to end of month
Add 1

So modifying your formula:

=IF(AND(TODAY()>A2-C2,B2="mo"),EOMONTH(TODAY()+C2-1,0)+1)

If you have Excel prior to 2007, you will need to ensure the Analysis ToolPak
is installed. See HELP for the EOMONTH function if this formula returns a
#NAME error.
--ron
 
Back
Top