Excel macro adds a random value in "time" field

[sweetumz]

Hi all,

I've built a macro that will update call records from a file each month, and then filter certain type of calls and produce a file based on that filter. I recently noticed that when the macro runs to generate the output file, it is adding a value of "1/1/1900" in my "Call Duration" column for one cell (lets say A5). For instance, if I had a call for 48 minutes and 54 seconds and the format of the cell is "Custom" (h:mm:ss), the value in that cell (A5) changes to "1/1/1900 12:48:54 AM".

This is only happening for one cell. All the other cells show just the duration i.e. another cell below (A6) shows "5:17:18 AM"

Is there a way I can prevent the macro from adding "1/1/1900" to the output results?

Below is my VBA code, but I'm not sure if this issue has anything to do with VBA:

Sub Calls()
'
' Calls_Over_30_Mins Macro
'
Dim wb As Workbook, sh As Worksheet, ReportDate As String, lr As Long
ReportDate = ActiveSheet.Range("A31").Value
'
Set sh = Sheets("Call Usage Report Format")
lr = sh.Cells(Rows.Count, 1).End(xlUp).Row
With sh
'    .ShowAllData
    .Range("$A$1:$J" & lr).AutoFilter Field:=10, Criteria1:="N"
    .Range("$A$1:$J" & lr).AutoFilter Field:=8, Criteria1:= _
    ">=0:30:00", Operator:=xlAnd
End With
Set wb = Workbooks.Add
sh.Range("A1:J" & lr).Copy wb.Sheets(1).Range("A1")
    ChDir "C:\Reports"
'
'Sort Data
    'sh.Range("B1").Select
    'wb.Sheets(1).Range ("B1")
    wb.Worksheets("Sheet1").sort.SortFields.Clear
    wb.Worksheets("Sheet1").sort.SortFields.Add Key:=Range("B1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With wb.Worksheets("Sheet1").sort
        .SetRange Range("A2:J" & lr)
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    wb.SaveAs Filename:= _
        "V:\Reports\" & ReportDate & " Call Usage 30 minutes.xls" _
        , FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
sh.Cells.AutoFilter
MsgBox "File saved in specified path"
End Sub

 

[Somendra Misra]

Hi,

Welcome to chandoo.org forum.

Can you upload your sample file.

Regards,

 

[NARAYANK991]

Hi ,

There is absolutely no way the macro you have posted can introduce the additional 1 , which is what is the equivalent of 1/1/1900 ; if you can just check your original data , by introducing a formula in any unused column , as follows :

=(A1 > 1) + 0

What this will do is put 1 where your time has a date component , and 0 elsewhere ; with this formula in place , you can either use a MATCH function or do a FIND to locate the error value.

Narayan