| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculate a quarter
With a date in A1:
=ROUNDUP(MONTH(A1)/3,0) & "Q" & RIGHT(YEAR(A1),2) -- Gary''s Student - gsnu200786 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| I want to round Int to quarter | Excel Worksheet Functions | |||
| Quarter Dates | Excel Worksheet Functions | |||
| how to calculate time start & time finish in quarter hour | Excel Discussion (Misc queries) | |||
| First and Last Day of the Quarter | Excel Worksheet Functions | |||
| Calculate % variance on previous quarters. Quarter %, etc. pivot | Excel Discussion (Misc queries) | |||
Linear Mode
