Windows – How to keep Excel from changing the date format

microsoft excelwindows

Pretty basic question for a more advanced user than me but I can't seem to find an option.

I am working on an order form for my clients to fill out by plugging data into a preformatted spreadsheet. However, I am using a date format that apparently Excel doesn't like.

This: 01-31-15

Keeps changing to this: 1/31/2015

This just really doesn't work with what I am doing and I don't want to retype every date on every order.

I've tried making it a General text field, there are no existing functions in the cell but then it changes my setting to a date field and just keeps reformatting. I've also tried cursing at it and that didn't help either. 🙂

Best Answer

Try specifying a custom number format of mm-dd-yy, that should do the trick.

Incidentally, General is a number format that can cause headaches sometimes - Excel decides for itself what type of value it thinks you entered in a cell. I tend to stay away from General and explicitly pick my number formats where possible.

Related Solutions

Excel – How to set date format when manually inserting date on excel, without changing regional settings

The Worksheet_Change event could essentially accomplish what you're asking for, but you need to be sure the user intends things to change. I've added a 'Yes/No' choice box to my solution to prevent any accidental changes. Doing this without a choice box is risky because if you tried to edit an existing value the it would change back to the original formatting.

This code should 'live' inside the VBA code for the sheet you're wanting it to act on:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim iDay, iMonth, iYear As Integer
Dim solutionRange As Range

'the cells you want the date to change for
Set solutionRange = Range("D4:D12")

'only triggers if the user's changing one cell
If Target.Count = 1 Then
    If (Not (Intersect(solutionRange, Target) Is Nothing) And (Target.Value > 0)) Then
        'disable application events so this change doesn't trigger itself
        Application.EnableEvents = False

        iDay = Left(Target.Value, 2)
        iMonth = Mid(Target.Value, 4, 2)
        iYear = Right(Target.Value, 4)

        ' Give the user a choice to say whether they need the date changed. You *can* remove both the 'If' and 'End If' if you really need to, but I'd strongly suggest keeping them.
        If (MsgBox("Have you entered your date in the format 'MM/DD/YYYY'?", vbYesNo) = vbYes) Then
            Target.Value = DateValue(iMonth & "/" & iDay & "/" & iYear)
        End If

        Application.EnableEvents = True
    End If
End If
End Sub

Best Answer

Related Solutions

Excel – How to set date format when manually inserting date on excel, without changing regional settings

Related Question