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.myonlinetraininghub.com/calculating-time-in-excel

https://www.myonlinetraininghub.com/excel-date-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

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

Your dates and times are still text.

If you actually make them dates/times you'll be able to do your calculations.

Regards

Phil

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

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