Solved: Date format

[ianswer]

I have following VBA: -

[VBA]
Dim DateVal As String
DateVal = Format(Date, "dd-mm-yyyy")
[/VBA]

Regional Settings - Short Date - was already changed to dd-mm-yyyy

I wanted to ensure that Col D all cells from 2nd row onwards to have same format as aforesaid. How can I do?

[GTO]

Hi there,

At least as posed, select all the cells from row 2 and downward in Col D, right-click and choose format cells... Then select the number tab and custom in the category box. If the format you want is not listed, build it there.

[Paul_Hossler]

Maybe ...

[VBA]

Columns("D").NumberFormatLocal = "dd-mm-yyyy"

[/VBA]

Paul

[ianswer]

Hi there,

At least as posed, select all the cells from row 2 and downward in Col D, right-click and choose format cells... Then select the number tab and custom in the category box. If the format you want is not listed, build it there.

That's good idea. I can record Marco using your suggestion. But, I don't know why my system was not able to format Date.

[GTO]

Did you try Paul's suggestion?

Could you give a couple of examples of what exactly is being entered and how, where Excel does not coerce it?

[Paul_Hossler]

If you want Excel to use the Regional Settings for date formating,

[vba]
.NumberFormatLocal = "m/d/yyyy"
[/vba]

is the way to go

Paul

[ianswer]

Did you try Paul's suggestion?

Could you give a couple of examples of what exactly is being entered and how, where Excel does not coerce it?

Thanks Gentlemen,

Please refer to attachment.

Please view that LValue contains a string in format of DD-MM-YYYY

But, it passes that string to cell in format of MM-DD-YYYY

I don't know why?

[Paul_Hossler]

1. It's not clear to me why you're using the LValue string at all

2. Once Excel has a Date in a cell, there after any numerical value gets treated as a date

3. LValue = Format (Date, "dd-mm-yyyy") has nothing to do with WRS.

4. You can 'override' WRS:

[VBA]
Option Explicit

Private Sub Workbook_Open()

Range("A:A").NumberFormatLocal = "dd-mm-yyyy"
Range("A1").Value = Now
Range("A2").Value = Now + 1
Range("A3").Value = Now + 3
Range("A4").Value = Now + 5
Range("A5").Value = Now + 7
End Sub
[/VBA]

5. Or you can use WRS

[VBA]
Option Explicit

Private Sub Workbook_Open()

Range("A:A").NumberFormatLocal = "m/d/yyyy"
Range("A1").Value = Now
Range("A2").Value = Now + 1
Range("A3").Value = Now + 3
Range("A4").Value = Now + 5
Range("A5").Value = Now + 7
End Sub
[/VBA]


Paul

[justdriving]

Hi,

I hope this will solve your problem : -

[VBA]
Private Sub Workbook_Open()
Dim LValue As String

LValue = Format(Date, "Short Date")
Range("A1").NumberFormat = "@"
Range("A1").Value = LValue

End Sub

[/VBA]

You have to first ensure that cell is of same data type where your data will go.

Thanks,

[mikerickson]

I am concerned with putting a string into a cell and expecting it to become a number (date). I always hesitate to trust automatic conversion. I can't complain about my result if I don't ask for what I want.

For example, this causes the date May 10, 2011 to be put in A1 and show as 10-05-2011, because the auto-coversion from text to date does not take the number formatting into account.

[VBA]With Range("A1")
.NumberFormat = "dd-mm-yyyy"
.Value = Format(Date, "dd-mm-yyyy"): Rem Date = October 5, 2011
MsgBox .Text: Rem 10-05-2011
End With[/VBA]
On the other hand, this causes October 5, 2011 to be put in A1 and show 05-10-2011 as desired.
[VBA]With Range("A1")
.NumberFormat = "dd-mm-yyyy"
.Value = Date: Rem October 5, 2011
MsgBox .Text: Rem 05-10-2011
End With[/VBA]

[ianswer]

What does following do: -

cells(1,1).NumberFormat = "@"

[GTO]

If you test that on a new/blank/throwaway wb, you will see that it changes the cell's numberformat to Text.

[ianswer]

I also wanted to know what this code will do: -

[vba]
Cells(1,1).NumberFormat = "[$-409]d/mmm/yy;@"
[/vba]

and why this code was used before formatting: -

[vba]
[$-409]
[/vba]

Is there any special meaning to using following at end of formatting: -

[VBA]@[/VBA]

[ianswer]

I got it...

The [$-409] is a locale code, given in hexadecimal. Prefixing a date with a certain locale code determines what is shown when you use the various date time format codes.