Date format problem (5.8.14)

Solved/Closed
zopfan
Posts
7
Registration date
Wednesday July 11, 2012
Status
Member
Last seen
August 20, 2018
- Jan 13, 2014 at 07:01 AM
 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?

4 replies

TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Jan 13, 2014 at 12:08 PM
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
0
zopfan
Posts
7
Registration date
Wednesday July 11, 2012
Status
Member
Last seen
August 20, 2018
7
Jan 14, 2014 at 01:33 AM
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.
0
TrowaD
Posts
2880
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
May 2, 2022
509
Jan 14, 2014 at 10:22 AM
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
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.
0