Time Conversion

[Svmaxcel]

Data which I receive is in hh:mm:as format for hours and minutes.
Sometimes I get data like
09:21 which is 9 min 21 sec
00:32 which is 32 sec
07:34:23 which is 7 hours 34 min and 23 sec
02:29:43 which is 2 hours 29 min and 43 sec

I want to convert it into minutes.
For example
01:30 should be 1.5 min.
02:00 should be 2 min.
02:30:14 should be 150 min.
Please guide me with the formula

[mdmackillop]

Your data is obviously confusing. How do you determine what is hh:mm and what is mm:ss.

Best guess without seeing your actual data.

Try this UDF

Function mins(Data)
x = UBound(Split(Data, ":"))
Select Case x
Case 1
    Mins = Round(TimeValue("00:" & Data) * 1440, 0)
Case 2
    Mins = Round(TimeValue(Data) * 1440, 0)
End Select
End Function

[Svmaxcel]

Can't we try Len to check the lengh and formulate according

[mdmackillop]

Let us know how you get on, or maybe post some actual data

[Svmaxcel]

2 questions here.

If time is displayed properly like 11:33:12, how can we converted it into minutes.

Q2: How to vice versa date and month, I mean if anyone type 9/6/2027 instead of 6/9/2017, how can we reverse it.

[mdmackillop]

Is 11:33:12 formatted as text or time?

"if anyone type 9/6/2027 instead of 6/9/2017." These are both valid dates; how would you know it was wrong? If you cannot trust the input (your own appears to contain a 10 year discrepancy) you need to develop alternative data entry methods.

[snb]

Sub M_snb()
  c00 = "09:21" ' which is 9 min 21 sec
  c01 = "00:32"  ' which is  32 sec
  c02 = "07:34:23"  ' which is 7 hours 34 min and 23 sec
  c03 = "02:29:43"  ' which is 2 hours 29 min and 43 sec
 
 For j = 1 To 4
    MsgBox Format(CDate(Right("00:" & Choose(j, c00, c01, c02, c03), 8)), "hh \hour\s NN \mi\nu\te\s ss \se\co\n\d\s")
 Next
End Sub

[Svmaxcel]

My system got formatted yesterday and ms office 2007 was installed.
I have got some weird issue.
Check the attach file, when I am trying to extract date,month, week using text I am getting Value error.
Tried to paste value in new sheet still same issue.

Also let me know how can I can I converted duration into minutes.

Attached file is the dump I receive from the tool

[mdmackillop]

Try

Sub Test()

    Pth = "F:\NR usage.csv"   'Change to suit
    
    ActiveWorkbook.Queries.Add Name:="NR usage", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents("" & pth & ""),[Delimiter=""    "", Columns=9, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Login Date/time"", type text}, {""Agent"", type text}, {""Event""" & _
        ", type text}, {""Event Date/time"", type text}, {""Reason Code Name"", type text}, {""Duration"", type time}, {""Date"", type text}, {""Month"", type text}, {""Week"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""NR usage""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [NR usage]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "NR_usage"
        .Refresh BackgroundQuery:=False
    End With
    Columns(6).NumberFormat = "[m]"" mins"""
End Sub

[Svmaxcel]

I would like to discuss the issue in details. The issue started after system was formated.
Didn't try the code as I thought IT will take care of the issue.
For example let's assume my Raw data as RAW.

Issues : I use a took which gives me login/logout details of employee, I have to select the Start and end date and click submit after which file is downloaded in my system.

When I open the file I see Date and events from 1 Sep to 23 sep.
I use text formula as text(a2,"DDD") and (a2,"mmm") to get month and day of that day.
Real issues
Dates from 1Sep to 12Sep are fine, I mean I get the month and date, but after 12Sep is what the issue is.
Excel is considering 13sep as 9 Jan. 14sep as 9 Feb and so on, the month and day formula shows Jan/Feb months, sometimes value error or sometimes Date comes instead of Month/Day.
I tried Datevalue got Vie error for some cells.

I have done below mentioned steps to solve it.

I do not havr admin rights, so asked the IT peraon to Changed regional setting to English US as it was India.
I use on citrix to work on client data, when i copy paste my raw file and paste it inside citrix excel, I get the same issue, tried to paste as Values only.
I saved the raw file and didn't open it in my excel and directly opened it in Citrix excel and that works fine, all dates are fine.
Uninstalled/Reinstalled 2007/2010 ms office with sp3.
Tried to copy paste as value in new excel sheet same issue.
Tried text to column and selected Date.
Changed excel date format.
Replaced "-" with "-" and "/" with "-" and "/".
Issue is with all the months I tried same for August. Also.
Data cannot be wrong as it works on other system which are not yet formatted.

Please suggest what could be wrong, all system which were formated recently have same issue as IT team as ran an IMG copy of windows 2007 pro.
Currently I have off 2007 with sp3
Should I opt for reinstalling OS

[SamT]

When I open the file I see Date and events from 1 Sep to 23 sep.
I use text formula as text(a2,"DDD") and (a2,"mmm") to get month and day of that day.
Real issues
Dates from 1Sep to 12Sep are fine, I mean I get the month and date, but after 12Sep is what the issue is.
Excel is considering 13sep as 9 Jan. 14sep as 9 Feb and so on,

That doesn't make sense. My Excel interprets both "13sep" and "sep13" as 9/13/2017. OTOH it sees text " '13sep" as "13sep"

What do the dates look like in the RAW file:
13Sep17?
13 Sep 2017?
13917?
13/9/2017?
13 9 17?
13 - 9 -2017?
Something else?

[mdmackillop]

Didn't try the code as I thought IT will take care of the issue.

Please explain.

[SamT]

I do not hav[e] admin rights, so asked the IT per[s]on to Change[] regional setting to English US as [I am in] India.

[mdmackillop]

Hi Sam
Did you see the file attached in post #8? I don't know where the 1Sep etc. is coming from.

[Paul_Hossler]

Using .TextToColumns with Chr(9) as a deliminator makes a better (aka recognizable) worksheet

Capture.JPG


I'm not sure about how duration is calculated, but if 5:04 = 304 seconds then the macro below

Option Explicit
Sub Macro1()
    Dim r As Long
    Dim t As String
    Dim v As Variant
    
    ActiveSheet.Columns(1).TextToColumns Destination:=Range("A1"), _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:=Chr(9), FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
    For r = 2 To ActiveSheet.Cells(1, 1).CurrentRegion.Rows.Count
        t = ActiveSheet.Cells(r, 6).Text
        ActiveSheet.Cells(r, 6).NumberFormat = Application.International(xlGeneralFormatName)
        
        v = Split(t, ":")
        Select Case UBound(v)
            Case 0
                ActiveSheet.Cells(r, 6).Value = v(0)
            Case 1
                ActiveSheet.Cells(r, 6).Value = 60 * v(0) + v(1)
            Case 2
                ActiveSheet.Cells(r, 6).Value = 3600 * v(0) + 60 * v(1) + v(2)
        End Select
    Next r
    
End Sub

Capture2.JPG

[SamT]

Hi Sam
Did you see the file attached in post #8? I don't know where the 1Sep etc. is coming from.

Lack of practice with English?

[mdmackillop]

Lack of practice with English?

Aye.

[Svmaxcel]

I didn't had admin right to change regional settings of windows.
It was changed in Admin account, but didn't resolve issue.
I logged in as admin did registry tweak and issue us now solved
Appreciate help from everyone.

[Svmaxcel]

Using .TextToColumns with Chr(9) as a deliminator makes a better (aka recognizable) worksheet

Capture.JPG


I'm not sure about how duration is calculated, but if 5:04 = 304 seconds then the macro below

Option Explicit
Sub Macro1()
    Dim r As Long
    Dim t As String
    Dim v As Variant
    
    ActiveSheet.Columns(1).TextToColumns Destination:=Range("A1"), _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:=Chr(9), FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
    For r = 2 To ActiveSheet.Cells(1, 1).CurrentRegion.Rows.Count
        t = ActiveSheet.Cells(r, 6).Text
        ActiveSheet.Cells(r, 6).NumberFormat = Application.International(xlGeneralFormatName)
        
        v = Split(t, ":")
        Select Case UBound(v)
            Case 0
                ActiveSheet.Cells(r, 6).Value = v(0)
            Case 1
                ActiveSheet.Cells(r, 6).Value = 60 * v(0) + v(1)
            Case 2
                ActiveSheet.Cells(r, 6).Value = 3600 * v(0) + 60 * v(1) + v(2)
        End Select
    Next r
    
End Sub

Capture2.JPG

Paul my data would be more then 70k+, should I go for this, or use normal formula or make data table for faster calculation

[Paul_Hossler]

I can't say which is faster, but my feeling is that adding formulas that have to be calculated would be slower

Try it and see

The delimiter in the CSV is a tab char (ASCII 9) so you you also use this line.

I used a Chr(9) since I was reading the file in hex

    ActiveSheet.Columns(1).TextToColumns Destination:=Range("A1"), _
        DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True