date&time=text, not formattable

**seraphin** · 05-17-2019, 10:20 AM

I have a column of dates with different formats such as "16MAR2017", "06-Sep-2016", "12/17/2015" and others that contain time.
When I used "text to columns" to split the date column, the field is split into "date" in one column, and "time" in a second column of time, all harmonized into one format respectively.

However, when I concatenate the two new fields, the output turns into text and shown as for example "42810.036458333", which cannot be re-formatted back to date + time. "Isnumber" indicates the new field is indeed a text. Googling around did not find any solution.

As I need to preserve the original content as much as possible, I do have to add the time back into those fields that contain time.

Any help ? Thanks

**Melvosh** · 05-17-2019, 10:23 AM

If you're using CONCATENATE, that converts everything to text strings. Try just adding the two cells together, e.g. =A1+B1.

**CK76** · 05-17-2019, 10:24 AM

You can use =VALUE(), =N(), =Text*1, =Text+0 etc to force text that can be evaluated to number into number.

**ben_hensel** · 05-17-2019, 10:25 AM

When you have the two columns for "date" and "time", and you want to put them back together, don't concatenate them as text strings, just add them like numbers.

**seraphin** · 05-17-2019, 11:07 AM

Thanks a lot. It's very helpful !!
A follow-up, as adding them creates a problem that for those dates without a time, now the new field shows 0:00, which could be interpreted as midnight 0:00, and confusing as some others do record 0:00 in the original content. THis might be a trivial problem, but I am trying to preserve the original content without adding new information.

**Melvosh** · 05-17-2019, 11:33 AM

Assuming time is in column B and date is in column A, you could do something like:

Please Login or Register  to view this content.

This checks to see if the time column is blank, and returns only a date if there's no time, and returns a date and time if there is a time.

**seraphin** · 05-17-2019, 11:36 AM

Right. Sorry for my brain fart !

**torachan** · 05-17-2019, 11:41 AM

Some of what has been said whilst good advice in general.
You can concatenate dates and time and use results in calculations.
In the attached using two helper columns (E&H) which were finally hidden aided data entry.
The secret is to get the formatting right.
torachan.