| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Count of days in a quarter
Hi,
Is there any formula to count the number of days in the quarter. e.g if I have a date Feb6, 2007 in one cell, I want to populate corresponding cell with number of days in Q1 2007 (90 days). Please help me in this. Thanks, Mahesh |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count of days in a quarter
One way
=CHOOSE(INDEX({1;2;3;4},INT((MONTH(A1)+2)/3)),IF(DAY(DATE(YEAR(A1),2,29))=29,91,90),91,92,92 ) will take care of leap years as well if only for 2007 =CHOOSE(INDEX({1;2;3;4},INT((MONTH(A1)+2)/3)),90,91,92,92) -- Regards, Peo Sjoblom "Mahesh" wrote in message ... Hi, Is there any formula to count the number of days in the quarter. e.g if I have a date Feb6, 2007 in one cell, I want to populate corresponding cell with number of days in Q1 2007 (90 days). Please help me in this. Thanks, Mahesh |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count of days in a quarter
No need for the call to INDEX.
=CHOOSE(INT((MONTH(A1)+2)/3),90+(DAY(DATE(YEAR(A1),2,29))=29),91,92,92) -- Biff Microsoft Excel MVP "Peo Sjoblom" wrote in message ... One way =CHOOSE(INDEX({1;2;3;4},INT((MONTH(A1)+2)/3)),IF(DAY(DATE(YEAR(A1),2,29))=29,91,90),91,92,92 ) will take care of leap years as well if only for 2007 =CHOOSE(INDEX({1;2;3;4},INT((MONTH(A1)+2)/3)),90,91,92,92) -- Regards, Peo Sjoblom "Mahesh" wrote in message ... Hi, Is there any formula to count the number of days in the quarter. e.g if I have a date Feb6, 2007 in one cell, I want to populate corresponding cell with number of days in Q1 2007 (90 days). Please help me in this. Thanks, Mahesh |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count of days in a quarter
Yes and no, if you are going to be able to adapt it to financial quarters
yes, but if you are using simple calendar quarters like what the OP asked for then you are right that there is no need for the index. -- Regards, Peo Sjoblom "T. Valko" wrote in message ... No need for the call to INDEX. =CHOOSE(INT((MONTH(A1)+2)/3),90+(DAY(DATE(YEAR(A1),2,29))=29),91,92,92) -- Biff Microsoft Excel MVP "Peo Sjoblom" wrote in message ... One way =CHOOSE(INDEX({1;2;3;4},INT((MONTH(A1)+2)/3)),IF(DAY(DATE(YEAR(A1),2,29))=29,91,90),91,92,92 ) will take care of leap years as well if only for 2007 =CHOOSE(INDEX({1;2;3;4},INT((MONTH(A1)+2)/3)),90,91,92,92) -- Regards, Peo Sjoblom "Mahesh" wrote in message ... Hi, Is there any formula to count the number of days in the quarter. e.g if I have a date Feb6, 2007 in one cell, I want to populate corresponding cell with number of days in Q1 2007 (90 days). Please help me in this. Thanks, Mahesh |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count of days in a quarter
Yeah, good point about financial quarters.
-- Biff Microsoft Excel MVP "Peo Sjoblom" wrote in message ... Yes and no, if you are going to be able to adapt it to financial quarters yes, but if you are using simple calendar quarters like what the OP asked for then you are right that there is no need for the index. -- Regards, Peo Sjoblom "T. Valko" wrote in message ... No need for the call to INDEX. =CHOOSE(INT((MONTH(A1)+2)/3),90+(DAY(DATE(YEAR(A1),2,29))=29),91,92,92) -- Biff Microsoft Excel MVP "Peo Sjoblom" wrote in message ... One way =CHOOSE(INDEX({1;2;3;4},INT((MONTH(A1)+2)/3)),IF(DAY(DATE(YEAR(A1),2,29))=29,91,90),91,92,92 ) will take care of leap years as well if only for 2007 =CHOOSE(INDEX({1;2;3;4},INT((MONTH(A1)+2)/3)),90,91,92,92) -- Regards, Peo Sjoblom "Mahesh" wrote in message ... Hi, Is there any formula to count the number of days in the quarter. e.g if I have a date Feb6, 2007 in one cell, I want to populate corresponding cell with number of days in Q1 2007 (90 days). Please help me in this. Thanks, Mahesh |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count of days in a quarter
Here's another way
=SUM(DATE(YEAR(A1),FLOOR(MONTH(A1)-1,3)+{1,4},1)*{-1,1}) "T. Valko" wrote: Yeah, good point about financial quarters. -- Biff Microsoft Excel MVP "Peo Sjoblom" wrote in message ... Yes and no, if you are going to be able to adapt it to financial quarters yes, but if you are using simple calendar quarters like what the OP asked for then you are right that there is no need for the index. -- Regards, Peo Sjoblom "T. Valko" wrote in message ... No need for the call to INDEX. =CHOOSE(INT((MONTH(A1)+2)/3),90+(DAY(DATE(YEAR(A1),2,29))=29),91,92,92) -- Biff Microsoft Excel MVP "Peo Sjoblom" wrote in message ... One way =CHOOSE(INDEX({1;2;3;4},INT((MONTH(A1)+2)/3)),IF(DAY(DATE(YEAR(A1),2,29))=29,91,90),91,92,92 ) will take care of leap years as well if only for 2007 =CHOOSE(INDEX({1;2;3;4},INT((MONTH(A1)+2)/3)),90,91,92,92) -- Regards, Peo Sjoblom "Mahesh" wrote in message ... Hi, Is there any formula to count the number of days in the quarter. e.g if I have a date Feb6, 2007 in one cell, I want to populate corresponding cell with number of days in Q1 2007 (90 days). Please help me in this. Thanks, Mahesh |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Count of days in a quarter
That's pretty slick. I like it!
-- Biff Microsoft Excel MVP "daddylonglegs" wrote in message ... Here's another way =SUM(DATE(YEAR(A1),FLOOR(MONTH(A1)-1,3)+{1,4},1)*{-1,1}) "T. Valko" wrote: Yeah, good point about financial quarters. -- Biff Microsoft Excel MVP "Peo Sjoblom" wrote in message ... Yes and no, if you are going to be able to adapt it to financial quarters yes, but if you are using simple calendar quarters like what the OP asked for then you are right that there is no need for the index. -- Regards, Peo Sjoblom "T. Valko" wrote in message ... No need for the call to INDEX. =CHOOSE(INT((MONTH(A1)+2)/3),90+(DAY(DATE(YEAR(A1),2,29))=29),91,92,92) -- Biff Microsoft Excel MVP "Peo Sjoblom" wrote in message ... One way =CHOOSE(INDEX({1;2;3;4},INT((MONTH(A1)+2)/3)),IF(DAY(DATE(YEAR(A1),2,29))=29,91,90),91,92,92 ) will take care of leap years as well if only for 2007 =CHOOSE(INDEX({1;2;3;4},INT((MONTH(A1)+2)/3)),90,91,92,92) -- Regards, Peo Sjoblom "Mahesh" wrote in message ... Hi, Is there any formula to count the number of days in the quarter. e.g if I have a date Feb6, 2007 in one cell, I want to populate corresponding cell with number of days in Q1 2007 (90 days). Please help me in this. Thanks, Mahesh |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| COUNT how many ROWS ago out of 10 days that the highest high in 10 days was made | Excel Worksheet Functions | |||
| count days | New Users to Excel | |||
| Count # days | Excel Worksheet Functions | |||
| count down the days??? | Excel Discussion (Misc queries) | |||
| Count of days | Excel Worksheet Functions | |||
Linear Mode
