Use the built in logic of the formulas
=DATE(YEAR(A1),FLOOR(MONTH(A1)-1,3)+4,1)
=DATE(YEAR(A1),FLOOR(MONTH(A1)-1,3)+7,1)
and so on for second and third
=DATE(YEAR(A1),CEILING(MONTH(A1),3)+4,0)
=DATE(YEAR(A1),CEILING(MONTH(A1),3)+7,0)
and so on
--
Regards,
Peo Sjoblom
Northwest Excel Solutions
www.nwexcelsolutions.com
(remove ^^ from email address)
Portland, Oregon
"Wolfspaw" wrote in message
...
Thank you so much! I was not even aware of the Floor and Ceiling
functions.
May I ask one more favor? I would like to fill the neighboring cells with
the next quarter. Again using the date 2/14/03 and your formulas already
provided, I am looking for the following:
1/1/2003 4/1/2003 7/1/2003 etc.
3/31/2003 6/30/2003 9/30/2003 etc.
Thanks again.
"Bob Phillips" wrote:
Nice use of FLOOR and CEILING but I think the first formula should be
=DATE(YEAR(A1),FLOOR(MONTH(A1)-1,3)+1,1)
--
HTH
Bob Phillips
(remove nothere from email address if mailing direct)
"daddylonglegs"
wrote in message
news:daddylonglegs.244akc_1141428902.6934@excelfor um-nospam.com...
If your date is in A1 this formula gives the first day of the quarter
=DATE(YEAR(A1),FLOOR(MONTH(A1)-1,3),1)
and this the last day
=DATE(YEAR(A1),CEILING(MONTH(A1),3)+1,0)
--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile:
http://www.excelforum.com/member.php...o&userid=30486
View this thread:
http://www.excelforum.com/showthread...hreadid=518912