Extracting date from start of string

juca73

New Member
Joined
Dec 30, 2017
Messages
40
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
I cant seem to find a formula that works, i have a date in a date time string

21/03/2021 07:03:00

This is in cell A2

I have done the time part of it into another column yet struggling to extract the date to another column, i have tried other suggestions yet nothing seems to be working

the data comes from another source direct to excel and appears to be in custom format

dd/mm/yyyy hh:mm

i wish to have the date as below when extracted

21st March 2021

Thanks in advance
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
If your system settings use DMY dates then you should be able to do it with
Excel Formula:
=INT(A2+0)
and format the result as required.
 
Upvote 0
What are you getting? I'm assuming (based on example) you're getting a return of 44276.2937? Or are you getting nothing?

That does look like it should be super easy, but I set up what you've done (minus external source) and it wasn't liking it, was it?

Using this formula:
=((DAY(A2))&"/"&(MONTH(A2)))&"/"&(YEAR(A2)) you will get the return of 21/3/2021 - its a step in the right direction.
I would imagine you could further edit that with =text
 
Upvote 0
Thanks Jasonb, that worked, i just changed the format in custom to get desired date layout
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,974
Members
448,537
Latest member
Et_Cetera

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
Back
Top