CONCATENATE Day column, month column, and year column to make a date column

Hi All,
This is my first time posting here, but I really would like to seek a guru who might be able to help me.

I have 3 columns that tell me the date of the record. Which is great when I just want to sort my month. However if I want to use excel timelines or manipulate the date, then I need to somehow create a date column as Excel doesn't see the 3 columns as a date field

YEAR MONTH DAY
2015 8 9
2015 7 8

Therefore, with me trying to be clever, I create a new column and CONCATENATE the data
E.g.
=CONCATENATE(F4,"/",E4,"/",D4)

I get the following result:

9/8/2015
8/7/2015


HOWEVER - my problem. Excel doesn't recognise the change when I "format cell", and change the category from "general" to "date." **

What am I doing wrong... is there an easier way of
A) - getting Excel to recognise 3 separate columns combined as a date format?
B) - getting my CONCATENATE data category to be recognised as a date format by Excel.

** I also tried to copy the CONCATENATE data and just paste value without success.

Please also note I will continue to receive data in this 3 column date format every week with a few thousand lines of data, so I am also looking at a method of how to convert this going forward.


Your help, suggestions, guidance will be very much appreciated.

Thanks

Peter

Try

Please Login or Register  to view this content.

Let me know if you have any questions.

Dear JeteMC and HSV, Thank you so much!

Now for the bonus (sorry for being cheeky). I also have two other columns of data - Hour and AM/PM.
e.g.
HOUR AM/PM
1 AM
1 PM
2 AM

Is there a way of getting excel to recognise these two columns as a time category or does it need to be linked to the date fields that you just helped me with?

Thanks

Peter

Hi,

If you want your times separately from the dates, you can use the 'TIME' function to join your hours and AM/PM in a similar way to how you used the 'DATE' function above. Where DATE expects year,month,day - TIME expects hour,minute,second. You need to know that Excel treats hours as fractions of a day - so 0.5 formatted as time equals 12:00 noon, whilst 0.75 equals 6:00pm, etc.

If your hours are all whole hours, you can use this formula (assuming the hours are in A and AM/PM in B:

Formula:

Please Login or Register  to view this content.

The first two bits of the formula (with the ANDs) are needed to deal with 12AM being midnight and 12PM being noon.
If your hours aren't whole hours, you'll need to do some calculations on them to extract the minutes/seconds before inputting those to the TIME function. As I don't know exactly how your times are recorded, I haven't worked on that - but if you need more help, just ask.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.