PDA

View Full Version : 2007 date formatting



ukyank
12-02-2013, 06:37 AM
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
12-02-2013, 06:54 AM
here is a test file with just the code for formatting and it still formats the date incorrectly.

Paul_Hossler
12-02-2013, 08:15 AM
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
12-02-2013, 09:53 AM
that works but basically isnt it "wrong"?

Paul_Hossler
12-02-2013, 12:22 PM
'Wrong' how?

ukyank
12-03-2013, 03:23 AM
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
12-03-2013, 07:59 AM
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
12-06-2013, 06:50 AM
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
12-07-2013, 11:15 PM
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
12-08-2013, 09:41 AM
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
12-08-2013, 12:10 PM
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
12-09-2013, 06:40 PM
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
12-10-2013, 12:17 AM
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
12-10-2013, 05:59 AM
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