6

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. :)

5
  • 2
    Are you sure specifying a custom number format of mm-dd-yy doesn't work for you?
    – misha256
    Feb 11, 2015 at 22:57
  • Thanks, new to Excel. I didn't know that would have to be custom. Seems ordinary. Not sure what 'General' is for, seems like what ever I type should just stick unless I choose a different option. But it worked.
    – David
    Feb 11, 2015 at 23:06
  • 1
    General does its best to guess what you want. If you put in something that looks like a date it will input it as a date and use the basic date format it defaults to, If you put in 31% it will convert it to 0.31 (which is displayed as 31%), etc
    – gtwebb
    Feb 11, 2015 at 23:08
  • @David Glad to hear it works, I have turned my comment into an answer :-)
    – misha256
    Feb 11, 2015 at 23:10
  • Excel is using the date format you chose - your setting in Windows itself. You could either Windows to match your preferences, or use a custom date format in Excel.
    – Ian Boyd
    Sep 11, 2020 at 20:10

8 Answers 8

6

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.

3
  • Thanks, I appreciate your help and answer. Saved me a headache.
    – David
    Feb 11, 2015 at 23:12
  • I ended up having to use "text" because even with date formatting set it would show a different value in the formula bar than the cell. I didnt trust it. Needed for data import to sql database. May 24, 2019 at 14:54
  • It automatically changes my format from 'yyyy-mm-dd' when I edit the cell. So specifying custom number format doesn't work.
    – anton_rh
    Aug 14, 2020 at 11:21
1

I did not want to have to use formulas for a simple table where I would be entering task start and finish dates. My frustration was relieved when I realized that the issue was with my Windows 10 Language & region settings which Excel uses to determine default date formats.

I navigated to Settings -> Time & language -> Language & region -> Regional format

I could see that my "Short date" format was using European dates, while the "Long date" was using US formats. I clicked the "Change formats" button and updated my short date format to the US format. Once I did this all was right with the world.

0

I have just answered this on another page and hoped this may help, if you are having this issue on Windows 10 then the best (and complete) solution is to install the "English (United Kingdom)" language pack.

This can be found by going to: Control Panel [make sure you select 'view by: Small icons' in the top right so you can see all items] then select 'Language'. Here you will see the default "English (United States)" option with "enabled" against it and should see the "English (United Kingdom)" option below. [If not you should be able to find it via the 'add a language' button] Click on options and then the "Download and install language pack button"

Just wait for it to install then highlight the recently installed option and click the 'move up' button so that this becomes your default. It should display "Will be enabled on next sign-in". It will do exactly that, after a reset it will be enabled and should be your default.

This will then give you the wider variety of date options i.e. dd/MM/yy and d/M/yy.

Hope this helps :)

0

the best way to get what you want, ie: numbers staying the exact way you type them is to put this in front of what you type. ' then number. ie: '03/11/2015 It should always stay, no matter how the rules want to work for themselves. Sometimes you can enter a rule and it still doesn't work. hope this helps :)

2
  • 1
    I wouldn't describe this as the best way. By using ' you convert the number into a text string. This means it's no longer a number and you can't use it in calculations or in Pivot Table groups, and also charts don't pick up the fact this cell is a date.
    – Andi Mohr
    Nov 3, 2015 at 15:29
  • Although Andy is right, if you use Excel with large data sets and you use pivot tables where cells with dates are part of the pivot criteria and dates cells are not used in time period computation, then the technique of transforming the dates into text will do the trick. It's just too bad that Excel does not distinguish dates from numbers internally. It's also too bad that ISO-8601 date format is not used much more often.
    – PRouleau
    Sep 3, 2021 at 19:11
0

Excel relies on the time and regional settings/preferences of your operating system to analyze data. Thus, if you have "European times" as preferences, it will consider your set of data format as being "European" (no matter that it is a US format, Excel does not know it).

So the trick is just to change your time and regional settings/preferences to the US ones.

  • For Windows, go to "Control panel" -> "Region and language"
  • For MAC, go to "System Preferences" -> "Date and Time" -> Open "Language and Region"

Once you have done this, close your Excel format and then reopen it to make sure that these changes will apply to your data set.

After this, depending of what you want to achieve, you can apply different formula to convert your data (column A) into the right date format:

  • in another column (column B) apply the formula =DATEVALUE(A)
  • if only the dates appear (and not the hours), add in a new column (Column C) the formula =TIMEVALUE(C) and finally add both in a last column (Column D) with the formula =B+C.

Normally, it should automatically be considered by Excel as a date. You can check this by applying a filter to your data (it will be classified by years, months and days).

4
  • 2
    Changing the system settings to use a different date format seems overly heavy handed
    – Burgi
    Apr 12, 2016 at 12:22
  • 1
    This is a very poor answer, unless you can explain why changing the OS system settings (which would probably break a lot of other things) is a better answer than using a custom number format in excel itself.
    – DavidPostill
    Apr 12, 2016 at 12:52
  • @ Burgi: I agree, it is heavy. However you can come back to your previous settings once you have stopped editing your document.
    – Wagemut
    Apr 13, 2016 at 17:28
  • @ DavidPostil: I believe it is a pretty basic answer to a pretty basic question :)
    – Wagemut
    Apr 13, 2016 at 17:31
0
  1. Do this before you even start putting numbers in or you will have to go back and change everything.
  2. Move over to the column you want to put your dates in
  3. Right Double Click at the top of the column.
  4. Go Down to "FORMAT CELLS"
  5. Highlight TEXT
  6. Then click OKAY
1
0

Try this:- Set format of cell to text and enter date. Now change the format of cell to general. The cell does not automatically change the format to date, but leaves it as general.

0

The data format changing after pasting it into an Excel 2013 sheet can be stopped this way.

  1. Click on the File menu.
  2. Select Options.
  3. Select Proofing.
  4. Revise the AutoCorrect Options and deactivate what is not necessary.
  5. Unselect options in the list of "When correcting spelling in Microsoft Office programs".

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .