Day of Quarter

Hello Friends,

Hope everyone is doing good, i am new to macros especially VBA, i have a requirement where i have to calculate the day of a quarter column in excel 2010.

Q: I have a Date (20120101) in A1, and in A2 i have to calculate the number of days in that quarter i.e.,

A1 A2

20120101 1
20120102 2
20120103 3
.
.
.
20120331 90
20120401 1
20120402 2
.
.
.
20120631 91
20120701 1
.
.
20120931 92
and same approach for quarter 4,2012

here the A1 column is already filled with dates just need to populate the A2 Column with appropriae count and its not only for year 2012 but i have to take it on for a lot of years.

Thanks in advance.

--
Sam

how is column A formatted - general or yyyymmdd? Does your list include every date for the periods?

yes it is formatted as YYYYMMDD, yes it will include every date for all the quaters.

Thanks,
Sam.

Please let me know if column A is general what will be the plan?

Hello,
First of all thanks for replying, but tha above did not work and it is giving me a error,i am sorry, if i have not properly stated the question, but i want to repost it.

Both the column as general FYI i am using excel 2010.

Date_SK Day_of_Quarter
20120101 1
20120102 2
20120103 3
.
.

20120131 31
20120201 32
20120202 33
.
.
20120331 90
20120401 1
20120402 2
.
.
.
20120501 31
20120501 32
.
.
.
20120630 91

so on for other quarters as well.

in the second column Here 1st Quarter which have first 3 month dates will be numbered from 1....90 and second quarter with april,may,june months start again from 1....91 and for third quarter dates it will numbered again from 1....92 and last quarter dates will be numbered from 1...92.

That means each new quarter is starting from 1


Thankyou , pls let me know if you need further details.

--
Sam

Hi,
When i tried the =TEXT(A2,"0000-00-00")-EOMONTH(TEXT(A2,"0000-00-00"),-1-MOD(MID(A2,5,6)-1,3))

then i am getting these values which are wrong at the first row, as 20120101 is the first day of the quarter then it should be 1 in Day_Of_Quarter column (in place of 32) and others are also not showing properly,please let me know your suggestions.Thankyou.


Date_SK Day_of_Quarter
20120101 32
20120102 63
20120103 3
20120104 35
20120105 66
20120106 6
20120107 38
20120108 69
20120109 9
20120110 41
20120111 72
20120112 12
20120113 44
20120114 75
20120115 15
20120116 47
20120117 78
20120118 18
20120119 50
20120120 81
20120121 21
20120122 53
20120123 84
20120124 24
20120125 56
20120126 87
20120127 27
20120128 59
20120129 90
20120130 30
20120131 62
20120201 63
20120202 2
20120203 34

Hi,
Sorry to bother you but it is giving me the wrong results(date format) instead of numbering, pls have a look at the below values in Day_Of_Quarter
for Date_Sk = 20120101 it should give me 1 and othe quarter should again start with 1,Thankyou so much.

Date_Sk Day_Of_Quarter

20120101 1/1/1900
20120102 1/2/1900
20120103 1/3/1900
20120104 1/4/1900
20120105 1/5/1900
20120106 1/6/1900
20120107 1/7/1900
20120108 1/8/1900
20120109 1/9/1900
20120110 1/10/1900
20120111 1/11/1900
20120112 1/12/1900
20120113 1/13/1900
20120114 1/14/1900
20120115 1/15/1900
20120116 1/16/1900
20120117 1/17/1900
20120118 1/18/1900
20120119 1/19/1900
20120120 1/20/1900
20120121 1/21/1900
20120122 1/22/1900
20120123 1/23/1900
20120124 1/24/1900
20120125 1/25/1900
20120126 1/26/1900
20120127 1/27/1900
20120128 1/28/1900
20120129 1/29/1900
20120130 1/30/1900
20120131 1/31/1900
20120201 2/1/1900
20120202 2/2/1900
20120203 2/3/1900
20120204 2/4/1900
20120205 2/5/1900
20120206 2/6/1900
20120207 2/7/1900
20120208 2/8/1900
20120209 2/9/1900
20120210 2/10/1900
20120211 2/11/1900
20120212 2/12/1900
20120213 2/13/1900
20120214 2/14/1900
20120215 2/15/1900
20120216 2/16/1900

=TEXT(A2,"0000-00-00")-EOMONTH(TEXT(A2,"0000-00-00"),-1-MOD(MID(A2,5,2)-1,3))
this is not working


-- shorter version

=TEXT(A2,"0000-00-00")-DATE(LEFT(A2,4),FLOOR(MID(A2,5,2)-1,3)+1,0) -- this is giving me the above results in the above post

Hi,
can we do it for this format of date, below are the fields i am expecting ,pls let me know your suggestions.


Date Day_of_Quarter
1/1/2012 1
1/2/2012 2
1/3/2012 3
1/4/2012 4
1/5/2012
1/6/2012
1/7/2012
1/8/2012
1/9/2012
1/10/2012
1/11/2012
1/12/2012
1/13/2012
1/14/2012
1/15/2012
1/16/2012
1/17/2012
1/18/2012
1/19/2012
1/20/2012
1/21/2012
1/22/2012
1/23/2012
1/24/2012
1/25/2012
1/26/2012
1/27/2012
1/28/2012
1/29/2012
1/30/2012
1/31/2012
2/1/2012 32
2/2/2012 33
2/3/2012 34
2/4/2012 35
.
.
.
3/31/2012 90
4/1/2012 1


Thankyou.
Sam

Great, its works now, thankyou alot for your patience and help, appreciate it.

--
Sam

Thankyou very much for the above, i have some more Q's like this.
my scenario is i have date and need to populate the other column with the numbering starting from 1 to 365, below is the example,thankyou.
Date DD
1/1/2012 1
1/2/2012 2
.
.
.
12/12/2012 365 or 366

--
sam