'
' DateTime Macro
'
' On PADS files, this macro inserts two columns: one with a date in 
' YY-MM-DD format, and one with a time in HH:SS:DD format. The columns are 
' inserted after the Time column, in columns B & C.
'
'
    Dim instName As String
        
    Dim strDate As String
    Dim strYear As String
    Dim strMonth As String
    Dim strDay As String
    Dim numDays As Double
    Dim startDate As Date
    Dim currDate As Date
    
    Dim intHH As Double
    Dim intMM As Double
    Dim intSS As Double


Cells.Select
Selection.Find(What:="Instrument Type=", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

instName = Mid$(ActiveCell.Value, 17, Len(ActiveCell.Value) - 16)
instName = Replace(instName, """", "")                              'removes quotes around instr name if PADS inserts them

        

strDate = Mid$(ActiveWorkbook.Name, Len(instName) + 3, 8)
strYear = Left$(strDate, 4)
strMonth = Mid$(strDate, 5, 2)
strDay = Mid$(strDate, 7, 2)
        
strDate = strDay & "/" & strMonth & "/" & strYear           ' strDate is assigned to current date string extracted from Excel file name.
startDate = CDate(strDate)                                  ' startDate is assigned the date file recording started (a date variable)


    Cells.Select
    Selection.Find(What:="~****", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate             ' looks for **** text string to identify start of data
  
    ActiveCell.Offset(1, 1).Select
        
    Selection.EntireColumn.Insert
    Selection.EntireColumn.Insert
    ActiveCell.FormulaR1C1 = "Date"                      ' headings for new columns are assigned.
    ActiveCell.Columns.ColumnWidth = 12.57
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "HHMMSS"
    ActiveCell.Offset(1, -1).Select
    
currDate = startDate
numDays = 0
Do Until ActiveCell.Offset(0, -1) = ""                  ' Loop fills in date and time for remaining data rows.
    If ActiveCell.Offset(0, -1).Value > 86400 Then
      numDays = ActiveCell.Offset(0, -1).Value \ 86400  ' numDays = the number of days the program has been running.
      currDate = DateAdd("d", numDays, startDate)
    End If
    
    ActiveCell.Value = "'" & Format(currDate, "yyyy-mm-dd")      ' This line inserts an escape character before the current  does not
    currDate = startDate 					 ' date so that Excel does not try to convert it back to mm/dd/yyyy.
       
   
   ActiveCell.Offset(0, 1).Select
   intHH = ActiveCell.Offset(0, -2).Value \ 3600
   intMM = (Fix(ActiveCell.Offset(0, -2).Value) - (3600 * intHH)) \ 60
   intSS = Fix(ActiveCell.Offset(0, -2).Value) - (3600 * intHH) - (60 * intMM)
   If numDays > 0 Then
    intHH = intHH - (24 * numDays)                      ' hours are adjusted if program is running for more than one day.
   End If
     ActiveCell.Value = intHH & ":" & intMM & ":" & intSS
   numDays = 0
   
   ActiveCell.Offset(1, -1).Select
Loop