Combine numbers in three Excel cells into date

[benzhaotb]

Hi
I have a data that has dates in A1 yy, A2, month, A3, day. How do I combine them into mm/d/yy in one cell?

 

[xlbo]

=A2&"/"&A3&"/"&A1

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[AnotherHiggins]

=Date(A1, A2, A3)

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

Help us help you. Please read FAQ 181-2886 before posting.

 

[GlennUK]

... and format the cell as date.

Cheers, Glenn.

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

 

[AnotherHiggins]

benzhaotb,

FYI: I think Glenn's post was in response to Geoff's. You don't need to change format when using Date function.

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

Help us help you. Please read FAQ 181-2886 before posting.

 

[benzhaotb]

Thanks. It worked.

 

[xlbo]

Hi John - doubt it. Mine just converts to a string so no need to format it...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[GlennUK]

Hi Geoff,

my fault. Yours would have needed it had it been converted to a date serial, but it wasn't. ( reading too quickly again )

Cheers, Glenn.

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

 

[combo]

benzhaotb,
check what century do you get with John's formula. For me =Date(1, 2, 3) returns Feb. 3, 1901, so I need to add 2000 to the year (assuming I need this century)...

combo

 

[SkipVought]

You might want to gain an understanding about Date/Time in Excel, because Excel makes a few assumptions about your data...

faq68-5827

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}

 

[AnotherHiggins]

Oops. I missed that it's a two-digit year. Thanks for picking that up, combo.

So for mine it should be
[tab][COLOR=blue white]=Date(A1 + 100, A2, A3)[/color]
to return 200X.

Or if you are dealing with data from the 1900s and the 2000s, you'll need to come up with a way to decide whether to add 100 or not, like If formula. Something like:
[tab][COLOR=blue white]=Date(if(A1 < 11, A1 + 100, A1), A2, A3)[/color]

But Skip is absolutely right (as he almost invariably is) in that you should understand how Excel deals with dates/times.
- - - - - - - -

[Purely academic curiosity]

Mine just converts to a string so no need to format it...

Even though you're returning a string, Excel seems to recognize that it is a date. Let's say that A1=1 A2=2 A3=3.

In B1, type in [COLOR=blue white]=A2&"/"&A3&"/"&A1[/color]
B1 now shows "2/2/1"

In another cell, put in [COLOR=blue white]=B1+1[/color]
That returns 36925, the serial number for 2/3/2001.

If you change the formatting of B1 to Date it does not change the appearance of cell B1. But if you then put [COLOR=blue white]=B1+1[/color] in a different cell (after having changed the format of B1), it will return 2/3/2001 straight away.

I just thought that was interesting. But I'm probably wrong.

[/Purely academic curiosity]


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

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

"...But if you then put =B1+1 in a different cell (after having changed the format of B1), it will return 2/3/2001 straight away....I just thought that was interesting. But I'm probably wrong."

I also had done the same thing, out of academic curiosity, and thought it was interesting.

So I must ALSO be wrong!!! ;-)

Skip,

_{Did you hear what happened when the OO programmer lost his library?...
He's now living in OBJECT poverty!}