After concatenating data, how do I get Excel to recognise date?

[Guest]

I have used the "concatenate" function to put together data from 4 separate
columns to form a date & time entry in the next column (the original 4
columns came from a .cvs file) on the basis of dd/mm/yyyy hh:mm format.
I have defined the destimation column for 'Custom' Cell Format using the
dd/mm/yyyy hh:mm choice. But Excel does not recognise the information as
date & time, BUT, if I press the Return Key on each individual cell, then
Excel does recognise the contents as a date & time. Then I can go on to
chart my results on a timeline (the whole purpose of the exercise).
The difficulty is that I have >5000 entries with more to add. I don't want
to have to hit the Return key for every indivual cell as this defeats the
purpose of mass copy & paste.
How can I get Excel to accept the concatenated data as a date & time?

 

[Peo Sjoblom]

Don't use concatenate to do this, if you want to add time to a date just add
it as A1+B1 where A1 is the date and B1 the time, then format as date and
time

 

[Dave Peterson]

Instead of concatenating the strings, try adding the values:

=a1+B1

(format the way you like)

 

[Guest]

Anisette, this may sound simple, but it sounds like you may have your
calculate option set to "Manual". To check this, go into Tools / Options,
and go to the Calculation tab, and make sure that it is set to Automatic and
not Manual.
Hope this solves your problem,
Lance

 

[Guest]

Lance,
I checked the Calculation tab as you suggested and it was set to Automatic,
but I should have thought to check that, I'll remember that one for ther
future.

Still having problems with compiling date & time out of the csv output from
a measuring device, e.g.
A1 = 2005
B1 = 12
C1 = 24
D1 = 13
I want to get the above into a single cell: 24/12/2005 13:00
The next entry is for 24/12/2005 14:00, 24/12/2005 15:00 and so on, which I
then need to graph on a X-Y timeline.

So I cannot add the individual cell values,, as others suggested, as I want
Excel to distinguish between the year, month, day and hour (otherwise I just
get the answer 2054).

Hence I tried the Concatenated Date idea, along the lines of
E1=C1&"/"&B1&"/"&A1&" "&D1&":00:00"

I get an answer that looks good on the screen but until I hit return to each
cell in Column E, Excel doesn't accept the date - even though I have the Cell
Format set to Custom dd/mm/yyyy hh:mm.

Once I hit the Return Key then Excel recognises the contents (and actually
converts it to 38710.5416666667 which when in the date format gives exactly
what I want.

Any ideas on how to get the input data converted to recognised date & time?

Anisette

 

[Guest]

I had this happen to me today and I did not have the manual calculation on.
I created the column by inserting it between two pre-existing columns and no
matter how I formatted the cells I could not get it to recognize a formula as
a formula (even if I used the 'fx' button in the formula bar). I had to go
the the first empty column in my sheet, create the formula there, then copy
that column over the 'non-functioning' one.

Any ideas as to why that would happen?

 

[Peo Sjoblom]

Try this

=DATE(A1,B1,C1)+D1/24

format as dd/mm/yy hh:mm

copy down

 

[Peo Sjoblom]

If the cells are formatted as text it will return the formula and not the
result

--

Regards,

Peo Sjoblom

I had this happen to me today and I did not have the manual calculation on.
I created the column by inserting it between two pre-existing columns and no
matter how I formatted the cells I could not get it to recognize a formula as
a formula (even if I used the 'fx' button in the formula bar). I had to go
the the first empty column in my sheet, create the formula there, then copy
that column over the 'non-functioning' one.

Any ideas as to why that would happen?

time?

 

[Guest]

You're a genius - Many thanks - A

Try this

=DATE(A1,B1,C1)+D1/24

format as dd/mm/yy hh:mm

copy down


--

Regards,

Peo Sjoblom

 

[Pete]

Anisette,

you need to tell Excel that you want the (numerical) value of your
result, so change your formula to:

=VALUE(C1&"/"&B1&"/"&A1) + D1/24

and format the cell as dd/mm/yy hh:mm, then copy down if needed on
other rows. You do not need to add the ":00:00".

Pete