Solved how to change a date which in 16.07.14 to 16/07/2014

July 15, 2014 at 22:04:24
Specs: Windows 7
When i work in a spread sheet and copy dates from other work sheets, i could not change the dates which are in 16.07.14 to 16/07/2014

See More: how to change a date which in 16.07.14 to 16/07/2014

Report •

July 16, 2014 at 00:59:51
I'm not sure if that's a windows 7 thing or something to do with the spreadsheet. Anyway, here's some info on changing the date format in windows 7:

You should check the help topics for your spreadsheet and see what it says about changing the time and date format.

message edited by DAVEINCAPS

Report •

July 16, 2014 at 01:03:43
You have two post running on this. The second will likely be deleted by a moderator... Stick with this one... This the response (below) which I posted in the second one:

Possibly the format in the destination is set differntly to that in the original cell(s).

The original may be in plain text fromat (one of the several possible formats for acell offered). I set my date formats using text format; as that way they remain that way, and Xcel doen't try to change them to another format/style.

message edited by trvlr

Report •

July 16, 2014 at 06:26:27
✔ Best Answer
First, a comment on trvlr's response: Using a text format for dates may help to lock them into the format you desire, but it makes it difficult to do "date math" since Excel doesn't (always) recognize the text data as dates. If it works for your purposes, that's great, but it can sometimes introduce other problems. Excel can be weird when dates are involved.

As far as the OP's question, Excel doesn't recognize the data as a date, since ##.##.## is not a valid date format. (the period is not a valid delimiter for dates in Excel)

If we replace the periods with slashes, we should be OK. Try this:

With your non-date in A1, enter this in B1.


Excel should now recognize the data in B1 as a date.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03

Report •

Related Solutions

July 16, 2014 at 15:13:03
I set my date column (A) initially using one of the default formats Xcel has... All worked fine - for a while... Then one day inserting a row caused it all to fail and I couldn't get the format back... Even tried some of the other date options too - no avail..

So resorted to the text format - what you type is what you see; and it worked fine - most of the time... And then again inserting a row suddenly changed it all back to an Xcel date format option...

Still persisted and managed to get it to again accept date as text format and thus far (been a while now) it's fine...

What is odd though that in another workbook the Xcel date option I used - never had the above problems; and later I was also able to change it to a text format (which I preferred) and it's been equally fine since...

This is with Office 2011 version (for Mac).

But my knowledge of Xcel cell formatting etc. is very minimal... Which leads me to ask "date math" - not at all clear what that means/involves...?

Report •

July 16, 2014 at 19:04:32
Which leads me to ask "date math" - not at all clear what that means/involves...?

Ever need to know the number of day between two dates?
The number of hours between two times?
That's Date Math

See here for more:


Report •

July 16, 2014 at 20:07:20
'Date math' Isn't that what those rainman people are good at?

Report •

Ask Question