I have a column with Date and time in the same field, how to I split or change so I only have the date?

[ChrisCpH]

HI all, I hope someone can help.

I have a Dtae&time column with 04-07-2019 15:17:10.
I only need the date (preferably as 04.07.2019).
I have tried to simply reformat the date to another format but that does not work.

Any ideas?

Thanks in advance.

 

[etaf]

is the date an actual date or text
reformat to Number
do you now get a number or still the date?

if it is a DATE then in a new cell
assuming the date is in A2
then in B2
=INT(A2)
format as a date , now you will only have the date and NO time
Otherwise
in B2
=VALUE(LEFT(A2,10)
Now should read as a date

copy the formula down

 

[ChrisCpH]

is the date an actual date or text
reformat to Number
do you now get a number or still the date?

if it is a DATE then in a new cell
assuming the date is in A2
then in B2
=INT(A2)
format as a date , now you will only have the date and NO time
Otherwise
in B2
=VALUE(LEFT(A2,10)
Now should read as a date

copy the formula down

Thanks, it is a DATE, I will try thanks.

 

[etaf]

if its a date
then FORMAT>Custom
DD.MM.YYYY
it should ignore time and format as you wanted
works ok on a sample i have

 

[ChrisCpH]

if its a date
then FORMAT>Custom
DD.MM.YYYY
it should ignore time and format as you wanted
works ok on a sample i have

Hi Wayne,

Unfortunately neither of your suggestions work.

When I check the formatting of the original cell, it indicates that it is a date field. However I had tried changing to a custom date field previously and tried again after your suggestion, no luck.

I also tried both the suggested formulas but neither worked.
When I enter "=INT(A2)" in column B, I just get "#name?"
When I enter "=VALUE(LEFT(A2,10)" ( also tried "=VALUE(LEFT(A2,10))" I get invalid formula.

I should mention that this is in a Danish Office365 Excel on Mac.

Any other ideas?

Thanks in advance

 

[etaf]

i'm on 365 for mac
A2 assumes thats where you dates are ?

surprised with the INT() you get NAME error
when you format the date cell as a number - what do you get

Using a blank cell
INT(Blankcell)
do you still get name or 0

 

[ChrisCpH]

Yes, it's actually L3 but I get the idea.

Using a blank cell and =INT(Blankcell) I still get Name error.

Not sure I follow correctly, but if I reformat A2 as a number, I still get Name error. Is that what you meant? I also tried to format B2 as a number, and both cells a a number..no change.

 

[etaf]

=INT(L3)
then

NOT A2
or B2, they were just cell examples

when you type =INT into the formula BAR what comes UP
should be a LIST of formulas with INT( at the top

Name means it does not understand that formula

if you format L3
USE a number or General format
what do you get

today 28 Feb 2020
should read as a number
43889

perhaps a sample with no sensitive data on a share, like dropbox or onedrive , so we can see the real spreadsheet itself

 

[ChrisCpH]

Yes, I understand that I should refer to L3 thanks.
When I type INT I only get one formula suggestion, which is "internal interest"? This requires more arguments to make a formula.

When I format as General I do indeed get 43889.

What is the English formula name you expect to come up with INT? I might be able to find the translation....?

 

[etaf]

integer - so if you have a NUMBER like 43889.23 - which is date and time
it will return 43889

as its a date, surprised the custom format does not work - works OK for me