Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: extracting weekly data from daily data

1. I agreed to these rules
Join Date
4th September 2005
Posts
9
Usergroup
Registered Users

## extracting weekly data from daily data

Hi there,

I have a spreadsheet with about 5000 sequential days in column A and corresponding prices in column B. I would like to copy consective sets of weeks and quarters into column c and do analyses on them, in a loop. The problem is some weeks are 4 days and others have missing data. I need to extract the data by week, 1 week at a time, for each work-week even if it is only 3 days long because of missing data.

Any help would be greatly appreciated.

Cheers,

Delberte

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: extracting weekly data from daily data

Hi Delberte,

how about adding a column on the end that showed the Week Number in it, you could then perform your calculations on the week number instead of the date itself

3. I agreed to these rules
Join Date
4th September 2005
Posts
9
Usergroup
Registered Users

## Re: extracting weekly data from daily data

Hi Dave,

I've tried using the weeknum function in XL 2003 but I get what I think are incorrect results. Either I'm using it incorrectly or there is a problem in XL

I have dates loaded in celll A3 as 1/3/2000, for example, for the first monday of the year and the datenum function (=DATENUM(A3,1)) says it is week 2 rather than week 1.

I'll keep playing with the weeknum function and see if there is a way around this.

Thanks for the suggestion.

Cheers,

Delberte

Excel Video Tutorials / Excel Dashboards Reports

4. ## Re: extracting weekly data from daily data

I am a bit confused when you mentioned DATENUM function since I dont seem to know it to work that way.
Establishes the date number for a date, when that date can be specified as either a date or a decimal year.
from this website

but know the WEEKNUM function. my guess is they both work the same way, but you check on it. Doing a search on the net here is a quote from a website:
For the year 2000, WEEKNUM will compute week 1 as containing either only 1 day, Saturday, 1-Jan, if the return-type setting is 1 (weeks begin on Sunday), or as containing 2 days, Saturday, 1-Jan, and Sunday, 2-Jan, if the return-type setting is 2 (weeks begin on Monday).
and this is the problem I think why you end up with a 2 instead of a 1. you can read more on that website

Sorry could not help more.
Last edited by laythss; September 6th, 2005 at 02:34. Reason: more explination and fix link

5. I agreed to these rules
Join Date
4th September 2005
Posts
9
Usergroup
Registered Users

## Re: extracting weekly data from daily data

Thanks for the suggestion.

While I work on this, do you have any thoughts on extracting 1 quarter at a time. I need to pull out the data for Jan, Feb, and Mar and do analyses on them, then do April, May June and so on for about 20 years of data.

Cheers,

Delberte

Excel Video Tutorials / Excel Dashboards Reports

6. ## Re: extracting weekly data from daily data

Delberte

Could we see some sample data?

What analysis are you doing on it?

Can you not use any of the built in methods such as a pivot table or grouping/sunb totalling?

Excel Video Tutorials / Excel Dashboards Reports

7. I agreed to these rules
Join Date
4th September 2005
Posts
9
Usergroup
Registered Users

## Re: extracting weekly data from daily data

Hi norie,

I am new to using forums. I don't know how to paste my spreadsheet, but I can describe it. In col A I have daily dates for many years. In col B I have corresponding prices. I copy various time windows to column C and do statistical analyses such as regression, box-jenkins, spectral and so on. I want to copy the data on prices for the first quarter's data, do the various analyses, then copy the second quarter's data to the same location and repeat and so on. I need to set up a loop that copies the data, does the analyses, stores them, copies the next set of data, and so on.

Because a "quarter" is the first 3 months of the year and is not always the same number of rows (due to holidays, leap-years, missing data), I need to somehow specify 3 months at a time as opposed to 365/4 days at a time and continue this through as much as 20 years of daily data.

Thanks for any suggestions you may have.

Cheers,

Delberte

Excel Video Tutorials / Excel Dashboards Reports

8. ## Re: extracting weekly data from daily data

sorry read the next post this was a duplicate
Last edited by laythss; September 6th, 2005 at 05:16. Reason: duplicate

9. ## Re: extracting weekly data from daily data

as a formula in the cell you would use something like this:
VB:
```=If(And(MONTH(A1)<=3,DAY(A1)>0),"first quarter",If(And(MONTH(A1)<=6,DAY(A1)>0),"2nd quarter",If(And(MONTH(A1)<=9,DAY(A1)>0),"3ed quarter",If(And(MONTH(A1)<=12,DAY(A1)>0),"4th quarter","ERROR"))))

```
but I am not sure if you are going to be using a formula in the cells, it seems 9since you said loop) you will be using VBA code, then it would be the same idea and you would still use
VB:
```If (MONTH(cells(1,1) < 3 And Day(Cells(1,1))>0) Then
msgbox (" This would be the first quarter")
Else
If ' and continue the nested if's

```
better yet you would use a select case:
VB:
``` 'assuming your dates are in column 1 (or A) then we will loop through all these cells
Dim i As Integer
i = 1
Do Until cells(i,1) = "" ' be aware that this will not allow a row gap in the
'dates, use a FOR NEXT if you have a gap
Select Case MONTH(cells(i,1))
Case 1,2,3
If Day(cells(i,1))>0 Then
' enter what you would like to do with the first quarter
End If

Case 4,5,6
'same thing
Case 7,8,9
'same
Case 10,11,12
'same
End Select
i = i+1
Loop

```
Hope this helps

10. I agreed to these rules
Join Date
4th September 2005
Posts
9
Usergroup
Registered Users

## Re: extracting weekly data from daily data

Hi Layth,

Thanks for the really helpful suggestions. The "do case" looks like what I need. Will try to incorporate it into my code.

Thanks again,

Cheers,

Delberte

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno