| Home |
| Search |
| Today's Posts |
|
#1
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
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
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
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
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
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
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
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
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 ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Create a formula in a date range to locate a specific date - ecel | Excel Discussion (Misc queries) | |||
| Excel Formula to calulate number of days passed from date to date | Excel Discussion (Misc queries) | |||
| Date Function formula that will return the date of a specific week | Excel Worksheet Functions | |||
| Formula for determining if two date columns fall within specific date range | Excel Worksheet Functions | |||
| Formula for determining if two date columns fall within specific date range | Excel Discussion (Misc queries) | |||
Linear Mode
