+ Reply to Thread
Results 1 to 7 of 7

Remove dots from date values

  1. #1
    Registered User
    Join Date
    05-27-2020
    Location
    Hungary
    MS-Off Ver
    2016
    Posts
    6

    Remove dots from date values

    Hello!
    Is there a way to remove the dots from excel values?

    I have a column with full of dates like this "2023.12.14", and i want it to look like this 20231214 in the column next to it.

    It is in date format. I've read in other forums about the use of Subsitute function which when i use like this =SUBSTITUTE(A2;".";"") it gives me 45274 back. Then i tried using =DATEVALUE function on the number i got but it gives back #VALUE! error.
    When i try using the TEXT function like this : =TEXT(A2;yyyymmdd) OR like this : =TEXT(A2;yyyy-mm-dd) it gives back yyyy00dd and yyyy-00-dd as the value.

    is there a way doing it without the replace menu and without having support colums?

    Thank you!

  2. #2
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,229

    Re: Remove dots from date values

    Sounds like they are real dates so you can change the cell format to "yyyymmdd"

  3. #3
    Registered User
    Join Date
    05-27-2020
    Location
    Hungary
    MS-Off Ver
    2016
    Posts
    6
    Quote Originally Posted by Fluff13 View Post
    Sounds like they are real dates so you can change the cell format to "yyyymmdd"
    Ye sorrry i forgot to tell that i need only the month and day, so in this case 1214. When i do the thing you said it works yes, i get the result(20231214) but i can't work with it because when i try to cut the 1214 off the end, the result comes back in the serial number form, like i cut it off the serial number not the actual date and i get something like 3457 instead of 1214 and so the problem still persists, since again i can't convert that number to the actual month day value.

  4. #4
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,229

    Re: Remove dots from date values

    In that case you will need to use
    Formula: copy to clipboard
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    05-27-2020
    Location
    Hungary
    MS-Off Ver
    2016
    Posts
    6

    Re: Remove dots from date values

    My god i was dumb, i got wrong values because i literally used it like my excel is in english even though its in my native language...Thank you! This is what i was looking for anyways!

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2407 (Windows 11 23H2 64-bit)
    Posts
    84,036

    Re: Remove dots from date values

    If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

    Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  7. #7
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,229

    Re: Remove dots from date values

    Glad to help & thanks for the feedback.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] How to Remove Dots from Labels
    By g.costapinto in forum Excel Charting & Pivots
    Replies: 5
    Last Post: 05-03-2020, 07:24 AM
  2. Replies: 5
    Last Post: 01-02-2016, 10:51 AM
  3. Remove duplicate values based on a date criteria
    By nomis6565 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 06-20-2013, 01:47 AM
  4. [SOLVED] In a text formula I need to have the numeric values with dots for thousands & 2 decimals
    By spiros63 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-05-2012, 09:19 AM
  5. Need long space, dots or dashes b/w two values
    By fassi in forum Excel General
    Replies: 7
    Last Post: 11-24-2011, 06:22 PM
  6. Macro to remove duplicates values and keep unique values bases on multiple criteria
    By Ricker090 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-16-2011, 04:37 PM
  7. Series with ea 2 values as single dots
    By djcampoverde2 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 05-02-2005, 10:43 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1