|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
October 10th, 2007, 12:24 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
formula for date
i have two columns
col a col b q3 2/2/07 q2 10/12/06 I have 4 quarters in a financial year jul-sep, oct-dec, jan-mar and apr-jun is there a formula I can enter in col a so that when I enter date in col b then correct quarter will come up in col a Thank you for your assistance 
|
|
#2
October 10th, 2007, 12:43 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
formula for date
Hi
You posted this question in worksheet functions on 6th Oct. You had several answers then, including my response of ="Q"&CEILING(MONTH(B1),3)/3 Did they not work for you? -- Regards Roger Govier "bee" wrote in message ... i have two columns col a col b q3 2/2/07 q2 10/12/06 I have 4 quarters in a financial year jul-sep, oct-dec, jan-mar and apr-jun is there a formula I can enter in col a so that when I enter date in col b then correct quarter will come up in col a Thank you for your assistance
|
|
#3
October 10th, 2007, 01:00 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
formula for date
The formulaes worked for a calendar year but from that post but I could not
get it to work for the financial year. e.g 12/12/06 came up as q4 and I needed it to come up as q2. Thank you. "Roger Govier" wrote: Hi You posted this question in worksheet functions on 6th Oct. You had several answers then, including my response of ="Q"&CEILING(MONTH(B1),3)/3 Did they not work for you? -- Regards Roger Govier "bee" wrote in message ... i have two columns col a col b q3 2/2/07 q2 10/12/06 I have 4 quarters in a financial year jul-sep, oct-dec, jan-mar and apr-jun is there a formula I can enter in col a so that when I enter date in col b then correct quarter will come up in col a Thank you for your assistance
|
|
#4
October 10th, 2007, 01:04 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
formula for date
Given that the OP wanted the fiscal year to start in July, if the
previous answers were like the one you just posted, they probably didn't work... One modification: ="q"&CEILING(MONTH(DATE(2007,MONTH(A11)+6,1)),3 )/3 In article , "Roger Govier" rogerattechnology4NOSPAMu.co.uk wrote: Hi You posted this question in worksheet functions on 6th Oct. You had several answers then, including my response of ="Q"&CEILING(MONTH(B1),3)/3 Did they not work for you?
|
|
#5
October 10th, 2007, 01:14 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
formula for date
Roger,
I haven't spotted the thread you mentioned in worksheet.functions, but wouldn't your formula need to look more like ="Q"&CEILING(MOD(MONTH(B9)+5,12)+1,3)/3 if it were to satisfy the OPs's description? -- David Biddulph "Roger Govier" rogerattechnology4NOSPAMu.co.uk wrote in message ... Hi You posted this question in worksheet functions on 6th Oct. You had several answers then, including my response of ="Q"&CEILING(MONTH(B1),3)/3 Did they not work for you? -- Regards Roger Govier "bee" wrote in message ... i have two columns col a col b q3 2/2/07 q2 10/12/06 I have 4 quarters in a financial year jul-sep, oct-dec, jan-mar and apr-jun is there a formula I can enter in col a so that when I enter date in col b then correct quarter will come up in col a Thank you for your assistance
|
|
#6
October 10th, 2007, 01:28 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
formula for date
I have tried this formula but it still does not work.
Col a Col B Q3 1/02/2007 Q3 2/12/2006 Q3 3/09/2006 Q3 5/4/2007 I entered ="Q"&CEILING(MONTH(DATE(2007,MONTH(A11=6,1)),3)/3 but it gave Q3 and the A11 changed to A12,a13 etc so I tried $a$11 but still gave Q3 on all lines Thank you. "JE McGimpsey" wrote: Given that the OP wanted the fiscal year to start in July, if the previous answers were like the one you just posted, they probably didn't work... One modification: ="q"&CEILING(MONTH(DATE(2007,MONTH(A11)+6,1)),3 )/3 In article , "Roger Govier" rogerattechnology4NOSPAMu.co.uk wrote: Hi You posted this question in worksheet functions on 6th Oct. You had several answers then, including my response of ="Q"&CEILING(MONTH(B1),3)/3 Did they not work for you?
|
|
#7
October 10th, 2007, 01:34 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
formula for date
Change the reference to that of your actual cell, e.g.:
="q"&CEILING(MONTH(DATE(2007,MONTH(B2)+6,1)),3)/3 (Note that you entered something rather different that what I posted...) In article , bee wrote: I have tried this formula but it still does not work. Col a Col B Q3 1/02/2007 Q3 2/12/2006 Q3 3/09/2006 Q3 5/4/2007 I entered ="Q"&CEILING(MONTH(DATE(2007,MONTH(A11=6,1)),3)/3 but it gave Q3 and the A11 changed to A12,a13 etc so I tried $a$11 but still gave Q3 on all lines Thank you. "JE McGimpsey" wrote: Given that the OP wanted the fiscal year to start in July, if the previous answers were like the one you just posted, they probably didn't work... One modification: ="q"&CEILING(MONTH(DATE(2007,MONTH(A11)+6,1)),3 )/3 In article , "Roger Govier" rogerattechnology4NOSPAMu.co.uk wrote: Hi You posted this question in worksheet functions on 6th Oct. You had several answers then, including my response of ="Q"&CEILING(MONTH(B1),3)/3 Did they not work for you?
|
|
#8
October 10th, 2007, 01:36 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
formula for date
Thank you very much
Your formulae worked perfectly very much appreciated "David Biddulph" wrote: Roger, I haven't spotted the thread you mentioned in worksheet.functions, but wouldn't your formula need to look more like ="Q"&CEILING(MOD(MONTH(B9)+5,12)+1,3)/3 if it were to satisfy the OPs's description? -- David Biddulph "Roger Govier" rogerattechnology4NOSPAMu.co.uk wrote in message ... Hi You posted this question in worksheet functions on 6th Oct. You had several answers then, including my response of ="Q"&CEILING(MONTH(B1),3)/3 Did they not work for you? -- Regards Roger Govier "bee" wrote in message ... i have two columns col a col b q3 2/2/07 q2 10/12/06 I have 4 quarters in a financial year jul-sep, oct-dec, jan-mar and apr-jun is there a formula I can enter in col a so that when I enter date in col b then correct quarter will come up in col a Thank you for your assistance
|
|
#9
October 10th, 2007, 02:24 PM
posted to microsoft.public.excel.newusers
|
|||
|
|||
|
formula for date
Hi
I'm sure there is an easier way, but this works ="Q"&2+CEILING(MONTH(B1),3)/3-4*(MONTH(B1)6) -- Regards Roger Govier "bee" wrote in message news  The formulaes worked for a calendar year but from that post but I could not get it to work for the financial year. e.g 12/12/06 came up as q4 and I needed it to come up as q2. Thank you. "Roger Govier" wrote: Hi You posted this question in worksheet functions on 6th Oct. You had several answers then, including my response of ="Q"&CEILING(MONTH(B1),3)/3 Did they not work for you? -- Regards Roger Govier "bee" wrote in message ... i have two columns col a col b q3 2/2/07 q2 10/12/06 I have 4 quarters in a financial year jul-sep, oct-dec, jan-mar and apr-jun is there a formula I can enter in col a so that when I enter date in col b then correct quarter will come up in col a Thank you for your assistance
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
