|
Calculate a quarter
I have a large spreadsheet that is a five-year forecast. In one column is a
date, I need to know a way to calculate the quarter, in XQYY format, the event is forecasted to happen. Where X is the quarter from the table below, Q is a set value and XX is the forecast year. Date Range Quarter January €“ March 1 April - June 2 July €“ September 3 October €“ December 4 For example, March 2009, would be 1Q09. Thanks, -- JPS |
Calculate a quarter
=TEXT(MONTH(A1)/4+1,"0")&"Q"&TEXT(MOD(YEAR(A1),100),"00")
Regards, Fred. "JPS" wrote in message ... I have a large spreadsheet that is a five-year forecast. In one column is a date, I need to know a way to calculate the quarter, in XQYY format, the event is forecasted to happen. Where X is the quarter from the table below, Q is a set value and XX is the forecast year. Date Range Quarter January €“ March 1 April - June 2 July €“ September 3 October €“ December 4 For example, March 2009, would be 1Q09. Thanks, -- JPS |
Calculate a quarter
With a date in A1:
=ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2) -- Gary''s Student - gsnu200786 |
Calculate a quarter
GSNU200786 & Fred
Both solutions worked As always thanks for the help.... Thanks -- JPS "Gary''s Student" wrote: With a date in A1: =ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2) -- Gary''s Student - gsnu200786 |
Calculate a quarter
Get incorrect results:
2/1/2008 = 2Q08 6/1/2008 = 3Q08 -- Biff Microsoft Excel MVP "Fred Smith" wrote in message ... =TEXT(MONTH(A1)/4+1,"0")&"Q"&TEXT(MOD(YEAR(A1),100),"00") Regards, Fred. "JPS" wrote in message ... I have a large spreadsheet that is a five-year forecast. In one column is a date, I need to know a way to calculate the quarter, in XQYY format, the event is forecasted to happen. Where X is the quarter from the table below, Q is a set value and XX is the forecast year. Date Range Quarter January - March 1 April - June 2 July - September 3 October - December 4 For example, March 2009, would be 1Q09. Thanks, -- JPS |
Calculate a quarter
Here's another one:
=CEILING(MONTH(A1)/3,1)&"Q"&TEXT(A1,"yy") -- Biff Microsoft Excel MVP "JPS" wrote in message ... I have a large spreadsheet that is a five-year forecast. In one column is a date, I need to know a way to calculate the quarter, in XQYY format, the event is forecasted to happen. Where X is the quarter from the table below, Q is a set value and XX is the forecast year. Date Range Quarter January - March 1 April - June 2 July - September 3 October - December 4 For example, March 2009, would be 1Q09. Thanks, -- JPS |
Calculate a quarter
You're right. The fractions need to be ignored:
=TEXT(INT(MONTH(A1)/4)+1,"0")&"Q"&TEXT(MOD(YEAR(A1),100),"00") Regards, Fred/ "T. Valko" wrote in message ... Get incorrect results: 2/1/2008 = 2Q08 6/1/2008 = 3Q08 -- Biff Microsoft Excel MVP "Fred Smith" wrote in message ... =TEXT(MONTH(A1)/4+1,"0")&"Q"&TEXT(MOD(YEAR(A1),100),"00") Regards, Fred. "JPS" wrote in message ... I have a large spreadsheet that is a five-year forecast. In one column is a date, I need to know a way to calculate the quarter, in XQYY format, the event is forecasted to happen. Where X is the quarter from the table below, Q is a set value and XX is the forecast year. Date Range Quarter January - March 1 April - June 2 July - September 3 October - December 4 For example, March 2009, would be 1Q09. Thanks, -- JPS |
Calculate a quarter
Too bad the worksheet doesn't have the same quarter formatting as vba.
Function Quarter_Year(d As Date) Quarter_Year = Format(d, "q\Qyy") End Function -- Dana DeLouis "JPS" wrote in message ... I have a large spreadsheet that is a five-year forecast. In one column is a date, I need to know a way to calculate the quarter, in XQYY format, the event is forecasted to happen. Where X is the quarter from the table below, Q is a set value and XX is the forecast year. Date Range Quarter January €“ March 1 April - June 2 July €“ September 3 October €“ December 4 For example, March 2009, would be 1Q09. Thanks, -- JPS |
Calculate a quarter
if cell A1 is empty I want it to report a 0 how can i do this. thanks in
advance. I am ccreating a template that calculate quarters but find that the template without dates is hshowing that it is the 1st quarter. "Gary''s Student" wrote: With a date in A1: =ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2) -- Gary''s Student - gsnu200786 |
Calculate a quarter
Hi
If you do want to show a 0 then use =IF(A1="",0,ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2)) or if you want the cell to remain blank, then =IF(A1="","",ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2)) The reason it is returning Q1, is that empty date cells are taken to be 00/01/1900, hence they fall into Month 1 -- Regards Roger Govier "Compben" wrote in message ... if cell A1 is empty I want it to report a 0 how can i do this. thanks in advance. I am ccreating a template that calculate quarters but find that the template without dates is hshowing that it is the 1st quarter. "Gary''s Student" wrote: With a date in A1: =ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2) -- Gary''s Student - gsnu200786 |
Calculate a quarter
excellent, thank you I have been working on this for days
"Roger Govier" wrote: Hi If you do want to show a 0 then use =IF(A1="",0,ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2)) or if you want the cell to remain blank, then =IF(A1="","",ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2)) The reason it is returning Q1, is that empty date cells are taken to be 00/01/1900, hence they fall into Month 1 -- Regards Roger Govier "Compben" wrote in message ... if cell A1 is empty I want it to report a 0 how can i do this. thanks in advance. I am ccreating a template that calculate quarters but find that the template without dates is hshowing that it is the 1st quarter. "Gary''s Student" wrote: With a date in A1: =ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2) -- Gary''s Student - gsnu200786 |
Calculate a quarter
this formula worked
=IF(A1="",0,ROUNDUP(MONTH(A1)/3,0) what if I want it to report 1st, 2nd, 3rd and 4th instead of 1,2,3 & 4? I had modified the formula to this: VLOOKUP(IF(F262="","",INT((MONTH(F262)-1)/3)+1),LOOKUP!$A$1:$B$4,2,FALSE) Quarter New Quarter 1 1st 2 2nd 3 3rd 4 4th but now I still have my original problem now the formula is reporting #NA when the date cell is blank. "Roger Govier" wrote: Hi If you do want to show a 0 then use =IF(A1="",0,ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2)) or if you want the cell to remain blank, then =IF(A1="","",ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2)) The reason it is returning Q1, is that empty date cells are taken to be 00/01/1900, hence they fall into Month 1 -- Regards Roger Govier "Compben" wrote in message ... if cell A1 is empty I want it to report a 0 how can i do this. thanks in advance. I am ccreating a template that calculate quarters but find that the template without dates is hshowing that it is the 1st quarter. "Gary''s Student" wrote: With a date in A1: =ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2) -- Gary''s Student - gsnu200786 |
Calculate a quarter
what if I want it to report 1st, 2nd, 3rd and 4th instead of 1,2,3 & 4?
Try this =IF(COUNT(A1),INT((MONTH(A1)+2)/3)&MID("stndrdth",INT((MONTH(A1)+2)/3)*2-1,2),0) -- Biff Microsoft Excel MVP "compben" wrote in message ... this formula worked =IF(A1="",0,ROUNDUP(MONTH(A1)/3,0) what if I want it to report 1st, 2nd, 3rd and 4th instead of 1,2,3 & 4? I had modified the formula to this: VLOOKUP(IF(F262="","",INT((MONTH(F262)-1)/3)+1),LOOKUP!$A$1:$B$4,2,FALSE) Quarter New Quarter 1 1st 2 2nd 3 3rd 4 4th but now I still have my original problem now the formula is reporting #NA when the date cell is blank. "Roger Govier" wrote: Hi If you do want to show a 0 then use =IF(A1="",0,ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2)) or if you want the cell to remain blank, then =IF(A1="","",ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2)) The reason it is returning Q1, is that empty date cells are taken to be 00/01/1900, hence they fall into Month 1 -- Regards Roger Govier "Compben" wrote in message ... if cell A1 is empty I want it to report a 0 how can i do this. thanks in advance. I am ccreating a template that calculate quarters but find that the template without dates is hshowing that it is the 1st quarter. "Gary''s Student" wrote: With a date in A1: =ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2) -- Gary''s Student - gsnu200786 |
Calculate a quarter
Hi
It should be =IF(F262="","",VLOOKUP(INT((MONTH(F262)-1)/3)+1,Lookup!$A$1:$B$4,2,FALSE)) -- Regards Roger Govier "compben" wrote in message ... this formula worked =IF(A1="",0,ROUNDUP(MONTH(A1)/3,0) what if I want it to report 1st, 2nd, 3rd and 4th instead of 1,2,3 & 4? I had modified the formula to this: VLOOKUP(IF(F262="","",INT((MONTH(F262)-1)/3)+1),LOOKUP!$A$1:$B$4,2,FALSE) Quarter New Quarter 1 1st 2 2nd 3 3rd 4 4th but now I still have my original problem now the formula is reporting #NA when the date cell is blank. "Roger Govier" wrote: Hi If you do want to show a 0 then use =IF(A1="",0,ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2)) or if you want the cell to remain blank, then =IF(A1="","",ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2)) The reason it is returning Q1, is that empty date cells are taken to be 00/01/1900, hence they fall into Month 1 -- Regards Roger Govier "Compben" wrote in message ... if cell A1 is empty I want it to report a 0 how can i do this. thanks in advance. I am ccreating a template that calculate quarters but find that the template without dates is hshowing that it is the 1st quarter. "Gary''s Student" wrote: With a date in A1: =ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2) -- Gary''s Student - gsnu200786 |
Calculate a quarter
excellent!!! thanks, this one worked perfectly.
"T. Valko" wrote: what if I want it to report 1st, 2nd, 3rd and 4th instead of 1,2,3 & 4? Try this =IF(COUNT(A1),INT((MONTH(A1)+2)/3)&MID("stndrdth",INT((MONTH(A1)+2)/3)*2-1,2),0) -- Biff Microsoft Excel MVP "compben" wrote in message ... this formula worked =IF(A1="",0,ROUNDUP(MONTH(A1)/3,0) what if I want it to report 1st, 2nd, 3rd and 4th instead of 1,2,3 & 4? I had modified the formula to this: VLOOKUP(IF(F262="","",INT((MONTH(F262)-1)/3)+1),LOOKUP!$A$1:$B$4,2,FALSE) Quarter New Quarter 1 1st 2 2nd 3 3rd 4 4th but now I still have my original problem now the formula is reporting #NA when the date cell is blank. "Roger Govier" wrote: Hi If you do want to show a 0 then use =IF(A1="",0,ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2)) or if you want the cell to remain blank, then =IF(A1="","",ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2)) The reason it is returning Q1, is that empty date cells are taken to be 00/01/1900, hence they fall into Month 1 -- Regards Roger Govier "Compben" wrote in message ... if cell A1 is empty I want it to report a 0 how can i do this. thanks in advance. I am ccreating a template that calculate quarters but find that the template without dates is hshowing that it is the 1st quarter. "Gary''s Student" wrote: With a date in A1: =ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2) -- Gary''s Student - gsnu200786 |
Calculate a quarter
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "compben" wrote in message ... excellent!!! thanks, this one worked perfectly. "T. Valko" wrote: what if I want it to report 1st, 2nd, 3rd and 4th instead of 1,2,3 & 4? Try this =IF(COUNT(A1),INT((MONTH(A1)+2)/3)&MID("stndrdth",INT((MONTH(A1)+2)/3)*2-1,2),0) -- Biff Microsoft Excel MVP "compben" wrote in message ... this formula worked =IF(A1="",0,ROUNDUP(MONTH(A1)/3,0) what if I want it to report 1st, 2nd, 3rd and 4th instead of 1,2,3 & 4? I had modified the formula to this: VLOOKUP(IF(F262="","",INT((MONTH(F262)-1)/3)+1),LOOKUP!$A$1:$B$4,2,FALSE) Quarter New Quarter 1 1st 2 2nd 3 3rd 4 4th but now I still have my original problem now the formula is reporting #NA when the date cell is blank. "Roger Govier" wrote: Hi If you do want to show a 0 then use =IF(A1="",0,ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2)) or if you want the cell to remain blank, then =IF(A1="","",ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2)) The reason it is returning Q1, is that empty date cells are taken to be 00/01/1900, hence they fall into Month 1 -- Regards Roger Govier "Compben" wrote in message ... if cell A1 is empty I want it to report a 0 how can i do this. thanks in advance. I am ccreating a template that calculate quarters but find that the template without dates is hshowing that it is the 1st quarter. "Gary''s Student" wrote: With a date in A1: =ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2) -- Gary''s Student - gsnu200786 |
Calculate a quarter
In this formula, is there a way to change it to a fiscal year, being from
April to March (Apr, May, june being Q1) And is there a way to have the year first folowed by the Qs? Thank you "Gary''s Student" wrote: With a date in A1: =ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2) -- Gary''s Student - gsnu200786 |
| All times are GMT +1. The time now is 10:41 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com