Combining "date and month" in one column with year in another column

[Chomeske]

Hi Everyone

I was given an excel file to work on the other day. It had a column with date and month, and another column with year. I ended up separating the date and month first and then used a DATE to put them all back together. I want to know if there was a smarted way of doing this. Is there a formula or trick that would have helped me combine "27/01" in column A with "2017" in Column B to get "27/01/2017" in column c?

Thanks much

[Mazzaropi]

Chomeske, Good morning.

Probably the 27/01 at column A is TEXT format.
2017 at column B can be a TEXT or a NUMBER.

Try to use:

C1 -->

Please Login or Register  to view this content.

Remember to format C1 as date.

Is that what you want?

I hope it helps.

[Pete_UK]

You can use this in C1:

=DATEVALUE(A1&"/"&B1)

Format as a date in the style you prefer, then copy down.

Hope this helps.

Pete

[63falcondude]

You can do it in one shot like this:

C2 =(A2&"/"&B2)+0

If this doesn't work due to your regional settings, you can use this:

C2 =DATE(B2,LEFT(A2,2),RIGHT(A2,2))

[Chomeske]

Probably the 27/01 at column A is TEXT format.
2017 at column B can be a TEXT or a NUMBER.

Thanks for your quick reply and good morning to you too!

No, unfortunately this does not seem to be working. The format in column A is date and is set to show it as, for example, "27-Jan".

I am sorry, this is my first post here and am having issues with the basics and navigating and commenting :/

[63falcondude]

Try this in C2:

=DATE(B2,MONTH(A2),DAY(A2))

If this doesn't work, upload a small representative sample sheet.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

[Chomeske]

Try this in C2:

=DATE(B2,MONTH(A2),DAY(A2))

If this doesn't work, upload a small representative sample sheet.

To upload an Excel workbook, follow these steps:
1) Click on "Go Advanced"
2) Click on "Manage Attachments"
3) Click on "Choose File"
4) Choose your file and click on "Open"
5) Click on "Upload"
6) Click on "Close this window"

AMAZING, this worked

Thanks heaps.

[Chomeske]

Thanks for your quick reply.

[63falcondude]

You're welcome. Glad we could help.