DevX Home Today's Headlines   Articles Archive   Tip Bank   Forums

# Thread: changing date from days to weeks

1. Registered User
Join Date
Feb 2008
Posts
11

## changing date from days to weeks

Hi. I have a spreadsheet with daily sales details. I would like to group these sales items into months for the different products and write the monthly data to another the sheet. e.g all Tomatoes sold during feb of the same year must be reflected as 1 amount. Please help.

the code below checks if the year and month are the same then checks if the products are the same and then writes to the new sheet.

Code:
```Public Sub ConsolidateMonthly()

Dim o As Integer
Dim n As Integer
Dim FirstDateValue As Date
Dim SecondDateValue As Date
Dim DateColumn As Integer
Dim ProductColumn As Integer
Dim AmountColumn As Integer
Dim FirstProduct As String
Dim SecondProduct As String
Dim FirstAmount As Integer
Dim SecondAmount As Integer
Dim ActualBalance As Integer

LastRow = getFirstEmptyRow("Reports") - 1
DateColumn = FindFieldIndex("Date", "Reports")

o = 2
ProductColumn = FindFieldIndex("Product", "Reports")

Do
n = DateColumn

FirstDateValue = CDate(Worksheets("Reports").Cells(2, n).Value)
SecondDateValue = CDate(Worksheets("Reports").Cells((o + 1), n).Value)

If Year(FirstDateValue) = Year(SecondDateValue) Then
If Month(FirstDateValue) = Month(SecondDateValue) Then
'If (Day(FirstDateValue) >= 1 And Day(FirstDateValue) <= 31) And (Day(seconddatevalue) >= 1 And Day(seconddatevalue) <= 31) Then

FirstProduct = Worksheets("Reports").Cells(3, ProductColumn).Value
SecondProduct = Worksheets("Reports").Cells((o + 1), ProductColumn).Value

If FirstProduct = SecondProduct Then

AmountColumn = FindFieldIndex("Actual Balance", "Reports")

FirstAmount = Worksheets("Reports").Cells(2, AmountColumn).Value
SecondAmount = Worksheets("Reports").Cells(o + 1, AmountColumn).Value

ActualBalance = FirstAmount + SecondAmount

Worksheets("Trial").Cells(o, 1).Value = Month(FirstDateValue)
Worksheets("Trial").Cells(o, 2).Value = Worksheets("Reports").Cells(o + 1, 2).Value
Worksheets("Trial").Cells(o, 3).Value = Worksheets("Reports").Cells(o + 1, 3).Value
Worksheets("Trial").Cells(o, 4).Value = Worksheets("Reports").Cells(o + 1, 4).Value
Worksheets("Trial").Cells(o, 5).Value = Worksheets("Reports").Cells(o + 1, 5).Value
Worksheets("Trial").Cells(o, 6).Value = Worksheets("Reports").Cells(o + 1, 6).Value
Worksheets("Trial").Cells(o, 7).Value = Worksheets("Reports").Cells(o + 1, 7).Value
Worksheets("Trial").Cells(o, 8).Value = ActualBalance

End If
End If
End If
o = o + 1
Loop While o <= LastRow

End Sub

help greatly appreciated```
Last edited by Hack; 02-21-2008 at 10:03 AM. Reason: Fixed Code Tags  Reply With Quote

2. If this is the issue Originally Posted by Mocha
Hi. I have a spreadsheet with daily sales details. I would like to group these sales items into months for the different products and write the monthly data to another the sheet. e.g all Tomatoes sold during feb of the same year must be reflected as 1 amount. Please help.
And this is true Originally Posted by Mocha
the code below checks if the year and month are the same then checks if the products are the same and then writes to the new sheet.
Then I don't understand the problem.

What issue are you having?  Reply With Quote

3. Registered User
Join Date
Feb 2008
Posts
11
the problem is that only the sales details with more than 1 product in the same month are written to the new sheet. I want it to write all products into the new sheets even if there is only one product type in the same month. eg if there was only 1 tomato and 4 cabbages sold in feb then it must still write the the tomato sales to the new sheet. all it does now is to write the cabbage details to the new sheet.  Reply With Quote

4. Put a break at the beginning of the routine and step through it to see what it is actually doing under those circumstances.  Reply With Quote

#### Posting Permissions

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