3

In my locale, date is inserted as dd/mm/yyyy or variations of that (including dd-mm-yy). I made an Excel form (in normal Excel file), already formatted to show date as dd/mm/yyyy, but when I typed "05/04/2015" it will be shown as "04/05/2015". I know this can be solved by changing the regional setting to my local setting, but I need to distribute this form to a lot of people with no knowledge of changing regional setting, and assuming no one can teach how to, hence my question :

Is there any way to input "05/04/2015" and be recognized as "5 April 2015", without having to change regional settings ?

Additional note: When I mean "4 May 15" (typed in 4/5/15), Excel will recognize this as "5 April 15" because Excel takes input as "mm/dd/yy" format. I need to change the default behavior of Excel to take as "dd/mm/yyyy" as this is the way the users will input.

If possible, the setting should also included/embedded on the file to allow end-users use the form without even changing setting.

I'm not familiar with macros and VBA, but willing to try those :)

EDIT : this is related to my question : How to change date format from dd-mm-yyyy to dd-mm-yy in formula bar of MS-EXCEL?

but I don't need to change the whole formula bar, as long as Excel recognize that I'm typing in dd/mm/yy format.

  • Can't you just right-click and format the cells with the appropriate date format? – Karan Jun 8 '15 at 5:12
  • 1
    possible duplicate of How to get a custom date format in Excel – Rsya Studios Jun 8 '15 at 5:21
  • I've changed the format to "dd/mm/yyyy", but when I typed "5/4/15", it will swap the day and month to "4/5/2015" because it recognized 4 as the day and 5 as the month, whereas I mean 5 is the day and 4 is the month. – Vylix Jun 8 '15 at 7:02
  • 1
    @AdiNugroho, I think you need to answer Karan's question in the comments... Can you not just format the cell manually? – Dave Jun 8 '15 at 7:52
  • 2
    Yes, I can, and I've done it. However, when I input "5/4/15" (5 April '15), it will be shown as "04/05/15" (because it thinks 5 is the month). – Vylix Jun 8 '15 at 8:31
1

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
  • Will this work inside a protected sheet? Because I locked columns and only allow edits on limited columns. And how to limit the change only from D4 - D~ (D column starting from row 4) ? – Vylix Jun 9 '15 at 9:04
  • You'd need to add one line at the start of the code to unprotect the sheet, and another at the end to protect it again before handing it back to the user. See this article for info. If you look at line 5, I've let you specify exactly where the change is allowed to take place. – Steve Taylor Jun 9 '15 at 10:28
  • This is a good start, but you'd be well advised to add some validation of the user input before you start parsing the date. – Excellll Jun 17 '15 at 20:41
3

I know exactly what you are talking about. I like to type YY-MM-DD. The setting to change it to what you want is not in Excel. The setting you want is in windows. It is hidden somewhere in the control panel "time and language".

I had to go to an advanced setting "Change Time and Date Format" and change the "Short Date" setting.

Depending on which operating system you use the directions could be a little different.

Hope this helps.

1

I do not think you can change the default. It's especially awkward to fix when you import data from overseas.

The least messy ways of doing this I can think of is some sort of manual way...


Option 1: We could accept the inputs in text format and put them in the cells, this way Excel won't try to interpret them as dates. Then, in an adjacent cell we could use the function DATE together with LEFT, RIGHT, and MID. For example (specify that the user must enter the date in full MM/DD/YYYY):

Cell A1: 04/05/2015

Cell B1: =DATE( RIGHT(A1,4), LEFT(A1,2), MID(A1,4,2) )


Option 2: Request that the user enters the 'day', 'month' and 'year' into three separate cells or fields, which might be better or worse depending on your set up.

Anyhow, good luck!

  • Thought of this manual way, but I guess it's the simplest way to accomplish my problem, thanks! – Vylix Jun 9 '15 at 9:03
1

I figured it out with inspired by the answer by Lance above. You can change the windows date setting in following way (don't know how to do it in other OS):

  1. Open Control Panel
  2. Click 'Change Keyboards and Other Input Method' - Region and Language window pops up
  3. Change Short Date to mm/dd/yyyy or as you desire
  4. Change Long Date to MMMM-dd-yy or as you desire
  5. Click OK
0

Highlight the column in question, right click on the selected column, then Format Cells... Select Custom and click in the field that says General, type "dd/mm/yyyy". That should work.

  • 1
    When I mean "4 May 15" (typed in 4/5/15), Excel will recognize this as "5 April 15" because Excel takes input as "mm/dd/yy" format. I need to change the default behavior of Excel to take as "dd/mm/yyyy" – Vylix Jun 8 '15 at 6:56
  • 1
    @AdiNugroho: Excel takes the input as mm/dd/yy because that's what you've set in Control Panel, as mentioned in this answer to the question you yourself linked to. Excel has no date format preference of its own. – Karan Jun 8 '15 at 8:19
  • Can this be overidden, or at least forced into Excel so that the users won't have to change their regional settings ? – Vylix Jun 8 '15 at 8:32
  • The Worksheet_Change event in VBA could be used for this, though I'd be wary of side-stepping standard functionality unless absolutely necessary. I'm not 100% whether the change event runs before or after Excel processes the date in your local format. Assuming it runs afterwards, you could have a script pull out the date you've input, retrieve the day/month/year and swap them around. – Steve Taylor Jun 8 '15 at 10:39
  • Has anybody gotten this to work as desired?   First of all, when I try it (in Excel 2013, in the U.S.), Excel seems to interpret my raw input and convert it into internal form (as it normally would, when I type a value into a cell) and then convert it back to text for Target.Value.  … (Cont’d) – G-Man Jul 24 '15 at 3:13
0

I had the same problem when assigning a new data value to my cell using the same format it already had

12-02-2018 would be swopped to 2 dec 2018 althought the format of the target cell was in the same format.

Using ..Range("B10").Value = DateValue(newdate) did solve my problem

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.