Specific Numeration in Excel

April 11, 2019 at 05:30:11
Specs: Windows 10, 8GB
Hi everyone,
My boss gave me an Excel file that he received from someone else to put our number there and there was in the first column a specific numbering format like e.g. 1.4.1 or 6.2.7 etc. (always 3 digits separated by a comma).
There was no " ' " sign (apostrophe) before those numbers, just x.y.z...
But when I tried to change even on single digit (for ex. 1.4.1 ---> 1.4.2) then immediately the format changed to 01.04.2002 .
I also have tried to use the "copy format" tool (brush) but with no effect.
Can anyone tell me how to put the string 1.4.2 in the cell (this is the original writing as in other existing cells!) so that in the end it also looks like this?
Thanks a lot in advance!

Zennon


See More: Specific Numeration in Excel

Report •

#1
April 11, 2019 at 07:34:28
It has to do with how the cell is Formatted.

Having the cell change to a Date means the cell is formatted in a Custom Date type,
because most date formats use the slash character ( / ) as the delimiter
not the dot ( . ) at least here in the US
But, I have seen the dot used as a date delimiter, but not recently.

Change the cell format to General or TEXT and see how that works.

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Report •

#2
April 11, 2019 at 13:35:09
Mike,

I tried to Custom format a cell as m.d.y.

Problem 1:

Excel "accepts" the custom format (no error) but when I go back in to check the format, it actually set it to m.d.yy without telling me.

Problem 2:

Even though Excel forced fed an extra "y" digit in the Custom Format field (which tells me that it knows that the y means "year") it is not really a Date. Even if I change the value to 1.4.11 to fit the format, none of the MONTH(), DAY(), YEAR() functions work when that cell is referenced.

I'm not saying that you are wrong, just that I can't get Excel to accept a format of m.d.y. Even after l format the cell as m.d.yy I can't get it to act as Zennon described.

Do you know how to make 1.4.1 act be treated as a Date?

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


Report •

#3
April 11, 2019 at 17:11:13
I partially misspoke when I said it was a Custom Format,

It's actually a Windows System Default separator that allows for a dot separator
in an Excel date.

I realized the error after posting my reply, but since it was only tangentially
related to the question of changing the numbers but keeping the format
I just let it slide.

Sorry for the confusion.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
April 11, 2019 at 17:58:08
No problem, I was just confused 'cuz I couldn't get it to work.


re: "It's actually a Windows System Default separator that allows for a dot separator in an Excel date."

That's what I also thought, but I can't seem to find that setting on either of my two Windows 7 systems. I thought it was on the Regional Settings...Date tab but I don't see any place where I can can change the date separator symbol. Do you know where to find that option?

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


Report •

#5
April 11, 2019 at 19:24:16
It's a bit wonky.

In the Regional Settings,
click the Change Date Time or Number Format
Next, you need to change the Language
from English ( US ) to English ( United Kingdom )

Then in the Short Date use the pull down menu and as you
scroll down you should see the date formatted with dots.
Also offers the date with a dash

Totally different from the sequence in XP.

MIKE

http://www.skeptic.com/


Report •

#6
April 11, 2019 at 20:44:19
re: "click the Change Date Time or Number Format"

OK, I don't actually have that. What I have are these four Tabs:

Format - Location - Keyboards and Languages - Administrative

The Format Tab covers what you've described.

The first thing on the Format tab is a "Format" drop down list from which I can choose English (United Kingdom).

Once I select the UK choice, the dot delimited date choice shows up in the Short Date Drop Down.

However, it won't allow for 1.4.1 like the OP described. It forces a 2 digit year: d.m.yy. If I enter 1.4.1, I get 1.4.01

Granted, I'm the one setting this up and choosing the date format, all on the same machine. It still could be a mix-up caused by the sheet being created in one region and being opened in another. Only the OP can tell us if that's part of the issue.

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


Report •

#7
April 12, 2019 at 02:49:47
Hi friends!
Thanks a lot for your efforts to solve the issue.
For your information: in the "foreign" Excel file those cells have format "General".
There is also NO CONDITIONAL FORATTING either.
Surprisingly it seems to be "a normal" data cell.
What also may matter here, I'm working on Excel 365 under Windows 10 - while working on Excel 2010 (and under Windows 7) all is OK (!), that means I can easily put i the cell string like for ex. 3.7.2 and it stays like this after I click ENTER.
But in the "new" Excel, even if I open new file such a string results immediately in 03.07.2007 or if I change afterwords the cell format to "General" it's being converted to 37440...
Strange, isn't it?
:-(

Zennon


Report •

#8
April 12, 2019 at 03:50:49
Is the file really "foreign"? By that I mean is it being used in different regions, as defined by Excel, the OS, etc.?

Is One Drive involved?

Check out this thread. The issue sounds similar, but there are a lot of variables involved, including One Drive vs. local machine settings.

https://techcommunity.microsoft.com...

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


Report •

#9
April 12, 2019 at 05:50:21
Hi DerbyDad!
Yes, it really is "foreign" (file comes from Spain).
OneDrive was not involved here.
But OK, I have to live with it (it is not critical issue), I was just wondering how it was done...

Nevertheless thank you once again for so much effort!!
:-)
Regards

Zennon


Report •

#10
April 12, 2019 at 06:57:18
Zennon,

One final effort.
Try making your own Custom Format, something like

#.#.#

See if that will force the cell to conform, and if possible
talk to your associate in Spain and see if they will tell
you how they did it.

Good luck.

MIKE

http://www.skeptic.com/


Report •

Ask Question