Converting Day Hour Minutes to Minutes in Excel or Access

[LonnieJo]

HELP. New at this and an Admin not a VB expert. Have Time Lapsed string
sets lapsed time to days hours minutes and downloaded to excel but now team
wants to see overall minutes as in < 36 hours, > 36 but < 48, etc etc so we
can graph the trends for service times. I am perplexed how to get this
changed. HELP

 

[AltaEgo]

I presume you have your time in hh:mm format? If so, these should help.

http://support.microsoft.com/kb/81176
http://www.mrexcel.com/archive/Dates/632.html
http://www.exceltip.com/st/Converting_Time_to_Decimal_Values/96.html

One in decimal, just calculate by 60 to get minutes.

 

[John W. Vinson]

HELP. New at this and an Admin not a VB expert. Have Time Lapsed string
sets lapsed time to days hours minutes and downloaded to excel but now team
wants to see overall minutes as in < 36 hours, > 36 but < 48, etc etc so we
can graph the trends for service times. I am perplexed how to get this
changed. HELP

You say "day hours minutes". What's the datatype? Could you post some specific
examples of how the value looks? Where are you downloading it from?

 

[LonnieJo]

Thanks John.

Here is what the cell/data looks like out of Access:

1 Day, 4 hours, 2 minutes

It is a date/time format.

 

[LonnieJo]

Thanks Steve. I will try this.
Happy Monday!

 

[John W. Vinson]

Thanks John.

Here is what the cell/data looks like out of Access:

1 Day, 4 hours, 2 minutes

It is a date/time format.

What is the *datatype* of the field? Open the table in design view and look at
the line for this field.

If you're storing duration data in a Date/Time field, be aware that it's not
really designed for that purpose: date/time fields are stored internally as a
double float number, a count of days and fractions of a day (times) from
midnight, December 30, 1899. The value above would actually be stored as
1.168055555555555555555, corresponding to #12/31/1899 04:02:00# if displayed
in a standard date/time format. My GUESS (and it's only a guess) is that the
field is actually in Text datatype; if it's an Excell cell, then it will be
treated as text in Access.

You'll probably need some VBA code to parse this into an integer number of
minutes, but I'll need to know the actual datatype to provide the code.

 

[LonnieJo]

Thanks John. Is there some where I can send you a sample excel sheet so you
can see? I am really confused. Thanks for your guidance. Really appreciate
it. I believe I have TONS to learn. I am at L Peterson at omlabs dot com.

 

[John W. Vinson]

Thanks John. Is there some where I can send you a sample excel sheet so you
can see? I am really confused. Thanks for your guidance. Really appreciate
it. I believe I have TONS to learn. I am at L Peterson at omlabs dot com.

I'm not any sort of Excel expert, my focus is on Access. I don't think I'd be
the best person to work on this for you! Have you posted the question in an
Excel group?

What I can say is how I would parse this in Access (into integer minutes):

1 Day, 4 hours, 2 minutes

Public Function GetMinutes(strIn As Strng) As Integer
If InStr(strIn, "Day") = 0 OR InStr(strIn, "Hours") = 0 _
Or InStr(strIn, "minutes" = 0 Then
GetMinutes = 0
MsgBox "Ill-formed time string", vbOKOnly
Else
GetMinutes = Val(strIn) * 1440 _
+ Val(Mid(strIn, InStr(strIn, "Day, ") + 1) * 60 _
+ Val(Mid(strIn, InStr(strIn, "hours, ") + 1)
End If
End Sub

Something very similar should work in an Excel macro but as I say I'm not well
versed in Excel, so the syntax may be a bit different.