Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1
Default Excel should have a QUARTER formula for DATE calculations

Excel should have a built in QUARTER function which allows users to calculate
the QUARTER in which a transaction occurs based on the date of the
transaction.

Ex: =QUARTER(J2) where cell J2 holds a date.

You have it for MONTH, DAY, and YEAR why not for QUARTER for those of us in
the financial world?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JMB JMB is offline
external usenet poster
 
Posts: 2,062
Default Excel should have a QUARTER formula for DATE calculations

But, fiscal years don't have to have the same start/end dates as calendar
years. For example, the U.S. Federal Fiscal Year is 10/1 - 9/30. In some
cases, an entities fiscal period can be more or less than 12 months. Also,
I've seen some entities define their fiscal year in days (360 or 365 - I
don't remember exactly), but I do remember their FYB sometimes was in
January, sometimes in December (and I don't recall whether or not the
quarters broke on the same date every year).

Where H5 has the FYB, and J8 has the date in question, I think this will
address the issue of the fiscal year not matching the calendar year
=IF(J8<=EOMONTH(H5,11),MATCH(J8,DATE(YEAR(H5),MONT H(H5)+{0,3,6,9},1)),"")

array entered, but it's still incomplete as it doesn't address other
possible scenarios.

"Biff" wrote:

Easy enough:

=CEILING(MONTH(J2)/3,1)

For robustness:

=IF(ISNUMBER(J2),CEILING(MONTH(D2)/3,1),"")

Biff

"Jeff Stubing" <Jeff wrote in message
...
Excel should have a built in QUARTER function which allows users to
calculate
the QUARTER in which a transaction occurs based on the date of the
transaction.

Ex: =QUARTER(J2) where cell J2 holds a date.

You have it for MONTH, DAY, and YEAR why not for QUARTER for those of us
in
the financial world?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions



  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,688
Default Excel should have a QUARTER formula for DATE calculations

That's probably why there isn't a Quarter function!

Biff

"JMB" wrote in message
...
But, fiscal years don't have to have the same start/end dates as calendar
years. For example, the U.S. Federal Fiscal Year is 10/1 - 9/30. In some
cases, an entities fiscal period can be more or less than 12 months.
Also,
I've seen some entities define their fiscal year in days (360 or 365 - I
don't remember exactly), but I do remember their FYB sometimes was in
January, sometimes in December (and I don't recall whether or not the
quarters broke on the same date every year).

Where H5 has the FYB, and J8 has the date in question, I think this will
address the issue of the fiscal year not matching the calendar year
=IF(J8<=EOMONTH(H5,11),MATCH(J8,DATE(YEAR(H5),MONT H(H5)+{0,3,6,9},1)),"")

array entered, but it's still incomplete as it doesn't address other
possible scenarios.

"Biff" wrote:

Easy enough:

=CEILING(MONTH(J2)/3,1)

For robustness:

=IF(ISNUMBER(J2),CEILING(MONTH(D2)/3,1),"")

Biff

"Jeff Stubing" <Jeff wrote in message
...
Excel should have a built in QUARTER function which allows users to
calculate
the QUARTER in which a transaction occurs based on the date of the
transaction.

Ex: =QUARTER(J2) where cell J2 holds a date.

You have it for MONTH, DAY, and YEAR why not for QUARTER for those of
us
in
the financial world?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the
"I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and
then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions







  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
JMB JMB is offline
external usenet poster
 
Posts: 2,062
Default Excel should have a QUARTER formula for DATE calculations

That's pretty much where I was going with it. Although I think the OP was
wanting calendar year - which I doubt can be made more elegant than your
solution.

"Biff" wrote:

That's probably why there isn't a Quarter function!

Biff

"JMB" wrote in message
...
But, fiscal years don't have to have the same start/end dates as calendar
years. For example, the U.S. Federal Fiscal Year is 10/1 - 9/30. In some
cases, an entities fiscal period can be more or less than 12 months.
Also,
I've seen some entities define their fiscal year in days (360 or 365 - I
don't remember exactly), but I do remember their FYB sometimes was in
January, sometimes in December (and I don't recall whether or not the
quarters broke on the same date every year).

Where H5 has the FYB, and J8 has the date in question, I think this will
address the issue of the fiscal year not matching the calendar year
=IF(J8<=EOMONTH(H5,11),MATCH(J8,DATE(YEAR(H5),MONT H(H5)+{0,3,6,9},1)),"")

array entered, but it's still incomplete as it doesn't address other
possible scenarios.

"Biff" wrote:

Easy enough:

=CEILING(MONTH(J2)/3,1)

For robustness:

=IF(ISNUMBER(J2),CEILING(MONTH(D2)/3,1),"")

Biff

"Jeff Stubing" <Jeff wrote in message
...
Excel should have a built in QUARTER function which allows users to
calculate
the QUARTER in which a transaction occurs based on the date of the
transaction.

Ex: =QUARTER(J2) where cell J2 holds a date.

You have it for MONTH, DAY, and YEAR why not for QUARTER for those of
us
in
the financial world?

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the
"I
Agree" button in the message pane. If you do not see the button, follow
this
link to open the suggestion in the Microsoft Web-based Newsreader and
then
click "I Agree" in the message pane.

http://www.microsoft.com/office/comm...et.f unctions






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


Similar Threads
Thread Thread Starter Forum Replies Last Post
HOW CAN I PROTECT A FORMULA IN EXCEL Kingsley Excel Discussion (Misc queries) 6 August 13th 06 04:12 PM
Excel formula Q scoobz Excel Worksheet Functions 8 July 17th 06 07:19 AM
Trouble writing an excel formula. hbb2699 Excel Worksheet Functions 3 June 8th 06 06:36 PM
converting formula from lotus.123 to excel zaharah Excel Worksheet Functions 2 July 27th 05 03:04 PM
Formula for current month minus one = Quarter number in a macro. Pank Excel Discussion (Misc queries) 11 June 22nd 05 02:47 PM


All times are GMT +1. The time now is 07:37 PM.

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

About Us

"It's about Microsoft Excel"