Date Formula

[Guest]

How would a formula be written to add a defined number of months but not by
days, like “[date in cell]A1+3 (months)†instead of “[date in cell]A1+90
(days, which is the default way Excel deals with dates)�

I have a spreadsheet that has quarterly maintenance that can be performed
anytime in January, April, July, and October, but it must be performed in
those months specifically. If I set up the formula as “=[date in cell]A1+90â€
for the next quarterly maintenance due, the month will change depending on
how many days in the months of the quarter and when in the month maintenance
was performed.

Cell A1 date entered = 1/1/07
Cell A2 formula is “=A1+90,†Cell A2 value is “Apr-07†… which is correct,
according to the desired schedule. However…

Cell A2 date entered = 1/31/07
Cell A2 formula is “=A1+90,†Cell A2 value is “May-07†… which is incorrect;
maintenance should be performed consistently in April.

Also,
Cell A2 date entered = 4/1/07, Cell A2 formula is “=A1+90,†Cell A2 value is
“Jun-07†… which is incorrect; maintenance should be performed consistently
in July.

I hope that I am explaining myself correctly - ANY help would be greatly
appreciated, Karen

 

[Guest]

try
=date(year(A1),month(A1)+3,day(a1))

 

[Guest]

I'll give it a try - Thank you so much for your help.
Karen

try
=date(year(A1),month(A1)+3,day(a1))

How would a formula be written to add a defined number of months but not by
days, like “[date in cell]A1+3 (months)†instead of “[date in cell]A1+90
(days, which is the default way Excel deals with dates)�

I have a spreadsheet that has quarterly maintenance that can be performed
anytime in January, April, July, and October, but it must be performed in
those months specifically. If I set up the formula as “=[date in cell]A1+90â€
for the next quarterly maintenance due, the month will change depending on
how many days in the months of the quarter and when in the month maintenance
was performed.

Cell A1 date entered = 1/1/07
Cell A2 formula is “=A1+90,†Cell A2 value is “Apr-07†… which is correct,
according to the desired schedule. However…

Cell A2 date entered = 1/31/07
Cell A2 formula is “=A1+90,†Cell A2 value is “May-07†… which is incorrect;
maintenance should be performed consistently in April.

Also,
Cell A2 date entered = 4/1/07, Cell A2 formula is “=A1+90,†Cell A2 value is
“Jun-07†… which is incorrect; maintenance should be performed consistently
in July.

I hope that I am explaining myself correctly - ANY help would be greatly
appreciated, Karen

 

[Guest]

I used your formula and I have a question. I may not have explained it
fully. Let me reword it.

The quarterly maintenance has to be performed anytime in January, April,
July, and October, but it must be performed in those months specifically.

So, let’s just say that below are the different scenarios – If cell A1
(which is date performed and the formula (cell B1) would be the date the
quarterly maintenance is due)

= 1/1/yy thru 3/31/yy, then return Apr yyyy
= 4/1/yy thru 6/30/yy, then return Jul yyyy
= 7/1/yy thru 9/30/yy, then return Oct yyyy
= 10/1/yy thru 12/31/yy, then return Jan yyyy (this would have to be plus
one year)

When I use the formula you gave me, this is what happens:
When I enter 1/1/07 in cell A1, it returns Apr 2007 – Which is correct
If I enter 1/31/07 in cell A1, it returns May 2007 – Which is incorrect –
Since the maintenance should be due in Apr 2007.

ANY help would be greatly appreciated, Karen

try
=date(year(A1),month(A1)+3,day(a1))

How would a formula be written to add a defined number of months but not by
days, like “[date in cell]A1+3 (months)†instead of “[date in cell]A1+90
(days, which is the default way Excel deals with dates)�

I have a spreadsheet that has quarterly maintenance that can be performed
anytime in January, April, July, and October, but it must be performed in
those months specifically. If I set up the formula as “=[date in cell]A1+90â€
for the next quarterly maintenance due, the month will change depending on
how many days in the months of the quarter and when in the month maintenance
was performed.

Cell A1 date entered = 1/1/07
Cell A2 formula is “=A1+90,†Cell A2 value is “Apr-07†… which is correct,
according to the desired schedule. However…

Cell A2 date entered = 1/31/07
Cell A2 formula is “=A1+90,†Cell A2 value is “May-07†… which is incorrect;
maintenance should be performed consistently in April.

Also,
Cell A2 date entered = 4/1/07, Cell A2 formula is “=A1+90,†Cell A2 value is
“Jun-07†… which is incorrect; maintenance should be performed consistently
in July.

I hope that I am explaining myself correctly - ANY help would be greatly
appreciated, Karen

 

[Guest]

I appologize I had missread your earlier submittal
=DATE(YEAR(A1),CEILING(MONTH(A1),3)+1,1)
format B1 as mmm yyyy

I used your formula and I have a question. I may not have explained it
fully. Let me reword it.

The quarterly maintenance has to be performed anytime in January, April,
July, and October, but it must be performed in those months specifically.

So, let’s just say that below are the different scenarios – If cell A1
(which is date performed and the formula (cell B1) would be the date the
quarterly maintenance is due)

= 1/1/yy thru 3/31/yy, then return Apr yyyy
= 4/1/yy thru 6/30/yy, then return Jul yyyy
= 7/1/yy thru 9/30/yy, then return Oct yyyy
= 10/1/yy thru 12/31/yy, then return Jan yyyy (this would have to be plus
one year)

When I use the formula you gave me, this is what happens:
When I enter 1/1/07 in cell A1, it returns Apr 2007 – Which is correct
If I enter 1/31/07 in cell A1, it returns May 2007 – Which is incorrect –
Since the maintenance should be due in Apr 2007.

ANY help would be greatly appreciated, Karen

try
=date(year(A1),month(A1)+3,day(a1))

How would a formula be written to add a defined number of months but not by
days, like “[date in cell]A1+3 (months)†instead of “[date in cell]A1+90
(days, which is the default way Excel deals with dates)�

I have a spreadsheet that has quarterly maintenance that can be performed
anytime in January, April, July, and October, but it must be performed in
those months specifically. If I set up the formula as “=[date in cell]A1+90â€
for the next quarterly maintenance due, the month will change depending on
how many days in the months of the quarter and when in the month maintenance
was performed.

Cell A1 date entered = 1/1/07
Cell A2 formula is “=A1+90,†Cell A2 value is “Apr-07†… which is correct,
according to the desired schedule. However…

Cell A2 date entered = 1/31/07
Cell A2 formula is “=A1+90,†Cell A2 value is “May-07†… which is incorrect;
maintenance should be performed consistently in April.

Also,
Cell A2 date entered = 4/1/07, Cell A2 formula is “=A1+90,†Cell A2 value is
“Jun-07†… which is incorrect; maintenance should be performed consistently
in July.

I hope that I am explaining myself correctly - ANY help would be greatly
appreciated, Karen

 

[Guest]

Thank you so much - You have been such a HUGE help. If it's not asking too
much, would you please break the formula down into laymen's terms? I looked
up the function CEILING and noticed that that functions rounds the date. Is
that correct?

Thanks again, Karen

I appologize I had missread your earlier submittal
=DATE(YEAR(A1),CEILING(MONTH(A1),3)+1,1)
format B1 as mmm yyyy

I used your formula and I have a question. I may not have explained it
fully. Let me reword it.

The quarterly maintenance has to be performed anytime in January, April,
July, and October, but it must be performed in those months specifically.

So, let’s just say that below are the different scenarios – If cell A1
(which is date performed and the formula (cell B1) would be the date the
quarterly maintenance is due)

= 1/1/yy thru 3/31/yy, then return Apr yyyy
= 4/1/yy thru 6/30/yy, then return Jul yyyy
= 7/1/yy thru 9/30/yy, then return Oct yyyy
= 10/1/yy thru 12/31/yy, then return Jan yyyy (this would have to be plus
one year)

When I use the formula you gave me, this is what happens:
When I enter 1/1/07 in cell A1, it returns Apr 2007 – Which is correct
If I enter 1/31/07 in cell A1, it returns May 2007 – Which is incorrect –
Since the maintenance should be due in Apr 2007.

ANY help would be greatly appreciated, Karen

try
=date(year(A1),month(A1)+3,day(a1))

:

How would a formula be written to add a defined number of months but not by
days, like “[date in cell]A1+3 (months)†instead of “[date in cell]A1+90
(days, which is the default way Excel deals with dates)�

I have a spreadsheet that has quarterly maintenance that can be performed
anytime in January, April, July, and October, but it must be performed in
those months specifically. If I set up the formula as “=[date in cell]A1+90â€
for the next quarterly maintenance due, the month will change depending on
how many days in the months of the quarter and when in the month maintenance
was performed.

Cell A1 date entered = 1/1/07
Cell A2 formula is “=A1+90,†Cell A2 value is “Apr-07†… which is correct,
according to the desired schedule. However…

Cell A2 date entered = 1/31/07
Cell A2 formula is “=A1+90,†Cell A2 value is “May-07†… which is incorrect;
maintenance should be performed consistently in April.

Also,
Cell A2 date entered = 4/1/07, Cell A2 formula is “=A1+90,†Cell A2 value is
“Jun-07†… which is incorrect; maintenance should be performed consistently
in July.

I hope that I am explaining myself correctly - ANY help would be greatly
appreciated, Karen

 

[Guest]

ceiling rounds the data "up" to a multiple of the second factor for example
jan month (1) would be rounded up to 3
I originally used floor() which rounds down and added 4 rather than 1 in the
month section of the date function but changed my mind
the date function is another very useful tool
as in date(year,month,day) gives the serial number date and excel
automatically, Mostly anyway, formats the cell as a date

Thank you so much - You have been such a HUGE help. If it's not asking too
much, would you please break the formula down into laymen's terms? I looked
up the function CEILING and noticed that that functions rounds the date. Is
that correct?

Thanks again, Karen

I appologize I had missread your earlier submittal
=DATE(YEAR(A1),CEILING(MONTH(A1),3)+1,1)
format B1 as mmm yyyy

I used your formula and I have a question. I may not have explained it
fully. Let me reword it.

The quarterly maintenance has to be performed anytime in January, April,
July, and October, but it must be performed in those months specifically.

So, let’s just say that below are the different scenarios – If cell A1
(which is date performed and the formula (cell B1) would be the date the
quarterly maintenance is due)

= 1/1/yy thru 3/31/yy, then return Apr yyyy
= 4/1/yy thru 6/30/yy, then return Jul yyyy
= 7/1/yy thru 9/30/yy, then return Oct yyyy
= 10/1/yy thru 12/31/yy, then return Jan yyyy (this would have to be plus
one year)

When I use the formula you gave me, this is what happens:
When I enter 1/1/07 in cell A1, it returns Apr 2007 – Which is correct
If I enter 1/31/07 in cell A1, it returns May 2007 – Which is incorrect –
Since the maintenance should be due in Apr 2007.

ANY help would be greatly appreciated, Karen


:

try
=date(year(A1),month(A1)+3,day(a1))

:

How would a formula be written to add a defined number of months but not by
days, like “[date in cell]A1+3 (months)†instead of “[date in cell]A1+90
(days, which is the default way Excel deals with dates)�

I have a spreadsheet that has quarterly maintenance that can be performed
anytime in January, April, July, and October, but it must be performed in
those months specifically. If I set up the formula as “=[date in cell]A1+90â€
for the next quarterly maintenance due, the month will change depending on
how many days in the months of the quarter and when in the month maintenance
was performed.

Cell A1 date entered = 1/1/07
Cell A2 formula is “=A1+90,†Cell A2 value is “Apr-07†… which is correct,
according to the desired schedule. However…

Cell A2 date entered = 1/31/07
Cell A2 formula is “=A1+90,†Cell A2 value is “May-07†… which is incorrect;
maintenance should be performed consistently in April.

Also,
Cell A2 date entered = 4/1/07, Cell A2 formula is “=A1+90,†Cell A2 value is
“Jun-07†… which is incorrect; maintenance should be performed consistently
in July.

I hope that I am explaining myself correctly - ANY help would be greatly
appreciated, Karen

 

[Guest]

Thank you so much BJ - You have helped me tremendously.
Have a great day, Karen

ceiling rounds the data "up" to a multiple of the second factor for example
jan month (1) would be rounded up to 3
I originally used floor() which rounds down and added 4 rather than 1 in the
month section of the date function but changed my mind
the date function is another very useful tool
as in date(year,month,day) gives the serial number date and excel
automatically, Mostly anyway, formats the cell as a date

Thank you so much - You have been such a HUGE help. If it's not asking too
much, would you please break the formula down into laymen's terms? I looked
up the function CEILING and noticed that that functions rounds the date. Is
that correct?

Thanks again, Karen

I appologize I had missread your earlier submittal
=DATE(YEAR(A1),CEILING(MONTH(A1),3)+1,1)
format B1 as mmm yyyy

:

I used your formula and I have a question. I may not have explained it
fully. Let me reword it.

The quarterly maintenance has to be performed anytime in January, April,
July, and October, but it must be performed in those months specifically.

So, let’s just say that below are the different scenarios – If cell A1
(which is date performed and the formula (cell B1) would be the date the
quarterly maintenance is due)

= 1/1/yy thru 3/31/yy, then return Apr yyyy
= 4/1/yy thru 6/30/yy, then return Jul yyyy
= 7/1/yy thru 9/30/yy, then return Oct yyyy
= 10/1/yy thru 12/31/yy, then return Jan yyyy (this would have to be plus
one year)

When I use the formula you gave me, this is what happens:
When I enter 1/1/07 in cell A1, it returns Apr 2007 – Which is correct
If I enter 1/31/07 in cell A1, it returns May 2007 – Which is incorrect –
Since the maintenance should be due in Apr 2007.

ANY help would be greatly appreciated, Karen


:

try
=date(year(A1),month(A1)+3,day(a1))

:

How would a formula be written to add a defined number of months but not by
days, like “[date in cell]A1+3 (months)†instead of “[date in cell]A1+90
(days, which is the default way Excel deals with dates)�

I have a spreadsheet that has quarterly maintenance that can be performed
anytime in January, April, July, and October, but it must be performed in
those months specifically. If I set up the formula as “=[date in cell]A1+90â€
for the next quarterly maintenance due, the month will change depending on
how many days in the months of the quarter and when in the month maintenance
was performed.

Cell A1 date entered = 1/1/07
Cell A2 formula is “=A1+90,†Cell A2 value is “Apr-07†… which is correct,
according to the desired schedule. However…

Cell A2 date entered = 1/31/07
Cell A2 formula is “=A1+90,†Cell A2 value is “May-07†… which is incorrect;
maintenance should be performed consistently in April.

Also,
Cell A2 date entered = 4/1/07, Cell A2 formula is “=A1+90,†Cell A2 value is
“Jun-07†… which is incorrect; maintenance should be performed consistently
in July.

I hope that I am explaining myself correctly - ANY help would be greatly
appreciated, Karen

 

[Guest]

Thanks again - What does the +1 and the 1 dsignate on the end of the formula?

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

Thanks, Karen

ceiling rounds the data "up" to a multiple of the second factor for example
jan month (1) would be rounded up to 3
I originally used floor() which rounds down and added 4 rather than 1 in the
month section of the date function but changed my mind
the date function is another very useful tool
as in date(year,month,day) gives the serial number date and excel
automatically, Mostly anyway, formats the cell as a date

Thank you so much - You have been such a HUGE help. If it's not asking too
much, would you please break the formula down into laymen's terms? I looked
up the function CEILING and noticed that that functions rounds the date. Is
that correct?

Thanks again, Karen

I appologize I had missread your earlier submittal
=DATE(YEAR(A1),CEILING(MONTH(A1),3)+1,1)
format B1 as mmm yyyy

:

I used your formula and I have a question. I may not have explained it
fully. Let me reword it.

The quarterly maintenance has to be performed anytime in January, April,
July, and October, but it must be performed in those months specifically.

So, let’s just say that below are the different scenarios – If cell A1
(which is date performed and the formula (cell B1) would be the date the
quarterly maintenance is due)

= 1/1/yy thru 3/31/yy, then return Apr yyyy
= 4/1/yy thru 6/30/yy, then return Jul yyyy
= 7/1/yy thru 9/30/yy, then return Oct yyyy
= 10/1/yy thru 12/31/yy, then return Jan yyyy (this would have to be plus
one year)

When I use the formula you gave me, this is what happens:
When I enter 1/1/07 in cell A1, it returns Apr 2007 – Which is correct
If I enter 1/31/07 in cell A1, it returns May 2007 – Which is incorrect –
Since the maintenance should be due in Apr 2007.

ANY help would be greatly appreciated, Karen


:

try
=date(year(A1),month(A1)+3,day(a1))

:

How would a formula be written to add a defined number of months but not by
days, like “[date in cell]A1+3 (months)†instead of “[date in cell]A1+90
(days, which is the default way Excel deals with dates)�

I have a spreadsheet that has quarterly maintenance that can be performed
anytime in January, April, July, and October, but it must be performed in
those months specifically. If I set up the formula as “=[date in cell]A1+90â€
for the next quarterly maintenance due, the month will change depending on
how many days in the months of the quarter and when in the month maintenance
was performed.

Cell A1 date entered = 1/1/07
Cell A2 formula is “=A1+90,†Cell A2 value is “Apr-07†… which is correct,
according to the desired schedule. However…

Cell A2 date entered = 1/31/07
Cell A2 formula is “=A1+90,†Cell A2 value is “May-07†… which is incorrect;
maintenance should be performed consistently in April.

Also,
Cell A2 date entered = 4/1/07, Cell A2 formula is “=A1+90,†Cell A2 value is
“Jun-07†… which is incorrect; maintenance should be performed consistently
in July.

I hope that I am explaining myself correctly - ANY help would be greatly
appreciated, Karen

 

[David Biddulph]

=DATE(YEAR(A1),MONTH(A1)+3,DAY(A1)) is one option for a simple 3-month
adavance, but you'll need to ask yourself the question as to what date you
want for 3 months after 30th November, for example. By the look of your
examples, you want a different answer from Excel's, and you'll see a number
of suggestions for alternative approaches in recent messages on this group
and related ones.

=IF(MONTH(DATE(YEAR(A4),MONTH(A4)+3,DAY(A4)))>MONTH(A4)+3,DATE(YEAR(A4),MONTH(A4)+4,0),DATE(YEAR(A4),MONTH(A4)+3,DAY(A4)))
may be an option, but there are certain to be other approaches.

You haven't said how you would treat a situation where your starting point
doesn't fit in your maintenance schedule, for example if A1 were
mid-February, but perhaps you've got Data Validation on your data entry to
prevent this?

 

[David Biddulph]

Syntax for Excel functions (such as DATE) can be found in Excel help.
--
David Biddulph

Thanks again - What does the +1 and the 1 dsignate on the end of the
formula?

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

Thanks, Karen

ceiling rounds the data "up" to a multiple of the second factor for
example
jan month (1) would be rounded up to 3
I originally used floor() which rounds down and added 4 rather than 1 in
the
month section of the date function but changed my mind
the date function is another very useful tool
as in date(year,month,day) gives the serial number date and excel
automatically, Mostly anyway, formats the cell as a date

Thank you so much - You have been such a HUGE help. If it's not asking
too
much, would you please break the formula down into laymen's terms? I
looked
up the function CEILING and noticed that that functions rounds the
date. Is
that correct?

Thanks again, Karen

:

I appologize I had missread your earlier submittal
=DATE(YEAR(A1),CEILING(MONTH(A1),3)+1,1)
format B1 as mmm yyyy

:

I used your formula and I have a question. I may not have
explained it
fully. Let me reword it.

The quarterly maintenance has to be performed anytime in January,
April,
July, and October, but it must be performed in those months
specifically.

So, let's just say that below are the different scenarios - If cell
A1
(which is date performed and the formula (cell B1) would be the
date the
quarterly maintenance is due)

= 1/1/yy thru 3/31/yy, then return Apr yyyy
= 4/1/yy thru 6/30/yy, then return Jul yyyy
= 7/1/yy thru 9/30/yy, then return Oct yyyy
= 10/1/yy thru 12/31/yy, then return Jan yyyy (this would have to
be plus
one year)

When I use the formula you gave me, this is what happens:
When I enter 1/1/07 in cell A1, it returns Apr 2007 - Which is
correct
If I enter 1/31/07 in cell A1, it returns May 2007 - Which is
incorrect -
Since the maintenance should be due in Apr 2007.

ANY help would be greatly appreciated, Karen


:

try
=date(year(A1),month(A1)+3,day(a1))

:

How would a formula be written to add a defined number of
months but not by
days, like "[date in cell]A1+3 (months)" instead of "[date in
cell]A1+90
(days, which is the default way Excel deals with dates)"?

I have a spreadsheet that has quarterly maintenance that can be
performed
anytime in January, April, July, and October, but it must be
performed in
those months specifically. If I set up the formula as "=[date
in cell]A1+90"
for the next quarterly maintenance due, the month will change
depending on
how many days in the months of the quarter and when in the
month maintenance
was performed.

Cell A1 date entered = 1/1/07
Cell A2 formula is "=A1+90," Cell A2 value is "Apr-07" . which
is correct,
according to the desired schedule. However.

Cell A2 date entered = 1/31/07
Cell A2 formula is "=A1+90," Cell A2 value is "May-07" . which
is incorrect;
maintenance should be performed consistently in April.

Also,
Cell A2 date entered = 4/1/07, Cell A2 formula is "=A1+90,"
Cell A2 value is
"Jun-07" . which is incorrect; maintenance should be performed
consistently
in July.

I hope that I am explaining myself correctly - ANY help would
be greatly
appreciated, Karen

 

[Guest]

the "+1" adds one to the month found by the ceiling function
in words ceiling (feb,3) is 3~Mar Ceiling(feb,3)+1 is 4~Apr

the final 1 just indicates the first day of the month.
a 0 would have showed up as the last day of the previous month.

Thanks again - What does the +1 and the 1 dsignate on the end of the formula?

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

Thanks, Karen

ceiling rounds the data "up" to a multiple of the second factor for example
jan month (1) would be rounded up to 3
I originally used floor() which rounds down and added 4 rather than 1 in the
month section of the date function but changed my mind
the date function is another very useful tool
as in date(year,month,day) gives the serial number date and excel
automatically, Mostly anyway, formats the cell as a date

Thank you so much - You have been such a HUGE help. If it's not asking too
much, would you please break the formula down into laymen's terms? I looked
up the function CEILING and noticed that that functions rounds the date. Is
that correct?

Thanks again, Karen

:

I appologize I had missread your earlier submittal
=DATE(YEAR(A1),CEILING(MONTH(A1),3)+1,1)
format B1 as mmm yyyy

:

I used your formula and I have a question. I may not have explained it
fully. Let me reword it.

The quarterly maintenance has to be performed anytime in January, April,
July, and October, but it must be performed in those months specifically.

So, let’s just say that below are the different scenarios – If cell A1
(which is date performed and the formula (cell B1) would be the date the
quarterly maintenance is due)

= 1/1/yy thru 3/31/yy, then return Apr yyyy
= 4/1/yy thru 6/30/yy, then return Jul yyyy
= 7/1/yy thru 9/30/yy, then return Oct yyyy
= 10/1/yy thru 12/31/yy, then return Jan yyyy (this would have to be plus
one year)

When I use the formula you gave me, this is what happens:
When I enter 1/1/07 in cell A1, it returns Apr 2007 – Which is correct
If I enter 1/31/07 in cell A1, it returns May 2007 – Which is incorrect –
Since the maintenance should be due in Apr 2007.

ANY help would be greatly appreciated, Karen


:

try
=date(year(A1),month(A1)+3,day(a1))

:

How would a formula be written to add a defined number of months but not by
days, like “[date in cell]A1+3 (months)†instead of “[date in cell]A1+90
(days, which is the default way Excel deals with dates)�

I have a spreadsheet that has quarterly maintenance that can be performed
anytime in January, April, July, and October, but it must be performed in
those months specifically. If I set up the formula as “=[date in cell]A1+90â€
for the next quarterly maintenance due, the month will change depending on
how many days in the months of the quarter and when in the month maintenance
was performed.

Cell A1 date entered = 1/1/07
Cell A2 formula is “=A1+90,†Cell A2 value is “Apr-07†… which is correct,
according to the desired schedule. However…

Cell A2 date entered = 1/31/07
Cell A2 formula is “=A1+90,†Cell A2 value is “May-07†… which is incorrect;
maintenance should be performed consistently in April.

Also,
Cell A2 date entered = 4/1/07, Cell A2 formula is “=A1+90,†Cell A2 value is
“Jun-07†… which is incorrect; maintenance should be performed consistently
in July.

I hope that I am explaining myself correctly - ANY help would be greatly
appreciated, Karen

 

[Guest]

Thank you again for all your help. I have another question. The formula
works great - I want to alter it for another scenario.

I can't seem to figure out how to set up the cell formulas when I want other
maintenance intervals to be scheduled in particular months, no matter when
the previous maintenance was performed. For instance, annual maintenance
should occur once a year (every 12 months, ideally); and it also should stay
consistent. So, for example, how do I go about writing the formula if I want
to ensure that the “Next Annual Due" date is always in the month of June,
even if the previous maintenance was performed very late (say, January of the
same year)?

ANY help would be greatly appreciated, Karen

Also, how do I go about writing the formula for semi-annual maintenance to
always be scheduled in April or October, whichever is next?

the "+1" adds one to the month found by the ceiling function
in words ceiling (feb,3) is 3~Mar Ceiling(feb,3)+1 is 4~Apr

the final 1 just indicates the first day of the month.
a 0 would have showed up as the last day of the previous month.

Thanks again - What does the +1 and the 1 dsignate on the end of the formula?

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

Thanks, Karen

ceiling rounds the data "up" to a multiple of the second factor for example
jan month (1) would be rounded up to 3
I originally used floor() which rounds down and added 4 rather than 1 in the
month section of the date function but changed my mind
the date function is another very useful tool
as in date(year,month,day) gives the serial number date and excel
automatically, Mostly anyway, formats the cell as a date

:

Thank you so much - You have been such a HUGE help. If it's not asking too
much, would you please break the formula down into laymen's terms? I looked
up the function CEILING and noticed that that functions rounds the date. Is
that correct?

Thanks again, Karen

:

I appologize I had missread your earlier submittal
=DATE(YEAR(A1),CEILING(MONTH(A1),3)+1,1)
format B1 as mmm yyyy

:

I used your formula and I have a question. I may not have explained it
fully. Let me reword it.

The quarterly maintenance has to be performed anytime in January, April,
July, and October, but it must be performed in those months specifically.

So, let’s just say that below are the different scenarios – If cell A1
(which is date performed and the formula (cell B1) would be the date the
quarterly maintenance is due)

= 1/1/yy thru 3/31/yy, then return Apr yyyy
= 4/1/yy thru 6/30/yy, then return Jul yyyy
= 7/1/yy thru 9/30/yy, then return Oct yyyy
= 10/1/yy thru 12/31/yy, then return Jan yyyy (this would have to be plus
one year)

When I use the formula you gave me, this is what happens:
When I enter 1/1/07 in cell A1, it returns Apr 2007 – Which is correct
If I enter 1/31/07 in cell A1, it returns May 2007 – Which is incorrect –
Since the maintenance should be due in Apr 2007.

ANY help would be greatly appreciated, Karen


:

try
=date(year(A1),month(A1)+3,day(a1))

:

How would a formula be written to add a defined number of months but not by
days, like “[date in cell]A1+3 (months)†instead of “[date in cell]A1+90
(days, which is the default way Excel deals with dates)�

I have a spreadsheet that has quarterly maintenance that can be performed
anytime in January, April, July, and October, but it must be performed in
those months specifically. If I set up the formula as “=[date in cell]A1+90â€
for the next quarterly maintenance due, the month will change depending on
how many days in the months of the quarter and when in the month maintenance
was performed.

Cell A1 date entered = 1/1/07
Cell A2 formula is “=A1+90,†Cell A2 value is “Apr-07†… which is correct,
according to the desired schedule. However…

Cell A2 date entered = 1/31/07
Cell A2 formula is “=A1+90,†Cell A2 value is “May-07†… which is incorrect;
maintenance should be performed consistently in April.

Also,
Cell A2 date entered = 4/1/07, Cell A2 formula is “=A1+90,†Cell A2 value is
“Jun-07†… which is incorrect; maintenance should be performed consistently
in July.

I hope that I am explaining myself correctly - ANY help would be greatly
appreciated, Karen

 

[Guest]

Thank you David - You gave me a few good ideas.
Karen

=DATE(YEAR(A1),MONTH(A1)+3,DAY(A1)) is one option for a simple 3-month
adavance, but you'll need to ask yourself the question as to what date you
want for 3 months after 30th November, for example. By the look of your
examples, you want a different answer from Excel's, and you'll see a number
of suggestions for alternative approaches in recent messages on this group
and related ones.

=IF(MONTH(DATE(YEAR(A4),MONTH(A4)+3,DAY(A4)))>MONTH(A4)+3,DATE(YEAR(A4),MONTH(A4)+4,0),DATE(YEAR(A4),MONTH(A4)+3,DAY(A4)))
may be an option, but there are certain to be other approaches.

You haven't said how you would treat a situation where your starting point
doesn't fit in your maintenance schedule, for example if A1 were
mid-February, but perhaps you've got Data Validation on your data entry to
prevent this?
--
David Biddulph

How would a formula be written to add a defined number of months but not
by
days, like "[date in cell]A1+3 (months)" instead of "[date in cell]A1+90
(days, which is the default way Excel deals with dates)"?

I have a spreadsheet that has quarterly maintenance that can be performed
anytime in January, April, July, and October, but it must be performed in
those months specifically. If I set up the formula as "=[date in
cell]A1+90"
for the next quarterly maintenance due, the month will change depending on
how many days in the months of the quarter and when in the month
maintenance
was performed.

Cell A1 date entered = 1/1/07
Cell A2 formula is "=A1+90," Cell A2 value is "Apr-07" . which is
correct,
according to the desired schedule. However.

Cell A2 date entered = 1/31/07
Cell A2 formula is "=A1+90," Cell A2 value is "May-07" . which is
incorrect;
maintenance should be performed consistently in April.

Also,
Cell A2 date entered = 4/1/07, Cell A2 formula is "=A1+90," Cell A2 value
is
"Jun-07" . which is incorrect; maintenance should be performed
consistently
in July.

I hope that I am explaining myself correctly - ANY help would be greatly
appreciated, Karen