# Excel Question - Concatenate and Dates

**URL:** <https://boards.straightdope.com/t/excel-question-concatenate-and-dates/394183>\
**Category:** Factual Questions\
**Created:** [March 1, 2007, 5:58pm UTC](https://boards.straightdope.com/t/excel-question-concatenate-and-dates/394183 "2007-03-01T17:58:41Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![UncleBeer](https://avatars.discourse-cdn.com/v4/letter/u/977dab/32.png) [@UncleBeer](https://boards.straightdope.com/u/UncleBeer)\
**Post date:** [March 1, 2007, 5:58pm UTC](https://boards.straightdope.com/t/excel-question-concatenate-and-dates/394183/1 "2007-03-01T17:58:41Z")

</div>

I have a function in my worksheet:

```auto

=CONCATENATE("[REPLY: INSPECTED ",V5,"; ",BP5,"]")

```

where cell V5 refers to a cell formatted as a date. The results of my formula are:

> [@](#):
>
> [REPLY: INSPECTED 39097; VIOLATION DUE TO EXCESSIVE SAG IN NEUTRAL; DTE SHOULD RE-TENSION SPAN]

As you can see, Excel converts the date recorded in V5 to a serial number (in this case 39097 which is 1/15/07) when placing that the value of V5 into the concatenated string. How can I force the serial number to appear as a date in the string?

Thanks.

---

<div class="post-metadata">

**Author:** ![Arnold\_Winkelried](https://avatars.discourse-cdn.com/v4/letter/a/3d9bf3/32.png) [@Arnold\_Winkelried](https://boards.straightdope.com/u/Arnold_Winkelried)\
**Post date:** [March 1, 2007, 6:22pm UTC](https://boards.straightdope.com/t/excel-question-concatenate-and-dates/394183/2 "2007-03-01T18:22:39Z")

</div>

I don’t have Excel on this computer, but did you try something like

```auto

year (v5), "/", month (v5), "/", day (v5)

```

?

---

<div class="post-metadata">

**Author:** ![dwc1970](https://avatars.discourse-cdn.com/v4/letter/d/a183cd/32.png) [@dwc1970](https://boards.straightdope.com/u/dwc1970)\
**Post date:** [March 1, 2007, 6:23pm UTC](https://boards.straightdope.com/t/excel-question-concatenate-and-dates/394183/3 "2007-03-01T18:23:42Z")

</div>

Substitute the V5 reference as follows:

**TEXT(V5,“mm/dd/yy”)**

You can adjust the formatting of the numbers accordingly.

---

<div class="post-metadata">

**Author:** ![UncleBeer](https://avatars.discourse-cdn.com/v4/letter/u/977dab/32.png) [@UncleBeer](https://boards.straightdope.com/u/UncleBeer)\
**Post date:** [March 1, 2007, 10:30pm UTC](https://boards.straightdope.com/t/excel-question-concatenate-and-dates/394183/4 "2007-03-01T22:30:12Z")

</div>

That works. Thanks DWC.

---

<div class="post-metadata">

**Author:** ![dwc1970](https://avatars.discourse-cdn.com/v4/letter/d/a183cd/32.png) [@dwc1970](https://boards.straightdope.com/u/dwc1970)\
**Post date:** [March 2, 2007, 5:51pm UTC](https://boards.straightdope.com/t/excel-question-concatenate-and-dates/394183/5 "2007-03-02T17:51:21Z")

</div>

[QUOTE=UncleBeer]  
That works. Thanks DWC.  
[/QUOTE]

Any time, glad to be of assistance.