Date format problem: 2012.03.45 ??

[TorontoJim]

I have a workbook that until yesterday was just fine. Now all of a sudden all the dates in two columns have changed so that the DAY is incrementing non-stop. That is, I have dates like:

2012.03.45
2012.03.46
2012.03.47
...
2012.03.820

I've checked the format and it's yyyy.mm.dd as it should be. I've tried changing the format but the format won't change anything. Does anyone have a solution for this?

 

[Sandeep Warrier]

If you change the format to General, what value do you see there? If these values are proper dates, you should get a number like 40998 (30/03/2012).

 

[TorontoJim]

If I change it to General, it doesn't change. Here is the code that inserts the date:

    wsRequestList.Cells(7, 2).NumberFormat = "yyyy.mm.dd"
    Dim strThisMonth As String
    strThisMonth = Month(Date)
    If Len(strThisMonth) < 2 Then
        strThisMonth = "0" & strThisMonth
    End If
    Dim strThisDOM As String
    strThisDOM = Day(Date)
    If Len(strThisDOM) < 2 Then
        strThisDOM = "0" & strThisDOM
    End If
    wsRequestList.Range("B7").Value = Year(Date) & "." & strThisMonth & "." & strThisDOM

I had to specifically format the days and months to two digits as these records form court documents. None of the built in formatting options would do that for me.

These dates have been FINE until yesterday when for no reason, they all started being incremented. Since I've concantenated the dates as a string I don't see how anything could have selectively incremented the day only without adding characters to the string.

 

[TorontoJim]

Sorry for the double post.

The only OTHER part of the macros that touches that cell is a process to Trim the value in case of manual entry (as there have been problems with manual entries and extra spaces). I've just been through all the macros in the workbook and nothing alters that cell once it is filled in except for the Trim.

 

[shg]

You're setting the format correctly, and then meticulousy ignoring it and populating the cell with a string. Maybe just:

    With wsRequestList.Range("B7")
        .NumberFormat = "yyyy.mm.dd"
        .Value = Date
    End With

If you really need a string in there for some reason (versus a formatted date), then just this:

    wsRequestList.Range("B7") = Format(Date, "yyyy.mm.dd")

 

[TorontoJim]

Yes, thanks. I am changing it for future entries. I still have to go back and do 800 manual edits.....

 

[shg]

I still have to go back and do 800 manual edits

Por que?

Select the column, do find and replace . with - and they will convert to dates.