Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. Thank you for your help. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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" wrote in message ... 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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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" wrote in message ... 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
Posted to microsoft.public.excel.misc
|
|||
|
|||
End of the 4 quarters
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,"") -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=521401 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. "daddylonglegs" wrote: 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,"") -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=521401 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
End of the 4 quarters
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? -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=521401 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. "daddylonglegs" wrote: 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? -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=521401 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. "daddylonglegs" wrote: 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? -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=521401 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formating Fiscal Quarters, rather than Months | Excel Discussion (Misc queries) | |||
How do I calculate the number of quarters between two date | Excel Discussion (Misc queries) | |||
Show quarters and years on x axis of a chart | Charts and Charting in Excel | |||
How do I Turning Dates into Quarters | Excel Worksheet Functions | |||
Sumproduct, By Quarters and Amount | Excel Worksheet Functions |