# Thread: How to convert Daily DATA in Excel into Monthly, Weekly and Yearly

1. Member
Join Date
Jan 2012
Posts
21

## How to convert Daily DATA in Excel into Monthly, Weekly and Yearly

I am looking at share prices across a five-year period and have them on a daily basis. If I want to graph them, there is little point in charting five-years worth of daily prices, as the graph loses clarity. I would like to be able to see long-term graphs in, say, weeks or months worth of data. Is there a way that I can run a Macro, or such thing that would automatically convert daily data into longer time frames according to my design?

2. Member
Join Date
Jul 2011
Posts
640

## Re: How to convert Daily DATA in Excel into Monthly, Weekly and Yearly

Well, you haven't exactly described what your design is. I presume you would have a code for each share and maybe a description/name, and also a date and a price, but you may have other fields as well. How would you like to combine daily data into monthly? As an average? If so, you can put 1st Jan 2008 in a cell (say M1) and 1st Feb 2008 in the next cell (M2) and then fill these down so you get 1st of each month thereafter. Then you could use this array* formula:
=AVERAGE(IF((MONTH(date_range)=MONTH(M1))*(YEAR(da te_range)=YEAR(M1))*(share_range="code")),price_ra nge))
Where date_range, share_range and price_range are whatever you use in your file, and code is the share you are interested in. Then you can copy the formula down for as many dates as you have in column M. An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) rather than the usual <Enter>. If you do this correctly then Excel will wrap curly braces { } around the formula when viewed in the formula bar - do not type these yourself. If you subsequently edit/amend the formula you must use CSE again.

3. Member
Join Date
Jun 2011
Posts
635

## Re: How to convert Daily DATA in Excel into Monthly, Weekly and Yearly

Can you give more details on the data you have. You can not create values ​​of daily dates from months only. Unless you divide the value of your month by the number of days, and thus have an average per day. This is not ideal. In fact I have a planning application for different products, but it is given in months.

4. Member
Join Date
Jun 2011
Posts
487

## Re: How to convert Daily DATA in Excel into Monthly, Weekly and Yearly

The Employees Hours of Work Manager application allows you to record daily input and output times of the employee and count the hours worked, any excess / lack of time, any recovery time and overtime. You can control the days of vacation, sick leave for the employee. The application consists of 14 worksheets plus a sheet with a brief introduction. There are two pages dedicated to the settings (Settings sheet Holidays and paper) which are connected for the remaining 12 sheets, one for each month of the year. You can use this to get easy result of what you are expecting.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
Page generated in 1,716,014,523.18471 seconds with 18 queries