Excel: Date Problem

[krets]

If I have a date in a cell, how can I force Excel to use the date as the value? For instance, if a cell says 10/30/2006 I want that to be the value, not 39020.

What I would like to do is be able to use the LEFT and RIGHT functions to generate a unique key for each record in a long list that would contain some other vales besides the date.

For example:

Job Code | Date Hired | Division
Tech89 | 10/30/2006 | PIA

Then I want to create a formula that would grab those values and create something like:

PIATech89102007

I know how to do everything but fix my date issue.

Any ideas?

 

[onedtent]

you mean =C2&A2&LEFT(B2,2)&RIGHT(B2,4) doesn't work for you? i just tried it seems to work

 

[GlennUK]

You can generate the string version of any true date by using the TEXT function, and embed in a concatenation like this:

=C2&A2&TEXT("MMYYYY")
or
=C2&A2&TEXT("MMDDYYYY")

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[krets]

Nope, what I end up with is something like:

PIATech89388628

 

[krets]

Sorry, the above message is to onedtent.

Glenn: The problem I am having is that the data given to me came from a query that seems to have had some issues. Some dates look like MM/DD/YYYY and some have a single quote like 'MM/DD/YYYY and that is causing me some trouble.

I created a function to chop off the ' but that hasn't helped me much.

 

[onedtent]

to use text function hightlight all your dates, then use data, text to columns, finish to make then all same.

 

[AnotherHiggins]

This is a common problem with imported data. The cause is that the data is formatted as text. In your case, starting a cell off with a single quote tells excel to treat that cell as text.

Two common methods to fix this are using Text To Columns and the Times 1 Fix.

The Times 1 Fix is accomplished like this:

[ul][li]In a cell to the right of all the imported data, type [blue]1[/blue][/li]
[ul][li](this should be right-aligned, indicating that the cell is formatted as a number)[/li][/ul]
[li]copy that cell[/li]
[li]select the cells that are giving you trouble^[red]*[/red][/li]
[li]Go to Edit > Paste Special > Multiply[/li]
[li]Now try applying a new format to those cells [/li][/ul]

Once all cells are properly formatted as numbers, then you can use the TEXT function as described by Glenn.

^[red]*[/red] Note that any null cells that you select will be converted to zeros. For this reason I recommend that you don’t select an entire row/column.



[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.