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

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!

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

"Stacie2410" wrote:

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!

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

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

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

Hi

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


Arvi Laanemets


"Stacie2410" wrote in message
...
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!

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

Maybe this:
=IF(AND(TODAY()A2-C2,B2="mo"),DATE(YEAR(A2),MONTH(A2),1),DATE(YEAR(A 2),MONTH(A2)+1,1))

Does that help?

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Stacie2410" wrote:



"Stacie2410" wrote:

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!

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

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

Rounding date field up to next first of month, with existingformu
 

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

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

On Wed, 13 Jan 2010 20:45:01 -0800, Stacie2410
wrote:

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!

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