Formula pulls in date incorrectly - help please!

[HR_Excel_User]

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

 

[Toadstool]

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.

 

[Peter_SSs]

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

 

[HR_Excel_User]

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!

 

[Toadstool]

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.

=TEXT(Sheet2!D5,"YYYYMMDD        ")

 

[Peter_SSs]

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   instead of repeated normal spaces