OK, the formula in cell C1 and copied down to the last row of data is:
=IF(B1="","",IF(--B1<1,"",IF(--B2<1,--B1+--B2,--B1)))
You then need to format the cells in column C as Date/Time and convert the formulas to values (or vice-versa). At that point, you can delete the original column B.
A VBA module to do all that is shown below:
Option Explicit
Sub ReformatPDFData()
' =IF(B1="","",IF(--B1<1,"",IF(--B2<1,--B1+--B2,--B1)))
' note that the original cells do *NOT* contain true
' date and time, date, or time.
' They are text values which "look like" dates and times
' The -- coerces the values into numeric dates and times
Dim lLR As Long ' store number of last row of data
' determine last row of data
lLR = Range("B" & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
With Range("C1:C" & lLR)
' add formula to cells in column C
.Formula = "=IF(B1="""","""",IF(--B1<1,"""",IF(--B2<1,--B1+--B2,--B1)))"
' convert formulas to values
.Value = .Value
' format as date and time
.NumberFormat = "dd/mm/yyyy hh:mm:ss"
' delete original data
.Offset(0, -1).EntireColumn.Delete
End With
Application.ScreenUpdating = True
End Sub
Please see the attached updated sample workbook.
Regards, TMS
Bookmarks