PDA

View Full Version : Solved: Date format



ianswer
10-04-2011, 01:35 PM
I have following VBA: -


Dim DateVal As String
DateVal = Format(Date, "dd-mm-yyyy")


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
10-04-2011, 04:14 PM
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
10-04-2011, 06:42 PM
Maybe ...



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



Paul

ianswer
10-05-2011, 01:02 AM
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
10-05-2011, 06:20 AM
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
10-05-2011, 10:33 AM
If you want Excel to use the Regional Settings for date formating,


.NumberFormatLocal = "m/d/yyyy"


is the way to go

Paul

ianswer
10-05-2011, 12:02 PM
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
10-05-2011, 01:17 PM
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:


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


5. Or you can use WRS


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



Paul

justdriving
10-05-2011, 01:29 PM
Hi,

I hope this will solve your problem : -


Private Sub Workbook_Open()
Dim LValue As String

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

End Sub



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

Thanks,

mikerickson
10-05-2011, 06:14 PM
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.

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
On the other hand, this causes October 5, 2011 to be put in A1 and show 05-10-2011 as desired.
With Range("A1")
.NumberFormat = "dd-mm-yyyy"
.Value = Date: Rem October 5, 2011
MsgBox .Text: Rem 05-10-2011
End With

ianswer
10-06-2011, 12:17 PM
What does following do: -

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

GTO
10-06-2011, 12:38 PM
If you test that on a new/blank/throwaway wb, you will see that it changes the cell's numberformat to Text.

ianswer
10-07-2011, 01:31 PM
I also wanted to know what this code will do: -


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


and why this code was used before formatting: -


[$-409]


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

@

ianswer
10-07-2011, 01:41 PM
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.