Extracting date from start of string

juca73

New Member
Joined
Dec 30, 2017
Messages
33
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

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,921
Office Version
  1. 365
Platform
  1. Windows
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.
 

TheSubject

New Member
Joined
Feb 16, 2016
Messages
18
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
 

juca73

New Member
Joined
Dec 30, 2017
Messages
33
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Thanks Jasonb, that worked, i just changed the format in custom to get desired date layout
 

Watch MrExcel Video

Forum statistics

Threads
1,132,803
Messages
5,655,395
Members
418,194
Latest member
LabraLime

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