convert unix time in human time

tuspilica

New Member
Joined
Feb 9, 2016
Messages
16
Hello,

I'm trying to convert all the UNIX format time from all the worksheets into human format.
For that, i'm serching by "time event" columns (that is not fixed, in one sheet can be D, in other can be F), and where it finds this column, it perform the conversion.

It's working ok but it deletes also the very next column.
For example, if my Time event is incolumn D, it deletes the column E that contains data.

Code:
Sub ConvertDate()  Dim i             As Long
  Dim wks           As Worksheet
  Dim rFind         As Range
  Dim rOut          As Range


  For i = 2 To ActiveWorkbook.Sheets.Count
    Set wks = Sheets(i)


    With wks
      Set rFind = .Cells.Find(What:="Time event", LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False)
      If Not rFind Is Nothing Then
         Set rOut = Range(rFind, .Cells(Rows.Count, rFind.Column).End(xlUp))[COLOR=#ff0000][B].Offset(, 1)[/B][/COLOR]
        If rOut.Rows.Count > 1 Then
          rOut.Cells(1) = "Time event"
          With rOut.Offset(1)
            .NumberFormat = "dd/mm/yyyy hh:mm:ss"
            .FormulaR1C1 = "=IF(RC[-1] = """", """", RC[-1]/86400 + DATE(1970,1,1) - DATE(1900,1,1))"
            .Value2 = .Value2
            .EntireColumn.AutoFit
[COLOR=#ff0000][B]            .Offset(, -1).EntireColumn.Delete[/B][/COLOR]
          End With
        End If
      End If
    End With
  Next i
End Sub

If i delete the red lines, the macro it will not delete the next column, but it will convert the UNIX time into a fixe date
31.12.1969 0:00:00
which is not good.

Can you help me with this?
Thank you in advance
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
You forgot to insert a column for your R1C1 formulas.

Code:
Sub ConvertDate()
    Dim i             As Long
    Dim wks           As Worksheet
    Dim rFind         As Range
    Dim rOut          As Range
    
    For i = 2 To ActiveWorkbook.Sheets.Count
    Set wks = Sheets(i)

    With wks
        Set rFind = .Cells.Find(What:="Time event", LookIn:=xlFormulas, LookAt:=xlWhole, MatchCase:=False)
        If Not rFind Is Nothing Then
            [COLOR=#ff0000]Columns(rFind.Column + 1).EntireColumn.Insert[/COLOR]
            Set rOut = Range(rFind, .Cells(Rows.Count, rFind.Column).End(xlUp)).Offset(, 1)
            If rOut.Rows.Count > 1 Then
                rOut.Cells(1) = "Time event"
                With rOut.Offset(1)
                    .NumberFormat = "dd/mm/yyyy hh:mm:ss"
                    .FormulaR1C1 = "=IF(RC[-1] = """", """", RC[-1]/86400 + DATE(1970,1,1) - DATE(1900,1,1))"
                    .Value2 = .Value2
                    .EntireColumn.AutoFit
                    .Offset(, -1).EntireColumn.Delete
                End With
            End If
        End If
    End With
Next i
End Sub
 
Upvote 0
Thank you Tetra201:)
The idea is very good and it works, somehow.
The issue is if i run the macro while i have the sheet with "Time event" opened, it is ok.
But, if i run the macro thru a button located on the 1st page, which is going to be the final design:

1. it introduce me a new column on the 1st sheet, what i don't want:
Code:
[COLOR=#333333] 
For i = 2 To ActiveWorkbook.Sheets.Count
[/COLOR][COLOR=#333333]    Set wks = Sheets(i)[/COLOR]

I want the macro to run (search and replace where exists the Time column) except the first one, that is protected and freezed (sheets 2 and 3 exists but are hidden).

2. the formula works, converts time, but it has the same behavior - not introducing the column on the sheets with Time, but only in the 1st one and "eats" the "Time+1" column.

:(
 
Upvote 0

Forum statistics

Threads
1,223,098
Messages
6,170,100
Members
452,301
Latest member
QualityAssurance

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top