# End of the 4 quarters

1. ## End of the 4 quarters

Is there a formula that can match the end of the 4 quarters (March31,June
30,September30, and December 31) to todays date so I can initialize a report
that is due? I'd like to have the number 5 placed in a cell to remind me.

2. ## Re: End of the 4 quarters

=DATE(YEAR(TODAY()),(INT((MONTH(TODAY())-1)/3)+1)*3+1,0)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"ben simpson" <bensimpson@discussions.microsoft.com> wrote in message
news:E4E68170-46C2-408A-B147-79860D92E2F4@microsoft.com...
> Is there a formula that can match the end of the 4 quarters (March31,June
> 30,September30, and December 31) to todays date so I can initialize a

report
> that is due? I'd like to have the number 5 placed in a cell to remind me.
> Thank you for your help.

3. ## Re: End of the 4 quarters

Thanks Bob for looking in. The formula gave me the date of the end of the
current quarter, but how can I get this to put the value of 5 in the cell
when the actual date matches this date (3/31)? Or is there a work around by
placing the formula in a cell then referencing the result to do this?

"Bob Phillips" wrote:

> =DATE(YEAR(TODAY()),(INT((MONTH(TODAY())-1)/3)+1)*3+1,0)
>
> --
>
> HTH
>
> Bob Phillips
>
> (remove nothere from the email address if mailing direct)
>
> "ben simpson" <bensimpson@discussions.microsoft.com> wrote in message
> news:E4E68170-46C2-408A-B147-79860D92E2F4@microsoft.com...
> > Is there a formula that can match the end of the 4 quarters (March31,June
> > 30,September30, and December 31) to todays date so I can initialize a

> report
> > that is due? I'd like to have the number 5 placed in a cell to remind me.
> > Thank you for your help.

>
>
>

4. Do you mean you want a formula to return 5 on the last day of a quarter only? If so try

=IF(DAY(NOW()+1)+MOD(MONTH(NOW()),3)=1,5,"")

5. ## Re: End of the 4 quarters

Thanks daddy. Now what I'm missing is for a way that the 5 will remain there
through the end of the work period, not just there for that 1 day. Its a
period of less than 1 month, but the dates vary.

>
> Do you mean you want a formula to return 5 on the last day of a quarter
> only? If so try
>
> =IF(DAY(NOW()+1)+MOD(MONTH(NOW()),3)=1,5,"")
>
>
> --
> ------------------------------------------------------------------------
>
>

6. So you want 5 to remain until a specific date and then what, return to zero? then become 5 again on the last day of the next quarter?

Can you define the end of the next work period?

7. ## Re: End of the 4 quarters

That is exactly what I,m looking for daddy. The work periods are a rotating
schedule that lasts 27 days. They overlap the end of the quarters, as well
as years. Example: work period 1 this calendar year began on 1/22/2006 and
ran thru 2/17/2006, and so on. This December, work perion 13 begins on
12/12/2006, and ends on 1/7/2007. I think that whomever it was that thought
this schedule up was trying to stick it to those that followed---he he he.
Thanks again.

>
> So you want 5 to remain until a specific date and then what, return to
> zero? then become 5 again on the last day of the next quarter?
>
> Can you define the end of the next work period?
>
>
> --
> ------------------------------------------------------------------------
>
>

8. ## Re: End of the 4 quarters

I forgot...I have the work period hidden in a range on the sheet that can be
easily referenced, also have the TODAY() hidden for as reference (to avoid
those pesky volatile warnings). My brain has died, and I just haven't
realized it yet!!! Thanks.

"ben simpson" wrote:

> That is exactly what I,m looking for daddy. The work periods are a rotating
> schedule that lasts 27 days. They overlap the end of the quarters, as well
> as years. Example: work period 1 this calendar year began on 1/22/2006 and
> ran thru 2/17/2006, and so on. This December, work perion 13 begins on
> 12/12/2006, and ends on 1/7/2007. I think that whomever it was that thought
> this schedule up was trying to stick it to those that followed---he he he.
> Thanks again.
>
>
> >
> > So you want 5 to remain until a specific date and then what, return to
> > zero? then become 5 again on the last day of the next quarter?
> >
> > Can you define the end of the next work period?
> >
> >
> > --
> > ------------------------------------------------------------------------
> >
> >

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1