# Capture and calculated data based on work week to work in VBA or formulas • ### 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.

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 Tuesday, July 22, 2014 6:07 AM
Tuesday, July 22, 2014 5:59 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)

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.

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 columnIndexNext RowIndexEnd Sub`

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

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.

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)

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.

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.

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
• • Edited by Thursday, July 24, 2014 2:33 AM
Thursday, July 24, 2014 2:30 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 columnIndexNext RowIndexEnd Sub`

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