Can't concatenate time and date cells

[loba333]

So i'm trying to create a column, time, which will include two existing columns , time and date.

I'm trying to plot a graph of time against some variable y, the date for the time i have is split in to two columns, i have tried to concaternate them into one using =TEXT(A2,"m/dd/yy ")&TEXT(B2,"hh:mm:ss") but alas when i plot my graph it doesn't treat each cell in 'time' as an ascending value or even a value for that matter.

I trialed it out using this as a data set :

1 03/06/14 16:00:00
2 03/06/14 17:00:00
3 03/06/14 18:00:00
4 03/06/14 19:00:00
5 03/06/14 20:00:00
6 03/06/14 21:00:00
7 03/06/14 22:00:00
8 03/06/14 23:00:00
9 04/06/14 00:00:00
10 04/06/14 01:00:00

But to no vail it does not produce a linear plot as expected, can someone help me with this problem ?

Thanks you in advance

[protonLeah]

In the cells holding the concatenation formula, try: =A2 + B2 instead.

[daffodil11]

That's because the output of the TEXT function is in fact, text. You need them to be exposed to an operation to become values again.

You can multiply the expression times one to have it evaluated as a value again.

=(TEXT(A2,"m/dd/yy ")&TEXT(B2,"hh:mm:ss"))*1

Then, format the cell as date/time etc

[newdoverman]

If you are not sure what you are dealing with, select the dates and times and click on the Home tab, Number Group, then select GENERAL.

If all the dates turn into 5 digit values and the times into decimal values they are real Excel dates and times. If that is the case, click undo and you can simply add the cells together as suggested by protonLeah without further processing other than possibly formatting as date and time.

Otherwise the dates and times are text and have to be converted to real dates and times and the formula by daffodil11 will work for you.

If neither one works please upload a workbook with your problem data.

[FDibbins]

Hi, welcome to the forum

What you need to understand about dates and times in excel is...

a date is just a number representing the number of days passed since 1/1/900...and then formated in a way that we recognise as a date

Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75