1d 2h 3m

  • Thread starter Thread starter Pietro
  • Start date Start date
P

Pietro

Hi all,

I'm exporting an excel sheet to Access,I have a field that indicates time
elapsed since the row was logged on the excel sheet,this field shos time
elapsed in format:1d 2h 31m,now i want to know when was this case
received,meaning that i want to return the value 1d 2h 3m into 12/09/2007
19:24,which equation should i use in this query to do this task?
 
You're going to have to write a function to parse the string "1d 2h 31m"
into the numeric value 1591 (for total minutes), and then use the DateAdd
function to add that numeric value to whatever your base time is to get the
desired result.
 
I don't really have time to write the function for you, but it would be
something like:

Function ConvertToMinutes(InputString As String) As Long
' Expects InputString to be of the format 1d 2h 31m. In other
' words, three groups of values, separated by spaces, with
' d, h or m units at the end of each group.
'
' Function returns 0 if InputString is incorrect.

Dim lngLoop As Long
Dim lngMinutes As Long
Dim varValues As Variant

varValues = Split(InputString, " ")
If UBound(varValues) = 2 Then
For lngLoop = 0 To 2
Select Case Right(varValues(lngLoop), 1)
Case "d"
lngMinutes = lngMinutes + _
Val(varValues(lngLoop)) * 24& * 60&
Case "h"
lngMinutes = lngMinutes + _
Val(varValues(lngLoop)) * 60&
Case "m"
lngMinutes = lngMinutes + _
Val(varValues(lngLoop))
End Select
Next lngLoop
End If

ConvertToMinutes = lngMinutes

End Function

Now, I have no idea to what starting date you're trying to add that
duration, but let's assume that you're trying to find how long after 10:00
08 Dec, 2007 it is. You'd use something like:

DateAdd("m", ConvertToMinutes("1d 2h 31m"), #2007-12-08 10:00:00#)
 
Back
Top