[RESOLVED] [Excel] Weird result in Excel when formatting to time: mm:ss.00

[adrian1906]

I have code that extract results from posted track and field results and produces reports for each athlete on our team. For the time results, I format them so that they all have the format mm:ss.00. This means that results for the 55m dash would look like

00:07.79

and results for a race walk may look like

20:05.00

It looks fine on the worksheet however when I save the reports as PDF files, there is an occasional ? with a box around it. I am formatting using the following command:
Range("A1").NumberFormat = "mm:ss.00"

I have been able to determine a difference between the cells that print properly and the ones that produce the funny character.

Lets say I have two cells, one with 00:07.40 and one with 00:07.79. When I click on the 1st cell, I see the date equivalent in the entry bar (12:00:08 AM) but when I click on the 2nd, I see the same text in the entry bar (00:07.79). I confirmed that both cells are indeed formated using mm:ss.00 by right clicking on the cells and choosing Format Cells. The second cell is printing with the strange character.

Thank you.

[Joacim Andersson]

Moved to the Office Development forum.

[westconn1]

regardless of what is displayed in the sheet, what is the value of the two cells?
maybe one is a formatted date, the other a literal string

[adrian1906]

I verified that they both have the custom format: mm:ss.00. Is there something else that would govern how the information is displayed and stored?

Thank you.
Adrian

[westconn1]

Is there something else that would govern how the information is displayed and stored?

yes, how it is input, formula, typing or code

[adrian1906]

The information is entered into the cells two ways. Either by hand or imported directly from a website. I thought that making sure the cells shared the same format was enough. What else needs to be adjusted?

Thank you.

[westconn1]

The information is entered into the cells two ways.

can you determine if the different entry methods, are the cause of the different print out?

What else needs to be adjusted?

i can not tell without seeing a sample of the inputs, check for leading spaces

[adrian1906]

I determined the problem.

My code extracts data from a .csv file with the following format:
Name,AgeGroup,Event,Mark
Jessie Parson,11-12,55m Hurdles,9.13
Jessie Parson,11-12,55m Hurdles,9.49
Eric Allen,13-14,55m Hurdles,8.4

It first calls a procedure that adds the necessary 0's and colons to get the "mm:ss.00" format:

'******************************************************
'Used to convert time given in seconds to the mm:ss.00 format
Public Function ConvertTimeFunction(myvalue As Variant) As String
    Dim TimeStr As String
    Dim temp() As String
    Dim temp2() As String
    On Error GoTo Err_Clear
    temp = Split(myvalue, ":")
    If UBound(temp) = 2 Then    ' check for too many :
        TimeStr = myvalue
        MsgBox "Problem with the value " & myvalue
    Else
        temp = Split(myvalue, ".")
        If UBound(temp) = 0 Then
            myvalue = myvalue + ".00"
        End If
        temp = Split(myvalue, ":")
        If UBound(temp) = 0 Then
            myvalue = "00:" & myvalue
        ElseIf temp(0) = "" Then
            myvalue = "00:" & myvalue
        ElseIf temp(0) = 0 Then    'case for format 0:ss.00
            TimeStr = "0" & myvalue
        End If
        temp = Split(myvalue, ":")    ' redo
        ConvertTimeFunction = Format(temp(0), "0#") & ":" & Format(temp(1), "0#.00")
    End If
Err_Clear:
    If Err <> 0 Then
        MsgBox "Problem with the value " & myvalue
        Err.Clear
        Resume Next
    End If
End Function

It then writes the value to a cell on the performance sheet, sets the correct cell format, and then produces a report. On the performance sheet, some times are imported from the .csv file and some are manually entered. In the report, the manually entered times print ok but the auto imported values produced a strange character after the times.

I determine that it had to do with the way I declared my variable in my DataImport() procedure. I had mistakenly thought that the following lines were valid:
Dim tmpNF, tmpvalue, tmpmeet as string
Dim startcell, stopcell, NewDatum, Datum_Report, Datum_Data, MyNewRange As Range

where the tmpvalue is used to hold the "Mark" value extracted from the .csv file and NewDatum.Offset(x,y).value =tmpvalue is used to write the value to a cell. NewDatum.Offset(x,y).NumberFormat = "mm:ss.00" is used to set the format.

Two things I noticed:
1.) When declaring variables, they must be independently declared (unlike VB.net)
2.) The issue was resolved when I first set the .NumberFormat for the cell and then wrote the value

This one was a tough one to debug because I received no indication that the method of declaration was invalid. I always write with Option Explicit expecting to be told of undeclared variables.

Thanks for all the help.

[westconn1]

I always write with Option Explicit expecting to be told of undeclared variables.

your declarations were valid, just not typed, any not specifically typed would be of type variant (microsofts default), so no warning would be given
the text assigned to a variant may not be the same type as that coereced into a variable of specific type (or type mismatch if not possible)

[adrian1906]

Earlier I wrote that the culprit was the fact that I didn't explicitly delcare my variables as strings therefore they became variant by default. It turns out that it wasn't the culprit after all (Phew...). The true reason is that a SPACE or Carriage Return or Line Feed is being appened to my extracted data. The computer I was using when I thought I found the answer produced PDF files that was able to 'handle' the end character (either Space,CR, or LF) leading me to conlcude that the problem was solved.

The issue is the way I created the .csv file: The following line was used:

MyMatch(2) & " " & MyMatch(3) & "," & AGandEvent & "," & ConvertTimeFunction(value))

By not ending each line with a comma, a space was inadvertantly added to the string when extracted using the split(MyLineOfValues,",") command. Westconn1 mentioned looking for spaces but my technique was flawed. In the .cvs file, I simply placed the curser at the end of one row and used the arrows to move to other rows. Each time, the curser would sit right next to the value indicating no spaces. However, when I highlighted the entire row, the extra space became apparent.

The fix,

I fixed the problem by simply adding another comma.

MyMatch(2) & " " & MyMatch(3) & "," & AGandEvent & "," & ConvertTimeFunction(value) & ","

Thanks