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

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?

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

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


--

Regards,

Peo Sjoblom

"Anisette" wrote in message
...
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?

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

Instead of concatenating the strings, try adding the values:

=a1+B1

(format the way you like)



Anisette wrote:

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?

--

Dave Peterson

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

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

"Anisette" wrote:

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?

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

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
"Lance Gray" wrote:

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

"Anisette" wrote:

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?

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

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?
--
-----

Randy Davis


"Anisette" wrote:

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
"Lance Gray" wrote:

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

"Anisette" wrote:

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?

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

Try this

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

format as dd/mm/yy hh:mm

copy down


--

Regards,

Peo Sjoblom

"Anisette" wrote in message
...
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
"Lance Gray" wrote:

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

"Anisette" wrote:

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?

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

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

--

Regards,

Peo Sjoblom

"Randy Davis" wrote in message
...
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?
--
-----

Randy Davis


"Anisette" wrote:

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
"Lance Gray" wrote:

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

"Anisette" wrote:

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?

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

You're a genius - Many thanks - A

"Peo Sjoblom" wrote:

Try this

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

format as dd/mm/yy hh:mm

copy down


--

Regards,

Peo Sjoblom

"Anisette" wrote in message
...
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
"Lance Gray" wrote:

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

"Anisette" wrote:

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?

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

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