Excel 2013 help pls?

[blue-eye-boy]

Hey everyone, me again asking excel help.

I have a few spreadsheets, all with lots of data. There is columns, (day, month, year, product, weight, price). In the first 3 columns is the day, then month, then year. I need to combine those into one column with a complete date (1-jan-2015). How can I do this automatically please? I googled a lot for this, but cant find the right answer.

Thanks!

 

[MrR]

Use the formula, =date(year, month, day)

 

[blue-eye-boy]

Use the formula, =date(year, month, day)

Will this work with the day in column 1, month in column 2 and year in column 3?

I want to replace the 3 columns with one date column, then delete the 3 "old"one.

 

[nemisiz]

https://support.office.com/en-US/Ar...one-cell-94700285-8458-469b-aae9-394451a1045c

and change cell properties to date.

 

[powermzii]

Could also use the concatenate () function to enable addition of the "-"

 

[MrR]

Note, if the month is written out, e.g January or Jan, you will first need to do another step to get the numerical representation of the month.

Create a new sheet
In column A, list the months from January to December.
In column b, list the corresponding numerical value, e.g 1 for January, 2 for February, etc

In your original sheet, assuming column a is day, b is month and c is year:
Add a new column next to c - column D and name it month formatted
Use the formula =vlookup(b2,sheet2!$a$2:$b$13,2,false) this will return the numeric value

Add a new column next to d, column E and name it date.
Add formula =date(c2,d2,a2)

Should now have your full date

 

[themba990]

Will this work with the day in column 1, month in column 2 and year in column 3?

I want to replace the 3 columns with one date column, then delete the 3 "old"one.

Only if the month is numeric

 

[alphabyte]

Year is in cell A1, month is in B1, day is in C1, then in (say) D1 put the formula =DATE(A1,B1,C1)
EDIT: okay, you guys beat me to it.

 

[MrR]

Will this work with the day in column 1, month in column 2 and year in column 3?

I want to replace the 3 columns with one date column, then delete the 3 "old"one.

See previous post above. Once you have the format down, copy the whole column (ctrl+c) and paste special values (alt+E, s, v). This will replace the formula with the static values. You can now delete all the other fields

 

[blue-eye-boy]

Thanks guys, will try this and let you know...

 

[LPCPT]

This is what I would do.
Import to database (sql express)
Fix
Export

 

[blue-eye-boy]

Thanks a million, got it right this time!

 

[MrR]

Thanks a million, got it right this time!

Great stuff. Just for your own benefit, can you confirm that the values are in a date format? If it's not and you want to sort on date (earliest to latest) or do some form of calculation (aging) you would get errors.

 

[blue-eye-boy]

Great stuff. Just for your own benefit, can you confirm that the values are in a date format? If it's not and you want to sort on date (earliest to latest) or do some form of calculation (aging) you would get errors.

Jip they are. Thanks a mil