2007 date formatting

[ukyank]

Hi I have a problem that comes up. I have a spreadsheet hosted on dropbox. Using the below code to set the date in a cell:

    If InStr(ThisWorkbook.Name, "NewCall") Then
        ThisWorkbook.Sheets("PreCall").Range("F2").Value = Format(Now, "dd/mm/yyyy")
    End If

The date used to format correctly (UK english: dd/mm/yyyy) then I made a different change to the workbook and now the cell displays the format as mm/dd/yyyy. After investigating this and asking on another forum I verified that the:

Cell formatting states: date, *14/03/2001 Locale; English (UK)
The system's regional and language options are set to English (UK) and the short date says Todays date (02/12/2013).

Can anyone tell me where I messed up? I don't have any other date formatting or date code in the workbook.

Cheers,
UKYank

[ukyank]

here is a test file with just the code for formatting and it still formats the date incorrectly.

[Paul_Hossler]

Not exactly sure about the format you want, but using Format() returns a String, and I think that makes it immune to Regional Settings

I'd do it like this, and it displays as 2/12/2013

Private Sub Workbook_Open()
With Sheets("Sheet1")
    .Activate
    .Range("B1").Value = Now
    .Range("B1").NumberFormatLocal = "m/d/yyyy"
End With
End Sub

Paul

[ukyank]

that works but basically isnt it "wrong"?

[Paul_Hossler]

'Wrong' how?

[ukyank]

Sorry I should have expanded on my comment. That change makes the cell value correct but it seems that evrywhere else it still says the US version of the date. This value is used a lot of places:

1. creating the file name
2. initial saving folder of file
3. filling out other excel workbooks

and having the value still incorrect makes a lot more places to look to modify code. Is the original code I used code that should be avoided? Is it "better" to process the formatting in two steps instead of one?

Also it just seems crazy that my pc setting and excel cell formatting are both set to UK and the date is still showing as US. I'd rather fix that then code around it.

[Paul_Hossler]

Bit kludgy, but I've used an out of the way cell as a scratchpad area to 'read' format strings in the past

This seems to work in US and UK formats, and returns the 10 char date in sToday formatted as 03/12/2013 or 12/03/2013

Private Sub Workbook_Open()
    Dim sToday As String
    With Sheets("Sheet1")
        .Activate
        .Range("B1").Value = Now
        .Range("B1").NumberFormat = "m/d/yyyy"
    
        sToday = Format(Now, .Range("B1").NumberFormatLocal)
    
        MsgBox sToday
    
    
    End With
End Sub

Paul

[ukyank]

Hi Paul,

Cheers, its just that its not ideal. A bit disappointing that the application doesn't want to do things its supposed to do and I have to go and change code in a lot of places in different workbooks.

Norman

[SamT]

Since the Cell itself is formatted in UK style, why not just

If InStr(ThisWorkbook.Name, "NewCall") Then 
    ThisWorkbook.Sheets("PreCall").Range("F2").Value = Now
End If

Then Format the cells Date value to a String when you're ready to use it

Dim X As String
X = Format(Range("F2"),"dd/mm/yy")

Just assigning the (Date Formatted)Cell's Text to a string variable might work

Dim X As String
X = Range("F2").Text

[ukyank]

Thanks Sam I'll look at that I'm still confused why I need to do all that. The code worked fine up until a little while ago. Now I'm getting errors all over the place. Like most people I use today's date in a lot of places and this is really weird that it stopped working.

[SamT]

Back to your first post: What changes did you make?

Please post original (working) code and the changes you made that broke it.

[Paul_Hossler]

To construct a formatted 'date' string (at least in 2010) based on the current Regional Settings you could use this

Sub dateorder()
    Dim s As String
    Select Case Application.International(xlDateOrder)
        Case 0
            s = Format(Now, "mm-dd-yyyy")
        Case 1
            s = Format(Now, "dd-mm-yyyy")
        Case 2
            s = Format(Now, "yyyy-mm-dd")
    End Select
    
    MsgBox s
End Sub

Also if the cell number formatting is m/dd/yyyy then what ever the Windows Regional Settings are will determine the display formats: 12/9/2013 (US) or 9/12/2013 (UK)

Paul

[SamT]

Sorry I should have expanded on my comment. That change makes the cell value correct but it seems that evrywhere else it still says the US version of the date. This value is used a lot of places:

and having the value still incorrect makes a lot more places to look to modify code. Is the original code I used code that should be avoided? Is it "better" to process the formatting in two steps instead of one?

Also it just seems crazy that my pc setting and excel cell formatting are both set to UK and the date is still showing as US. I'd rather fix that then code around it.

An error in nomenclature.

The Value of a cell that displays a date is a DateSerial number.

The displayed value (12/07/2013) is the Cells Text Property



When Excel or VBA uses the cell in a calculation, they use the Cell.Value property. But when Excel Paints the window, it paints the Cell.Text property.

Try this experiment: Format a cell to UK Date Style, then paste a date with an unambiguous day value in US date style into it. IE 12/25/2013.

Format two cells, one to UK and one to US. Format two other cells to Number + 11 decimal places. In the Date cells enter the same date in the appropriate style. Now copy the two date cells and PasteSpecial.Values into the two number cells.

Replace the Dates with the formula "=NOW()" . Repeat the PasteSpecial step.



Microsoft had a choice: Write every date function to parse every possible way of displaying dates, or use DateSerial as all date Values and let the user decide the display format they wanted.

[Paul_Hossler]

SamT is correct of course about the .Value and .Text (and a good explaination it was too)

I've always found Excel to handle Dates in 'a special way' in that once a cell contains a date, that is retained and any new values even if not a true date will keep the format

For some reason I thought the OP was looking for a string to be used in a file name

To me this in not the best way to enter a date

If InStr(ThisWorkbook.Name, "NewCall") Then
    ThisWorkbook.Sheets("PreCall").Range("F2").Value = Format(Now, "dd/mm/yyyy") 
End If

I think that you need to format the cell(s) as "m/dd/yyyy" either manually or via .NumberFormat and then assign .Value = Now.

IF the PC is in UK Region, that displays as 10/12/2013. Changing to US mode, it displays as 12/10/2013. In both cases the .Value is the same (41618) but the .Text changes to the Regional Setting

It appears that by using the Format() method instead of the Style = Date, it will = Custom and is not subject to the Settings

Bottom line, it seems that the best I can recommend would be to make sure that the appropriate cells are Styled as Date and only assign values using other date variables or DateSerial

Paul