Date Formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Karen - 16 Oct 2007 21:40 GMT

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/newsgroups/en-us/default.aspx?query=karen&d
g=microsoft.public.excel.worksheet.functions&cat=en_US_e5f758f3-cd79-49bf-8fef-e
528337714eb&lang=en&cr=US&pt=&catlist=&dglist=&ptlist=&exp=&sloc=en-us

Reply to this Message

bj - 16 Oct 2007 22:05 GMT

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.

> 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
[quoted text clipped - 18 lines]
> Original Posting
> http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?query=karen&d
g=microsoft.public.excel.worksheet.functions&cat=en_US_e5f758f3-cd79-49bf-8fef-e
528337714eb&lang=en&cr=US&pt=&catlist=&dglist=&ptlist=&exp=&sloc=en-us

Reply to this Message

Karen - 17 Oct 2007 18:53 GMT

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, I’m 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

I’m 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

> maybe
> =if(month(a1)>5,date(year(A1)+1,6,1),date(year(A1),6,1))
[quoted text clipped - 23 lines]
> > Original Posting
> > http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?query=karen&d
g=microsoft.public.excel.worksheet.functions&cat=en_US_e5f758f3-cd79-49bf-8fef-e
528337714eb&lang=en&cr=US&pt=&catlist=&dglist=&ptlist=&exp=&sloc=en-us

Reply to this Message