Excel

[Jakeyharvey23]

Hello!

I am a beginner fixing a .csv document to be run through an R script. From the data download, I have two separate columns, one displaying date and the other displaying time which I have concatenated into a single column. However, the output is not in the desired date format and will not change if I select a different format type. In the new single column, the date is time is displayed as 03,01,202100:00:30 for a single cell whereas I want all cells to match the format 03/01/2021 00:00:30. Any ideas on how I can fix this, I have way to many rows of data to manually fix it.

[#2]

@Jakeyharvey23 Perhaps this will work for you.

[#3 · Accepted solution]

@Jakeyharvey23 

Since basically DATE & TIME are Numeric data therefore need to be insert into cell as single Numeric value.

If you use any of these formulas then it will be TEXT value rather than Numeric.

=TEXT(B1,"mm/dd/yy ")&TEXT(A1,"hh:mm:ss")

Or 

=TEXT(B1+A1,"mm/dd/yyyy HH:mm:ss")

If use either 

=CONCATENATE(B1,A1)

Or 

=B1&A1

 then get there Numeric representation like,,

442560.430902777777778 where 442560 is for DATE in cell B1 (03-01-2021) & 430902777777778 for Time in cell A1 (10:20:30).

• And then after Excel doesn't considers the CELL FORMAT.

Therefore I would like to suggest to use simple formula,

=B1+A1
Where B1 has DATE & A1 had TIME.

and the apply Cell Format mm/dd/yyyy hh:mm:ss,, or whichever is suitable to your data, then return value will be remain Numeric.

[#4]

Thanks so much, this worked.