PDA

View Full Version : [SOLVED] Time Conversion



Svmaxcel
09-17-2017, 03:04 AM
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
09-17-2017, 03:25 AM
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
09-17-2017, 07:37 AM
Can't we try Len to check the lengh and formulate according

mdmackillop
09-17-2017, 07:59 AM
Let us know how you get on, or maybe post some actual data

Svmaxcel
09-17-2017, 11:19 AM
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
09-17-2017, 11:29 AM
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
09-17-2017, 12:03 PM
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
09-19-2017, 01:23 AM
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
09-19-2017, 01:30 AM
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
09-23-2017, 11:46 AM
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
09-23-2017, 01:51 PM
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
09-23-2017, 02:25 PM
Didn't try the code as I thought IT will take care of the issue.
Please explain.

SamT
09-23-2017, 02:45 PM
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
09-23-2017, 03:20 PM
Hi Sam
Did you see the file attached in post #8? I don't know where the 1Sep etc. is coming from.

Paul_Hossler
09-23-2017, 05:34 PM
Using .TextToColumns with Chr(9) as a deliminator makes a better (aka recognizable) worksheet

20444


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




20445

SamT
09-23-2017, 06:05 PM
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
09-24-2017, 02:22 AM
Lack of practice with English?
Aye.

Svmaxcel
09-24-2017, 06:45 AM
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
09-24-2017, 06:47 AM
Using .TextToColumns with Chr(9) as a deliminator makes a better (aka recognizable) worksheet

20444


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




20445

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
09-24-2017, 09:46 AM
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

Svmaxcel
09-25-2017, 12:37 AM
Superb/Excellent/Fanstatic.
Its Resolved.