Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel should have a QUARTER formula for DATE calculations
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
HOW CAN I PROTECT A FORMULA IN EXCEL | Excel Discussion (Misc queries) | |||
Excel formula Q | Excel Worksheet Functions | |||
Trouble writing an excel formula. | Excel Worksheet Functions | |||
converting formula from lotus.123 to excel | Excel Worksheet Functions | |||
Formula for current month minus one = Quarter number in a macro. | Excel Discussion (Misc queries) |