time difference macro

[John Caines]

Hello All.
i have a problem ,, (Most people say that anyway )

I have a column A of data which is time data,, as an example;

25/03/2011 08:15:49
25/03/2011 08:19:53
25/03/2011 08:32:12
25/03/2011 08:44:03

I have another column, column F which is also time data,

25/03/2011 08:13:49
25/03/2011 08:16:06
25/03/2011 08:30:05
25/03/2011 08:41:54
25/03/2011 08:43:28

What I'm looking to do is find the "Hold Time", ie the different between the 2.
Column F is the start time, column A the closing Time.

So,, in the 1st example the answer is 2 minutes.
Is there anyway of creating a macro that I can click on that could give me this data?
It would be extremely useful.
If the data could be returned on the same sheet, in column M if possible.

I've tried looking on the forum,, can't see anything,, I'll look again though.
If someone can help me with this it would be great.
many thanks
john Caines

 

[CWatts]

You could use =F1-A1 and put that formula in column M and format the result as hh:mm

 

[DauntingGecko]

Hold down Alt + F11. This opens the VBE.

Click on 'Insert' > 'Module'. Paste the following code into the module:

Function MinuteDiff(Date1 As Date, Date2 As Date)

    On Error GoTo err
    
    MinuteDiff = dateDiff("n", Date1, Date2)
    Exit Function
    
err:
    MinuteDiff = "#N/A"

End Function

This gives you a custom function that works the difference out in minutes for you.

Go to your spreadsheet and type =MinuteDiff(a1,b1)

(change the a1 and b1 cells to the one's with your dates in).

Done.

 

[John Caines]

Many Thanks DauntingGecko,
I have a problem it seems,,,
Column A (Starts from A2), although it shows 25/03/2011 08:15:49
I didn't notice, but there is a formula in A2,, which is

="25/03/2011 08:15:49"

& F2 shows,25/03/2011 08:13:49
but has a formula of

="25/03/2011 08:13:49"

So, with your code DauntingGecko,, Cell M2 returns 48:00:00, which is wrong,,
(Should be 3 minutes, however that would be formatted)
not your fault I might add, I didn't mention/know/see that these cells had formula in
My sheet is here;
http://dl.dropbox.com/u/16052166/sheet1.xlsm
As a note DauntingGecko when you said
"Go to your spreadsheet and type =MinuteDiff(a1,b1)" did you mean in cell M2
& copy the formula down?
Sorry, my VBA isn't too hot
Many Thanks DauntingGecko
All the best
John Caines

 

[DauntingGecko]

Hi John,

I can't access your spreadsheet (blocked), but I've double checked my code and all seems fine.

Your formula doesn't affect it so not sure why your getting 48:00:00 >>???

Try copying and pasting as values for your dates so the formula is removed. Also try manually typing a new date and time with the formula as a test - it will work and should give you clues as to what is wrong with your spreadsheet.

 

[John Caines]

Hi Cwatts,
thanks for your reply also,,

If I do enter your formula I just get a lot of
###########################
And it says
"Dates and Times that are negative or are too larhe to display as #####"


It must be to do with they have been entered as a kind of formula?
Not sure.
hummmmmmmmmmmm

 

[John Caines]

Hi DauntingGecko,,
i'll try what you said now,,
the link is working,,, maybe it was because I tested it to see if it worked at the same time you were accessing it
Try it again,, it is ok,,,,


I'll try your suggestions now.

many thanks
JC

 

[CWatts]

My formula failed due to poor reading comprehension on my part.

You cant display negative time which is why it's not working. Switch the formula around and it should work, or so you dont have to worry about which side is later in time, you can get the time difference with this to force absolute value.

=ABS(F1-A1)

And then format as m:ss

 

[John Caines]

CWatts,,,!!!!
Working...
Yes,, you code is now working,, many thanks.
regarding DauntingGecko's code...
I can't get it to work properly
Maybe its the =MinuteDiff(A2,F2),,, somhow needs CWatts's suggestion,, a ABS part in it?

I'm not sure,,
But thanks for the formula CWatts..
Great stuff.
All the best
John Caines