I have a file in which I concatenate two fields, the vendor and the date.

it shows as 40255-3042
I would like 20100318-3042

Here's my formula: =concatenate(O2,"-",B2)

Can anyone help me format the date field I am picking up in column O2?

=text(o2,"yyyymmdd")&"-"&b2
or maybe...
=text(o2,"yyyymmdd")&"-"&text(b2,"0000")

To make sure that that vendor is 4 digits, too.

pm wrote:

I have a file in which I concatenate two fields, the vendor and the date.

it shows as 40255-3042
I would like 20100318-3042

Here's my formula: =concatenate(O2,"-",B2)

Can anyone help me format the date field I am picking up in column O2?

--

Dave Peterson

That works excellent, thanks! I tried using the text function too, but
couldn't get it to work. Thanks again for your help!

"Dave Peterson" wrote:

=text(o2,"yyyymmdd")&"-"&b2
or maybe...
=text(o2,"yyyymmdd")&"-"&text(b2,"0000")

To make sure that that vendor is 4 digits, too.

pm wrote:

I have a file in which I concatenate two fields, the vendor and the date.

it shows as 40255-3042
I would like 20100318-3042

Here's my formula: =concatenate(O2,"-",B2)

Can anyone help me format the date field I am picking up in column O2?

--

Dave Peterson
.

Sure, I can definitely help you with that!

The number you see in column O2 is actually the serial number that Excel uses to represent dates. To convert it to the format you want (yyyymmdd), you can use the TEXT function in Excel.

Here's the formula you can use:

1. =CONCATENATE(TEXT(O2,"yyyymmdd"),"-",B2)

Let me break it down for you:

- The TEXT function takes two arguments: the value you want to format (in this case, O2), and the format you want to apply (in this case, "yyyymmdd").
- The "yyyymmdd" format tells Excel to display the year, month, and day in that order, without any separators.
- We then concatenate the formatted date with the hyphen and the value in column B2, just like you were doing before.

So if O2 contains the date March 18, 2010, the formula will output "20100318-3042".

I hope that helps!