So an odd question, maybe really easy. I have a large spreadsheet, with a few columns that will be filled in by various users as time goes on. Currently those are date columns, however, this question isn't restricted to dates.

Once I enter some dates, I noticed that they are formatted in a format I'd rather not use (ie my default Windows format) :( Anyway, I'd like to use a standard, default format (ie dd-mmm-yyyy) to avoid any confusion between dates like "07/08/09" O.o

I can set that format on the entire column(s), however, the format doesn't seem to "stick" to blank cells. Once I go in and enter a new value into a cell that was empty, it doesn't use the format I specified.

Is there a way to force Excel to set the default format for the ENTIRE column, blank cells or otherwise ?

  • I've tried to reproduce your situation (format a whole empty column with a custom date format, then enter dates manually), but didn't succeed. For me it works correctly, each manually entered date is formatted according to my previously selected format. Could you please add more details to your question? (some screenshots, is it wrong always or after a time; do your colleagues enter dates manually or insert from somewhere else, what exactly they type...) – Máté Juhász Jun 23 '16 at 14:13
  • A detail not mention is if the various users are doing a copy/paste for data entry. They could be overwriting the format for the cell when they do a paste. – bvaughn Jun 23 '16 at 15:38
  • Yeah, I've encountered this issue on/off for a while, and haven't been able to really pinpoint what I'm doing wrong. Was just hoping somebody had insight. It sounds like it should behave like I believe it should, so I'll play with it more until I can narrow a good test case down. Thanks! – Ditto Jun 24 '16 at 14:25

If you enter a date in a previously blank cell, Excel tries to be ultra-clever, and formats the date according to your OS' standard format (ignoring the format you already previously gave the cell). You can see which format this is in the Excel Custom formats list; it is the one marked with an '*'.

I am not aware of any way to change this behavior, so your only solution would be to change you OS standard date format (if you like this other format better, that might be a useful change anyway). In Windows, this is in Control Panel\Clock, language, and region\Change date time or number formats\Short date.

Note that any field in Excel that is formatted this way will show on other people's computer according to their OS' setting. So whatever it looks on your machine, it will - potentially - look different on everyone else's machine. All formulas will work fine; this is just the display.

  • Understood, however, before entering data, I've set the format to the entire column .. so it should NOT be using the O/S format at this point. It should use the format I specified. I'm trying to re-create specific case – Ditto Jun 24 '16 at 14:24
  • Yes, it should, in your opinion. In my opinion too. Microsoft thought different though. – Aganju Jun 24 '16 at 14:28

Tested in Office 2010, right click the column label and choose format cells. I did this with an entirely blank column and all of them are reformatting to correct date format. That is to say if I enter 6-23-16 it corrects it to 23-Jun-2016.

This also works in Office 2016.

Edit:You could try Protecting the Sheet from the Review Tab. Before you do this you will want to Click the Allow Users to Edit Ranges, name the range as a note, select the range that needs to be modifiable, then Protect Sheet. This will lock the formatting of cells. Not sure if it will solve the problem but it's another option.

  • Yeah, this is how I'm changing the format, however, it doesn't appear to always "stick". Trying to simplify/re-create the issue isn't working, so I'll play with it some more until I can figure out what I'm doing "wrong" :) – Ditto Jun 24 '16 at 14:22
  • Got it, sorry for the repeat. At this point I'd have to assume like the others comments that other users are affecting the workbook. I'll see if I get time to test this on a couple computers. – Cand3r Jun 24 '16 at 14:24
  • So far in my tests, it's only me. no other users have touched it and I've seen the behaviour. :) I need to narrow down what I'm doing that's messing with it. :) – Ditto Jun 24 '16 at 14:26

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.