Results 1 to 6 of 6

Thread: Formula pulls in date incorrectly - help please!
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Sep 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Question Formula pulls in date incorrectly - help please!

    Hey everyone!

    I used this formula to add spaces after my text as I am working on a file upload where each field must be a certain length (see formula below):

    ='Sheet2'!D5&REPT(" ",16-LEN('Sheet2'!D5))

    I have the above formula pulling from a different sheet so I wondered if that was causing the error. No matter how I format the date on either spread sheet it pulls as follows (see below):

    The date on sheet2 is: 19981008 (the file requires the date as YYYYMMDD), but the above formula pulls in the date as: 3607. I'm currently stuck. I need the field to remain 16 characters so I don't want to mess with the above formula too much. Unless there's something I can add to the formula to tell it to pull the data a certain way?

    Any help would be GREATLY appreciated!

    Thanks in advance,

    HR

  2. #2
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    230
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Formula pulls in date incorrectly - help please!

    Try=TEXT(Sheet2!D5,"YYYYMMDD ")

    I suspect you're actually seeing 36076 as that's the number of days from 1 Jan 1900 to 8th October 1998, which is how Excel holds dates internally.
    Life advice found on a book of matches: "Keep cool. Keep away from children."

  3. #3
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,010
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Formula pulls in date incorrectly - help please!

    Try

    =TEXT(Sheet2!D5,"yyyymmdd")&REPT(" ",16-LEN(TEXT(Sheet2!D5,"yyyymmdd")))

    Edit: Actually, because we are formatting the date to 8 characters, this should do

    =TEXT(Sheet2!D5,"yyyymmdd")&REPT(" ",8)

    Edit again: Ah - I think Toadstool had it in the first place but the extra spaces have been eliminated by the forum software

    =TEXT(Sheet2!D5,"yyyymmdd        ") <- 8 spaces here
    Last edited by Peter_SSs; Sep 17th, 2019 at 07:08 PM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  4. #4
    New Member
    Join Date
    Sep 2019
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool Re: Formula pulls in date incorrectly - help please!

    Quote Originally Posted by Peter_SSs View Post
    Try

    =TEXT(Sheet2!D5,"yyyymmdd")&REPT(" ",16-LEN(TEXT(Sheet2!D5,"yyyymmdd")))

    Edit: Actually, because we are formatting the date to 8 characters, this should do

    =TEXT(Sheet2!D5,"yyyymmdd")&REPT(" ",8)

    Edit again: Ah - I think Toadstool had it in the first place but the extra spaces have been eliminated by the forum software

    =TEXT(Sheet2!D5,"yyyymmdd ") <- 8 spaces here
    Thank you both so much for the quick response! The formula posted worked perfectly. I look forward to learning and contributing when possible on this forum!

  5. #5
    Board Regular Toadstool's Avatar
    Join Date
    Mar 2018
    Posts
    230
    Post Thanks / Like
    Mentioned
    9 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Formula pulls in date incorrectly - help please!

    Quote Originally Posted by Peter_SSs View Post
    Edit again: Ah - I think Toadstool had it in the first place but the extra spaces have been eliminated by the forum software
    =TEXT(Sheet2!D5,"yyyymmdd ") <- 8 spaces here
    Grrr! Yes, that forum software beat me again.

    Code:
    =TEXT(Sheet2!D5,"YYYYMMDD        ")
    Life advice found on a book of matches: "Keep cool. Keep away from children."

  6. #6
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,010
    Post Thanks / Like
    Mentioned
    90 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Formula pulls in date incorrectly - help please!

    Quote Originally Posted by HR_Excel_User View Post
    Thank you both so much for the quick response! The formula posted worked perfectly. I look forward to learning and contributing when possible on this forum!
    No problem. Glad you got a successful outcome.

    BTW, "Welcome to the MrExcel board!"

    @Toadstool
    You can also overcome the issue of lost spaces in the forum by using repeated &nbsp; instead of repeated normal spaces
    Last edited by Peter_SSs; Sep 17th, 2019 at 07:35 PM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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