Date Formula
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?
"bj" wrote:
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.
"Karen" wrote:
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
"bj" wrote:
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
"Karen" wrote:
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
"bj" wrote:
I appologize I had missread your earlier submittal
=DATE(YEAR(A1),CEILING(MONTH(A1),3)+1,1)
format B1 as mmm yyyy
"Karen" wrote:
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, lets 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
"bj" wrote:
try
=date(year(A1),month(A1)+3,day(a1))
"Karen" wrote:
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
|