Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old March 3rd 06, 11:16 PM posted to microsoft.public.excel.worksheet.functions
Wolfspaw
 
Posts: n/a
Default 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   Report Post  
Old March 3rd 06, 11:34 PM posted to microsoft.public.excel.worksheet.functions
daddylonglegs
 
Posts: n/a
Default 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   Report Post  
Old March 3rd 06, 11:41 PM posted to microsoft.public.excel.worksheet.functions
daddylonglegs
 
Posts: n/a
Default 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   Report Post  
Old March 3rd 06, 11:47 PM posted to microsoft.public.excel.worksheet.functions
Bob Phillips
 
Posts: n/a
Default 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:[email protected] 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   Report Post  
Old March 4th 06, 12:40 AM posted to microsoft.public.excel.worksheet.functions
daddylonglegs
 
Posts: n/a
Default 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   Report Post  
Old March 4th 06, 01:19 AM posted to microsoft.public.excel.worksheet.functions
Wolfspaw
 
Posts: n/a
Default 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:[email protected] 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   Report Post  
Old March 4th 06, 02:10 AM posted to microsoft.public.excel.worksheet.functions
Peo Sjoblom
 
Posts: n/a
Default 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:[email protected] 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On



All times are GMT +1. The time now is 01:49 AM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017