Subtract two time stamps.

[gjsala]

I would like to get the difference in time from the following time stamps: (1/20/11 - 3:18:43.23 PM) and (1/22/11 - 5:05:06.43 AM) using VBA. I have used a text to column to take out the date and used military time to subtract the difference but the problem lies when I have a time stamp of greater than two days. Any ideas?
Thanks in advance!

 

[PHV]

What about the DateDiff function ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

or simply dt1 - dt2

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[AnotherHiggins]

the problem lies when I have a time stamp of greater than two days.

You don't specify what the problem is if the date difference is greater than 2 days - what are you seeing that you don't expect to see??

First, why would you "take out the date"? Aren't you trying to figure how many total hours have passed?

If you're putting the formula into a cell on the sheet, format the cell containing the formula as "[H]:MM:SS"

The square brackets tells Excel to display the total number of hours even if it's over 24. Otherwise it only shows the remainder of hours over a multiple of 24 because every 24 hours is moved to the "days" category.

Example: A difference of 52 hours would only show as 4 hours when formatted as "H:MM:SS".

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[gjsala]

I have the following code but I keep getting a "Invalid procedure call or argument". Can you please tell me what I'm missing?

Thanks!

Sub datediffFuction()
Dim pdate1 As Date, pdate2 As Date
Dim strDateString As String
pdate1 = Cells(5, 1)
pdate2 = Cells(4, 1)
strDateString = DateDiff(d, pdate2, pdate1)
End Sub

 

[SkipVought]

Your code returns DAYS (NUMERIC) between the two dates. That has nothing to do with a Date String????

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[gjsala]

Skip,
How would I change it to return Days(numeric) between the two days?

Thanks!

 

[SkipVought]

as I stated earlier

or simply dt1 - dt2

in this case

Sub datediffFuction()
Dim pdate1 As Date, pdate2 As Date
Dim lDays As Long
    pdate1 = Cells(5, 1)
    pdate2 = Cells(4, 1)
    lDays= pdate2 - pdate1
End Sub

or

Sub datediffFuction()
Dim lDays As Long
    lDays= Cells(4, 1).value - Cells(5, 1).value
End Sub

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PHV]

Anyway, to avoid "Invalid procedure call or argument":
strDateString = DateDiff([!]"[/!]d[!]"[/!], pdate2, pdate1)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[gjsala]

How would I be able to figure out how many hours are between these times stamps: (1/20/11 - 3:18:43.23 PM) and (1/22/11 - 5:05:06.43 AM)?

Thanks.

 

[Gavona]

Have a look at one of the many faqs on the topic. Like Skip's faq68-5827

Paste your dates/times into column A of a new worksheet
Select the cells containing your date/time values.
Run this macro to convert it to a real Excel date/time
(the resultant formulae use columns B to show the steps

Subtract one of the resultant date/time cells from another to get the difference in days
multiply by 24 to get the difference in hours.

Sub Macro1()

Set r = Selection

r.TextToColumns Destination:=r.Cells(1, 1), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 3), Array(7, 9), Array(9, 2), Array(23, 9)), _
        TrailingMinusNumbers:=True
r.Offset(0, 2).FormulaR1C1 = "=TIMEVALUE(RC[-1])"
r.Offset(0, 3).Formula = "=(R[0]C[-3]+R[0]C[-1])"

End Sub

Gavin

 

[gjsala]

Gavin,
Thanks for the response. When I ran this macro this is what happened:
(1/20/1 - 3:18:43.23 P #VALUE! #VALUE!
(1/22/1 - 5:05:06.43 A #VALUE! #VALUE!

Is there some formating that has to be done in columns C and D?

Thanks!

 

[Gavona]

Hi,

Firstly:
The formulae that are created expect a string that can be recognised by Windows (not Excel) as a date. The form that will be recognised as a date is set in Control Panel, Regional Language Settings, Formats (in Vista)

It works for
01/20/2011 3:18:00 PM
IF Windows recognises date formats mm/dd/yyyy (eg English US)

However, if like me Windows is set to recognise dates as dd/mm/yyyy (English UK) then it would not work for text in the above format - only in this format:
20/01/2011 3:18:00 PM

There are other options - it just depends on those regional settings.

Secondly:
The original text you have is unclear to me. Your most recent posting ends "P" rather than "PM)".
However it would appear that we need to remove the Opening and closing brackets and the hyphen in order for my solution to work.

So we may need code that creates this formula:
=TIMEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A6,")",""),"-",""),"(",""))
(Or VBA that loops through the string to remove the characters and if necessary re-orders to get around the first issue).

First Step:

It would be helpful if you could edit your text to discover what we need to do to your original text in order for the Timevalue function to recognise it as a date.
I suggest that you manually remove the characters ( ) - and if necessary swap around the day and month values etc.
Then post back precisely what form you need and the original format(s) of your dates/times.



Gavin