|
Date Formula
I posted a question on this board last week and BJ gave me a date formula
that helped me out with a problem I was having. I need to know how to alter the formula for a different scenario. BJ gave me the formula: =DATE(YEAR(A1),CEILING(MONTH(A1),3)+1,1) I need to alter it to suit the following scenario. I thought that I could just change the 3 to a 6 for a six month interval and its not working. HELP 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)? Original Posting http://www.microsoft.com/communities...xp=&sloc=en-us |
Date Formula
maybe
=if(month(a1)5,date(year(A1)+1,6,1),date(year(A1) ,6,1)) You can change the 5 depending on your criteria as to when the next one will be next year. "Karen" wrote: I posted a question on this board last week and BJ gave me a date formula that helped me out with a problem I was having. I need to know how to alter the formula for a different scenario. BJ gave me the formula: =DATE(YEAR(A1),CEILING(MONTH(A1),3)+1,1) I need to alter it to suit the following scenario. I thought that I could just change the 3 to a 6 for a six month interval and its not working. HELP 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)? Original Posting http://www.microsoft.com/communities...xp=&sloc=en-us |
Date Formula
Thank you so much for your help BJ - The formula didn't work. If you are
still willing to help me, I'll try to explain it a different way. For this particular 6 months schedule, Im looking for a formula that will change to the specific months of April or October (whichever is next) whenever maintenance is performed. E.g. ¦ If the A1 date is in any of these months: Oct. 07, Nov. 07, Dec. 07, Jan. 08, Feb. 08, Mar. 08, then the next scheduled maintenance should be Apr. 08 If the A1 date is in any of these months: Apr. 08, May 08, June 08, July 08, Aug. 08, Sept. 08, then the next scheduled maintenance should be Oct. 08 Im also looking for a 12-months formula that will change to a specific/assigned month of the year, no matter when maintenance is performed. E.g., if I want the assigned annual maintenance to be May ¦ If the A1 date is in any of these months: May 07, June 07, July 07, Aug. 07, Sept. 07, Oct. 07, Nov. 07, Dec. 07, Jan. 08, Feb. 08, Mar. 08, Apr. 08, then the next scheduled maintenance should be May 08 If the A1 date is in any of these months: May 08, June 08, July 08, Aug. 08, Sept. 08, Oct. 08, Nov. 08, Dec. 08, Jan. 09, Feb. 09, Mar. 09, Apr. 09, then the next scheduled maintenance should be May 09 Thank you again, Karen "bj" wrote: maybe =if(month(a1)5,date(year(A1)+1,6,1),date(year(A1) ,6,1)) You can change the 5 depending on your criteria as to when the next one will be next year. "Karen" wrote: I posted a question on this board last week and BJ gave me a date formula that helped me out with a problem I was having. I need to know how to alter the formula for a different scenario. BJ gave me the formula: =DATE(YEAR(A1),CEILING(MONTH(A1),3)+1,1) I need to alter it to suit the following scenario. I thought that I could just change the 3 to a 6 for a six month interval and its not working. HELP 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)? Original Posting http://www.microsoft.com/communities...xp=&sloc=en-us |
| All times are GMT +1. The time now is 04:24 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com