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
=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 |
#9
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 |