Date format problem (5.8.14) [Solved/Closed]

zopfan 6 Posts Wednesday July 11, 2012Registration date January 14, 2014 Last seen - Jan 13, 2014 at 07:01 AM - Latest reply:  zopfan
- Jan 14, 2014 at 11:40 PM
Hi. I'm using excel 2013 on Win7 Ultimate SP1. My problem is that I often type the dates with the help of decimals/periods. But I've learned thru google that excel doesn't accept them as date anyhow (e.g. 05.08.2014). I.e. we can't force excel to format the same as date.

Is there any way/solution to this?
See more 

4 replies

TrowaD 2395 Posts Sunday September 12, 2010Registration date July 17, 2018 Last seen - Jan 13, 2014 at 12:08 PM
0
Thank you
Hi Zopfan,

Well the solution would be to change "." into either "-" or "/".
You could use find/replace for that (Ctrl+H).

Not sure what kind of answer you expected.

Best regards,
Trowa
zopfan 6 Posts Wednesday July 11, 2012Registration date January 14, 2014 Last seen - Jan 14, 2014 at 01:33 AM
0
Thank you
Thank you for your kind and immediate reply.

But, I've already written in my query that "My problem is that I often type the dates with the help of decimals/periods..." and you've replied that I should use hyphens or slashes. Well I already knew that. But was wondering why excel won't allow us dots as date separators. After all there are many countries (India being one of them) where this is a normal practice.

Work arounds like Ctrl+ H I already know. But they're not practical enough.
TrowaD 2395 Posts Sunday September 12, 2010Registration date July 17, 2018 Last seen - Jan 14, 2014 at 10:22 AM
0
Thank you
That's why I wasn't sure what answer you expected.

Could you give an example of what you want to do with the dates if Excel did recognize your values as dates?

e.g. if you want to know how much days are between 05.08.2014 (A1) and 10.08.2014 (A2) you could use =LEFT(A2,2)-LEFT(A1,2).
0
Thank you
I just wanted that dates should behave like, well, dates.
Excel should take them as dates and I should be able to change their format.

And I've been able to solve much (I'll say around 70%) part of the problem.

My problem was that I was often in the habit of typing dates with dots in between the days and months and years (like 15.11.14), but excel won't accept them as dates unless I replace the dots with hyphens of the like. But I found that if I replace the hyphens or slashes with dots in the OS date format (in the Control Panel> Regional Settings), excel starts doing what I wanted. Even if I put dot in the first part in Regional Settings (like: dd.MMM-yy), even that did the trick.

Thank you very much for being with me.