locked
Capture and calculated data based on work week to work in VBA or formulas RRS feed

  • Question

  • Hi,

    I have a requirements to capture the data and calculate the qty based
    on the defined work week. May i request your assistance or
    idea on how to to this in vba macro or a formulas. I'll giving a sample data as reference. In my
    sample data i have already the formulas but i wanted to automate the work
    week calculation specially if the number of days in a month has been change.
    thank you in advance.

    btw, the values of every column is came from other worksheet. i copy paste this data as my sample. I have an existing thread from other forum. http://www.excelforum.com/excel-programming-vba-macros/1026120-capture-and-calculate-data-based-on-work-week-in-vba-or-formulas.html

    How can I attached sample working file in this thread?


    Here is the formula i used in WK1
    =ABS(IFERROR(SUM(C2:J2)/AO2-25%,0))

    Today is
    July and it has 31 days, the 31 days will be distributed to the defined workweek
    and
    calculate the contents of the coresponding column.

    here is the distribution of columns per week as reference.
    (31 days)
    wk1 - Day1 to
    Day8 (8 colums)
    wk2 - Day9 to Day16 (8 colums)
    wk3 - Day17 to Day24
    (8)
    wk4 - Day25 to day 31 (7) columns

    (30 days)
    wk1 - Day1 to Day8
    (8 colums)
    wk2 - Day9 to Day16 (8 colums)
    wk3 - Day17 to Day23 (7)
    wk4
    - Day23 to day 30 (7) columns


    • Edited by Lenoj Tuesday, July 22, 2014 6:07 AM
    Tuesday, July 22, 2014 5:59 AM

Answers

  • Hi Lenoj,

    We can use Date function to get current system date and use Day function to get day of the date. Then we can write the business logice based on the day. Here is a sample for your reference:

    Sub reCaculate()
    If Day(Date) <= 8 Then
       'write you own busineess logic here
    ElseIf Day(Date) <= 16 Then
    
    ElseIf Day(Date) <= 24 Then
    
    Else
    
    End If
    
     
    End Sub

    And here are some links for you learning Excel deveoping:

    Welcome to the Excel 2013 developer reference

    How do I... (Excel 2013 developer reference)

    Hope it is helpful.

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, July 23, 2014 2:31 AM
  • Hi Lenoj,

     my concern is on how to automate calculating the data by WK (column). Column AJ to AQ

    How did you want to calculate thease cells? We can loop the cell via Range.Cells and set the value based on your own business logic. Here is a example sets the value for cells A1:A2 on active sheet to 'Hello Word!' for your reference:

    Sub loopRanges()
    For RowIndex = 1 To 2
        For columnIndex = 1 To 2
            ActiveSheet.Cells(RowIndex, columnIndex).Value = "Hello Word!"
        Next columnIndex
    Next RowIndex
    End Sub

    You can combine the code with the specific requirement to achive the goal.

    Hope it is helpful.

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, July 29, 2014 2:03 AM

All replies

  • Just want to know if this idea would work. I just create a cell AV1 in test worksheet with a value of "7/31/2014". then get the number of days and the result copied to cell AW1, the result is 31 days. I will add also the formula for february were it has a 28 and 29 days.

    WK1=IF(AW1=31,ABS(IFERROR(SUM(C30:J30)/AO30-25%,0)),IF(AW1=30,ABS(IFERROR(SUM(C30:J30)/AO30-25%,0))))

    WK2=IF(AW1=31,ABS(IFERROR(SUM(K30:R30)/AO30-25%,0)),IF(AW1=30,ABS(IFERROR(SUM(K30:R30)/AO30-25%,0))))

    WK3=IF(AW1=31,ABS(IFERROR(SUM(S30:Z30)/AO30-25%,0)),IF(AW1=30,ABS(IFERROR(SUM(S30:Y30)/AO30-25%,0))))

    WK4=IF(AW1=31,ABS(IFERROR(SUM(AA30:AG30)/AO30-25%,0)),IF(AW1=30,ABS(IFERROR(SUM(Z30:AF30)/AO30-25%,0))))

    Tuesday, July 22, 2014 7:37 AM
  • Hi Lenoj,

    We can use Date function to get current system date and use Day function to get day of the date. Then we can write the business logice based on the day. Here is a sample for your reference:

    Sub reCaculate()
    If Day(Date) <= 8 Then
       'write you own busineess logic here
    ElseIf Day(Date) <= 16 Then
    
    ElseIf Day(Date) <= 24 Then
    
    Else
    
    End If
    
     
    End Sub

    And here are some links for you learning Excel deveoping:

    Welcome to the Excel 2013 developer reference

    How do I... (Excel 2013 developer reference)

    Hope it is helpful.

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, July 23, 2014 2:31 AM
  • Hi Fei Xue, I wanted to attached a sample data. May i know on how to attached sample excel in this thread as reference. thanks.

    actually, it depends on how many day in a month to we have to calculate this process..Lile for week1 i well calculate this particular column  ( 8 colums for week1 even the if the number of day in a month is 30 or 31 days.

    Wednesday, July 23, 2014 9:51 AM
  • Hi Lenoj,

    You can upload the sample through OneDrive and share the link with us.

    >>actually, it depends on how many day in a month to we have to calculate this process..Lile for week1 i well calculate this particular column  ( 8 colums for week1 even the if the number of day in a month is 30 or 31 days.<<

    Did you want to know the current month contains how much days? There is no such funcion in VBA, but we can write own fucntion to achieve the goal. Here is the code for your reference:

    Function dhDaysInMonth(Optional dtmDate As Date = 0) As Integer
        ' Return the number of days in the specified month.
        If dtmDate = 0 Then
            ' Did the caller pass in a date? If not, use
            ' the current date.
            dtmDate = Date
        End If
        dhDaysInMonth = DateSerial(Year(dtmDate), _
         Month(dtmDate) + 1, 1) - _
         DateSerial(Year(dtmDate), Month(dtmDate), 1)
    End Function

    And you can refer to the columns using Worksheet.Columns Property.

    Hope it is hlepful.

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, July 23, 2014 10:12 AM
  • Hi Fei Xue, I have aleady the code to get the days in a month. my concern is on how to automate calculating the data by WK (column). Column AJ to AQ. Btw , here is the link of sample excel file data.  Thank you.

    Thursday, July 24, 2014 2:09 AM
  • Hi Lenoj,

     my concern is on how to automate calculating the data by WK (column). Column AJ to AQ

    How did you want to calculate thease cells? We can loop the cell via Range.Cells and set the value based on your own business logic. Here is a example sets the value for cells A1:A2 on active sheet to 'Hello Word!' for your reference:

    Sub loopRanges()
    For RowIndex = 1 To 2
        For columnIndex = 1 To 2
            ActiveSheet.Cells(RowIndex, columnIndex).Value = "Hello Word!"
        Next columnIndex
    Next RowIndex
    End Sub

    You can combine the code with the specific requirement to achive the goal.

    Hope it is helpful.

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, July 29, 2014 2:03 AM