Date/time issues! VBA.

[Crazzled]

So, I've made a small loop formula to process some data;

Sub testtosortdata()
Rows(1).Delete
Range("N:O").NumberFormat = "hh:mm:ss"
Dim counter As Integer
counter = 1
Do Until ThisWorkbook.Sheets("sheet4").Cells(counter, 14).Value = ""
'set loop with empty cell ending
Dim value1 As Date
value1 = Range("N" & (counter + 1)).Value
Dim value2 As Date
xvalue2 = Range("ON" & counter).Value
Dim value3 As Date
value3 = value2 - value1
ThisWorkbook.Sheets("sheet4").Range("O" & counter).Value = value2 - value1
counter = counter + 1
Range("O").NumberFormat = "hh:mm:ss"
Loop


End Sub

It works off an imported table, and once the row is deleted is it dates.

I have a small issue.

value1 is a date, and time.
value 2 is a date, and time.

I want to copy over the time only, but so far despite whatever formatting I seem to throw at it, it either makes the value it as -4xxxxxx.xxx, depending on the date.

I basically need to work out the different in minutes between the two times, ignorant of date since the dates will always be the same day.

Any ideas?

 

[Andrew Poulsom]

Is this really in your code?

xvalue2 = Range("ON" & counter).Value

Do you have Option Explicit set? if not I strongly recommend that you do.

To extract the time from a date and time:

value1 = Range("N" & (counter + 1)).Value - Int(Range("N" & (counter + 1)).Value)

or, with the cell formatted as time only:

value1 = TimeValue(Range("N" & (counter + 1)).Text)

 

[Richard Schollar]

You just need to multiply the result by 1440 to get a value in minutes:

ThisWorkbook.Sheets("sheet4").Range("O" & counter).Value = (value2 - value1)*1440

And format this cell using a number (rather than date) format

 

[Crazzled]

Thank you both!
Yeah, the names aren't too relevant - I was just trying to work out what would work, before I added it into my worksheet they will be defined with a bit more time when I write the actual bit in. I wasn't using option explicit, and I'm not even sure what it was but after a quick look into it, seems it would be best to have it on.