+ Reply to Thread
Results 1 to 7 of 7

Formatting date with space at the front

  1. #1
    Registered User
    Join Date
    09-20-2018
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    6

    Formatting date with space at the front

    Hello excel grand masters,
    Looking for help in formatting a cell;
    The cell contain the date with a space at the front " spaceD-MM-YY"

    I know I can use "=Trim", however the challenge is NOT to insert another column.
    I have also tried using custom formatting the cell; D/MM/YY & DD/MM/YY Etc. Etc....., it seems not to change the format.

    How can I format this cell location so that when the data is copied directly into excel it recognises "spaceD-MM-YY" as a date.

    If at all possible?
    Without having to do extra formatting, converting, copy & pasting or other excel functionality.

    All your views and replies are greatly appreciated.

  2. #2
    Forum Contributor
    Join Date
    03-30-2016
    Location
    Gillette, WY
    MS-Off Ver
    Office 365
    Posts
    230

    Re: Formatting date with space at the front

    I dont think this is actually possible, sans VBA code. Excel encodes dates as serial numbers so they can be used in formulae. Any time a date is concatenated with any other character, even a space, Excel returns the date as the encoded serial number that it corresponds to. Could you attach a sample?

    Go Advanced>Manage Attachments>Upload File>Close Window>Submit Reply

  3. #3
    Forum Contributor
    Join Date
    03-30-2016
    Location
    Gillette, WY
    MS-Off Ver
    Office 365
    Posts
    230

    Re: Formatting date with space at the front

    Why is the leading space so important?

  4. #4
    Registered User
    Join Date
    09-20-2018
    Location
    Sydney
    MS-Off Ver
    Office 365
    Posts
    6

    Re: Formatting date with space at the front

    Thank you for your input TFiske

    Reason why I'm trying to achieve this is that, the users that will paste the date have no knowledge of Excel what so ever.
    (Regardless of how many times we keep showing them. Their view is that, anything more than copy & paste, then it's too complicated for them and are unwilling to learn). I have been tasked with finding a solution by managers

    The space is a consequence of downloading the data from one of the systems, when the date falls from 1st to the 9th

    I have attached a sample file

    Thanks again for any assistance, very much appreciated
    Attached Files Attached Files

  5. #5
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Formatting date with space at the front

    Here is a VBA solution

    Please Login or Register  to view this content.
    How to install your new code
    1. Copy the Excel VBA code
    2. Select the workbook in which you want to store the Excel VBA code
    3. Press Alt+F11 to open the Visual Basic Editor
    4. Choose Insert > Module
    5. Edit > Paste the macro into the module that appeared
    6. Close the VBEditor
    7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

    To run the Excel VBA code:
    1. Press Alt-F8 to open the macro list
    2. Select a macro in the list
    3. Click the Run button
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  6. #6
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Formatting date with space at the front

    If you have a space in front of your date, then you actually dont really have a real date, you have text, looking like a date - and no amount of formatting will change that.

    What you need to understand about dates and times in excel is…

    a date is just a number representing the number of days passed since 1/1/900...and then formatted in a way that we recognize as a date. So, for instance, today (Wed 03 Oct 2018) is actually 43376

    Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  7. #7
    Forum Contributor
    Join Date
    06-01-2014
    Location
    Riyadh, Saudi Arabia
    MS-Off Ver
    Microsoft 365
    Posts
    224

    Re: Formatting date with space at the front

    Just Find and replace the Space with blank after the data Paste. the Cells will convert to number and the dates will appear.

+ 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] insert space in front of any question, if a space is not present
    By OhioRuss in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-19-2018, 06:45 PM
  2. [SOLVED] Removing a space that is in front of my date
    By RJL3313 in forum Excel General
    Replies: 2
    Last Post: 06-17-2015, 11:35 AM
  3. Replies: 4
    Last Post: 10-30-2013, 01:58 PM
  4. [SOLVED] Help Please! Cant understand what is this space in front of values
    By dimitrov_dimityr in forum Excel General
    Replies: 4
    Last Post: 10-21-2013, 06:55 AM
  5. How to remove a space in the front
    By gurp99 in forum Excel General
    Replies: 12
    Last Post: 01-30-2011, 05:21 PM
  6. Macro to delete space in front of date
    By Neutron1871 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 03-29-2005, 07:06 PM
  7. get rid of space in front of a word
    By CENorth7272 in forum Excel General
    Replies: 2
    Last Post: 02-01-2005, 10:19 AM

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