Calculate difference between two time stamps...

Hi all, long time reader first time poster.

So, I am having trouble with a script.

What I have is a column in excel (Column E) with time stamps, their format is yyyy-mm-dd hh-mm-ss

What I want to do is calculate the difference between each one and put the number in the cell to the right of it.

I have been working on a code using DateDiff but I can't seem to recall how to call cells into it. ( I am new at VBA to please excuse any stupid questions).

What I have is as follows;

Please Login or Register  to view this content.

It always gives me an answer of 0 in cell F2, my question is do I have to change the format of the time stamp and if I do that, how can I loop the datediff so that it goes till the end of my time stamp column (column E)?

Thanks

fiy, here is an excerpt of what my time stamps look like.

Please Login or Register  to view this content.

Hi

1) I think you will have to change the date format to replace the - with a : for the time components at least. I managed to get the formula to work when the date had the - separator, but not the time.
2) do you want the cell to contain the formula, or the result of the formula.
3) formula is datedif, not datediff. Also, it is in the form of date1, date2, interval. I don't know what the interval "n" is.....

rylo

hi chi11, welcome to Excel forum, try to add Format option:

Please Login or Register  to view this content.

This would give you difference between date stamps in minutes

Hi Chi11

In your code, E3 and E2 will be identified as variables, not as ranges. Try enclosing them in a RANGE command:

Please Login or Register  to view this content.

Also, you don't need to select F2, you can just use:

Please Login or Register  to view this content.

This code works when your date stamp format is 2011/06/07 21:37:34. You have two date stamp formats in your data above. You would need to change the latter one (2011-06-09 01-36-24) to get this to work. The following code loops through every entry in column E, changes the format and then puts the DateDiff result in column F:

Please Login or Register  to view this content.

Hope this helps.

Cheers, Rob.

thanks for the quick response,

Originally Posted by rylo

Hi

1) I think you will have to change the date format to replace the - with a : for the time components at least. I managed to get the formula to work when the date had the - separator, but not the time.
2) do you want the cell to contain the formula, or the result of the formula.
3) formula is datedif, not datediff. Also, it is in the form of date1, date2, interval. I don't know what the interval "n" is.....

rylo

roylo, I think you are right on the formatting, something has to reformatted in the time stamps. all I need is the results of the formula. I think it's datediff in VBA. n is minutes.

Originally Posted by Paul

When you refer to cells in VBA, you have to specify them using Range or Cells, or at a very minimum brackets [].

Range("E2"), Range("E3")
Cells(2, 5), Cells(3, 5)
[E2],[E3]

Why are you trying to use VBA for this, and why DateDiff when you could just use a formula in cell F2 like

=E3-E2

and format it to show h:mm:ss (or whatever you want)?

Rylo - in VBA the function is DATEDIFF, with two F's. Worksheet formula is DATEDIF. The interval "n" is minutes, since "m" is already taken for months.

I want to use vba because this will be part of a much bigger script so I want to have it part of that. also with the way the time stamps are I cannot get that formula to work. thanks for the info on the brackets.

Originally Posted by watersev

hi chi11, welcome to Excel forum, try to add Format option:

Please Login or Register  to view this content.

This would give you difference between date stamps in minutes

I tried that but it did not work, it gave me a mismatch error.

Hi

Yep, datediff in VBA. Learn something with this all the time.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

change for your relevant cells. Should work.

rylo

thanks for the help rob, that was really fast.

one problem, I am erroring out at this line,

Please Login or Register  to view this content.

this is what happens in the data

Please Login or Register  to view this content.

actually that is rod's script that does that.

Hi Chi11

Ok - I think I know what's wrong. Try this revised script:

Please Login or Register  to view this content.

Apologies for the original bug!

Cheers, Rob.

that worked great thanks, Rob. I got lots to learn when it comes to vba.