Amend Symbol & With Date Formats

KuraiChikara

Board Regular
Joined
Nov 16, 2016
Messages
111
Office Version
  1. 2013
Platform
  1. Windows
Is it possible to use the amend symbol (&) with a date and keep the date format?
The purpose would be to create a unique identifier for Vlookups but the result is not user friendly.

Example:
A user could show up multiple times, their duties could show up multiple times, the only thing that would differentiate it would be the date.

If I wanted to create a unique value to perform a VLOOKUP, I would do the following with the pipe symbol (|) to separate the values but I don't like the end result of the date:

Putting in this formula: =A2&"|"&B2&"|"&C2
Yields this result: John|Lawn|43578

Is it possible to get the date shown as a date? My end goal would be this John|Lawn|04/23/2019

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]Unique Value Formula[/TD]
[TD]Name[/TD]
[TD]Duty[/TD]
[TD]Date[/TD]
[/TR]
[TR]
[TD]John|Lawn|43578[/TD]
[TD]John[/TD]
[TD]Lawn[/TD]
[TD]04/23/2019[/TD]
[/TR]
[TR]
[TD]John|Lawn|43577[/TD]
[TD]John[/TD]
[TD]Lawn[/TD]
[TD]04/22/2019[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
Code:
=A2&"|"&B2&"|"&TEXT(C2,"mm/dd/yyyy")

Note that even though it looks like a date it is actually text.
 
Upvote 0

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top