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

New Member
Joined
Feb 2, 2017
Messages
12
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.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

etaf

Well-known Member
Joined
Oct 24, 2012
Messages
4,660
Office Version
  1. 365
Platform
  1. MacOS
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

New Member
Joined
Feb 2, 2017
Messages
12
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

Well-known Member
Joined
Oct 24, 2012
Messages
4,660
Office Version
  1. 365
Platform
  1. MacOS
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

New Member
Joined
Feb 2, 2017
Messages
12

ADVERTISEMENT

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

Well-known Member
Joined
Oct 24, 2012
Messages
4,660
Office Version
  1. 365
Platform
  1. MacOS
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

New Member
Joined
Feb 2, 2017
Messages
12

ADVERTISEMENT

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

Well-known Member
Joined
Oct 24, 2012
Messages
4,660
Office Version
  1. 365
Platform
  1. MacOS
=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
 
Last edited:

ChrisCpH

New Member
Joined
Feb 2, 2017
Messages
12
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

Well-known Member
Joined
Oct 24, 2012
Messages
4,660
Office Version
  1. 365
Platform
  1. MacOS
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
 

Watch MrExcel Video

Forum statistics

Threads
1,129,467
Messages
5,636,462
Members
416,919
Latest member
twc2c

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top