Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First and Last Day of the Quarter
I am looking for two formulas that would return the first and last day of the
quarter based on any given date. Assuming the given date is 2/14/03, I would like to return the following in separate cells: 1/1/2003 3/31/2003 Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First and Last Day of the Quarter
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First and Last Day of the Quarter
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First and Last Day of the Quarter
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First and Last Day of the Quarter
Thanks Bob. You are, of course, correct, nice catch :) -- daddylonglegs ------------------------------------------------------------------------ daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486 View this thread: http://www.excelforum.com/showthread...hreadid=518912 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First and Last Day of the Quarter
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
First and Last Day of the Quarter
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|