3

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

  • 2
    Are you sure specifying a custom number format of mm-dd-yy doesn't work for you? – misha256 Feb 11 '15 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 '15 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 '15 at 23:08
  • @David Glad to hear it works, I have turned my comment into an answer :-) – misha256 Feb 11 '15 at 23:10
4

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.

  • Thanks, I appreciate your help and answer. Saved me a headache. – David Feb 11 '15 at 23:12
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 :)

  • 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 '15 at 15:29
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).

  • 1
    Changing the system settings to use a different date format seems overly heavy handed – Burgi Apr 12 '16 at 12:22
  • 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 '16 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 '16 at 17:28
  • @ DavidPostil: I believe it is a pretty basic answer to a pretty basic question :) – Wagemut Apr 13 '16 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

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.