Day number in a quarter

[tanuja.naik]

I need to find the day number in a quarter.
eg: for quarter 2, the number should be 1-90 starting from the first day
of the quarter.

Please guide as to how it can be done.

Thanks in advance.

[AliGW]

What are the dates of your quarters? 90 x 4 = 360 - what about the other five days of the year?

[samba_ravi]

Hi welcome to the forum,

Please attach a sample excel file with expected result

[tanuja.naik]

suppose i want to find the day number of the quarter for the year 2010.

I am actually creating a time dimension and i need to have data from 2010-2017
and for me one of the column is daynumberinquarter.

I am not able to think of a formula which will help me achieve this.

[AliGW]

Nor shall we be able to think of a formula for you unless you answer our questions. Once again, what are the dates of your quarters? If they are 90 days each, what happens with the remaining 5 days of the year?

[tanuja.naik]

Nor shall we be able to think of a formula for you unless you answer our questions. Once again, what are the dates of your quarters? If they are 90 days each, what happens with the remaining 5 days of the year?

1/1/2010-3/31/2010- quarter1- total no. of days=90
4/1/2010-6/30/2010- quarter2- total no. of days=91
7/1/2010-9/30/2010- quarter3- total no. of days=92
10/1/2010-12/31/2010- quarter4- total no. of days=92

[samba_ravi]

1/1/2010-3/31/2010- quarter1- total no. of days=90
4/1/2010-6/30/2010- quarter2- total no. of days=91
7/1/2010-9/30/2010- quarter3- total no. of days=92
10/1/2010-12/31/2010- quarter4- total no. of days=92

It gives better idea if you attached a sample excel file with your data and expected result

[tanuja.naik]

It gives better idea if you attached a sample excel file with your data and expected result

I am new to the forum. Can you please guide me on how to attach the excel. I am unable to attach any file.

[samba_ravi]

Attach A File.jpg
see the picture

[avk]

click on "Go Advanced"
In Attachment : click on "Manage Attachments"
>click on choose file & select your file from respective folder > click on "open"
>click on upload
>close this window

[AliGW]

This can be done with a lookup table (you will need to extend this to encompass the years you want to include - you don't need the numbers in column B, which I have there merely as a reference):

Excel 2016 (Windows) 32 bit

	A	B	C	D	E
1	01/01/2010	90		42	13/05/2010
2	01/04/2010	91
3	01/07/2010	92
4	01/10/2010	92

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	D
1	=E1-VLOOKUP(E1,$A$1:$B$4,1,1)

Sheet: Sheet1

[avk]

Dear tanuja Naik, I attach, my one of file which will help for you.
Since as per date column "A", i have calculated weekday as : In column "B2" =TEXT(A2,"aaaa")
Also calculated number of weekday & quarter, in "C" & "D" column respectively.
All calculation base on date.

[AliGW]

@avk - Sorry for off-topic interjection:

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!