| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Date Formula
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 |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Formula
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 |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Formula
I'll give it a try - Thank you so much for your help.
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 |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Formula
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 |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Formula
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 |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Formula
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 |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Formula
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 |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Formula
Thank you so much BJ - You have helped me tremendously.
Have a great day, 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 |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Formula
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 |
|
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Formula
=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)))MONT H(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 "Karen" wrote in message ... 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 |
|
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Formula
Syntax for Excel functions (such as DATE) can be found in Excel help.
-- David Biddulph "Karen" wrote in message ... 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, 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 "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 |
|
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Formula
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 |
|
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
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 |
|
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Date Formula
Thank you David - You gave me a few good ideas.
Karen "David Biddulph" wrote: =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)))MONT H(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 "Karen" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
| Excel Formula to calulate number of days passed from date to date | Excel Discussion (Misc queries) | |||
| Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
| Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) | |||
| formula to calculate future date from date in cell plus days | Excel Worksheet Functions | |||
Linear Mode
