Date Format

[CloudenL]

how do i add in when a workbook opens that a specific cell or cells automatically change to a date format (m/dd/yyyy) even if "jan 1 2011" or 1.1.2011 is typed in? is this possible?

 

[jbeaucaire]

In the ThisWorkbook module put in a Workbook_Open macro that formats the cell(s) on the specific sheet with a specific numberformat. For example:

Private Sub Workbook_Open()
    With Sheets("Sheet1")
        .Range("A5").NumberFormat = "MM-DDD-YYYY"
        .Range("E:E").NumberFormat = "MM-DDD-YYYY"
    End With
End Sub

 

[CloudenL]

Thank You, that worked, one additional question though. what if someone types it in as Jan 1, 2011, will it automatically convert it to the poper format? it doesnt seem to be working, so not sure if i am doing something incorrect in the spreadsheet itself under the formatting there, or does this code make the original formatting i did go away?

 

[jbeaucaire]

Once a cell has been formatted with a specific TIME/DATE number format, you shouldn't be able to easily trick Excel. It's pretty good at spotting a date "string" and converting it to date/format if the cell is already formatted as such. But it's not impossible.

If I type this:

Jan 1, 2011
Jan1, 2011

...Excel converts that properly to the date format already applied to the cell. But if I type this:

Jan1,2011
Jan 1,2011

...it won't. It just reads that as a text string.

 

[CloudenL]

Thank You