DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 4 of 4

Thread: changing date from days to weeks

  1. #1
    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

  2. #2
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    If this is the issue
    Quote 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
    Quote 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?

  3. #3
    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.

  4. #4
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Put a break at the beginning of the routine and step through it to see what it is actually doing under those circumstances.

Similar Threads

  1. Replies: 2
    Last Post: 11-03-2006, 05:46 AM
  2. JAVA Date Utility Tutorial
    By hemanthjava in forum Java
    Replies: 0
    Last Post: 07-05-2006, 10:08 PM
  3. Check if a date it between two days.
    By ukemike in forum Database
    Replies: 1
    Last Post: 03-03-2005, 05:52 PM
  4. How Do I Add 10 Days to A Date Column?
    By Chris in forum Database
    Replies: 1
    Last Post: 08-25-2002, 10:54 PM
  5. Windows API for changing system date format
    By metta in forum VB Classic
    Replies: 0
    Last Post: 05-05-2000, 01:32 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center