Log In
Register
Home
Active Member
November 23, 2018
Offline
Hi,
I'm a beginner in VBA. I've tried google anywhere and never found the way to do this. I've a request report contain multiple date&time depending on how many times requestor send back to us. I can count how many times requestor loop back the request at column B.
Example first request having 3 times of loop at column B,
I need to take value of date&time at each of these Header name and sum up= [Date(3)&Time(3) -Date(2)&Time(2)] + [Date(2)&Time(2) -Date(1)&Time(1)] + [Date(1)&Time(1) -Date(0)&Time(0)]
or can start at columnAB&AC and jump every 3 cells and the loop continue until it found empty cell.
It would be best if I can exclude weekend and Public Holiday.
can it be done?
October 5, 2010
Offline
Hi Shila,
I'm not really clear on what your objective is.
The values you have in the columns headed Date and Time aren't dates or times, they are text, so doing any calculations with them is difficult.
You should store these values correctly, please see these
https://www.myonlinetraininghu.....e-in-excel
https://www.myonlinetraininghu.....e-and-time
Once you have the data stored correctly, what are you then trying to do with these dates and times? Work out the number of days/hours between them?
It's often better to state what you want as a result rather than say what calculations you think will give you that result. That way we can think of our own solution to the problem.
Regards
Phil
Active Member
November 23, 2018
Offline
Hi Phil,
I want to sum up at column C the total number of cycle time between multiple loop of date&time.
I put the formula in attachment as example of what 1 want.
Two of way I can think of:
1. start pick up Date&time from column AB&AC. Next date&time is jump every 5 cells. Loop using 'do until' cell is blank. Total up the differences every 2 date&time
2. using 'loop'. I've found the counter of loop at column B. start pick up Date&time from column AB&AC. Next date&time is jump every 5 cells until loop counter met. Total up the differences every 2 date&time
Pls advise if you have better way.
Thanks in advance
Regards,
Shila
October 5, 2010
Offline
Your dates and times are still text.
If you actually make them dates/times you'll be able to do your calculations.
Regards
Phil
Active Member
November 23, 2018
Offline
Hi Philip,
I can view it as number instead of text because my computer format is 01.0.12018 instead of 01/01/2018.
Btw I've made a code but still have error. Not sure this is correct or not.
'extract formula down to last row of column C
Dim LastRow As Long, CurRow As Long
LastRow = Range("A" & Rows.count).End(xlUp).Row
'--------------------------------------------------
'looping of cycle time
Dim i As Long
Dim counter As Integer
Dim total As Double
Dim sumTotal As Double
Dim value1 As Date
Dim value2 As Date
value1 = activecell.Value + activecell.Value.Offset(0, 1).Value
value2 = activecell.Value.Offset(0, 5).Value + activecell.Value.Offset(0, 1).Value 'shift right 5 column from activeCell
counter = Range("B8").Value 'define loop#
Range("AB8").Select 'go to first cell
For i = 1 To counter 'start loop at 1 until defined as each cell at columnB
total = -(value1 - value2)
Range("C8").Select = sumTotal 'place cell to put total. every loop will place down next row
sumTotal = sumTotal + total 'sum up all total
Next i
'-------------------------------------------------
For CurRow = 8 To LastRow
'Range("C" & CurRow).Value = sumTotal
Next CurRow
October 5, 2010
Offline
Hi Shila,
The data in the Date(x) and Time(x) columns are text. Your date format in the regional settings doesn't mean you can use text as dates.
The data in the Created On column is a date. If you select one of these you will see that in the formula bar it shows e.g. 14/10/2018 10:39:21 AM whereas if I select something in the Date(1) column all I see is 15.10.2018
Another way to check this is if you select multiple cells e.g. AG8:AG11, the status bar will just show you a count of 4 in the bottom right of your screen.
If these cells contained dates, you'd also see an Average and Sum, which is what you see if you select G8:G11.
Regards
Phil
1 Guest(s)
