Extract Date from Cell

[hossam sadek]

Hi All
i need help with the attached sheet,i want a formula to extract the Date only from Column B to another column, to be honest, I found a way to do it but the challenge i faced that the dates formates are different some with "/" some with "-" take an example the attached sheet all the dates on this sheet are January 2021 dates

so if someone can help me to suggest a formula to do that to a new column

Thanks in advance

 

[AlanSidman]

In column D =Int(B1) Format as short date and copy down.

or if you want the data in Column B, then just format as short date.

 

[hossam sadek]

Thanks, @AlanSidman for the reply, i have already done that but the main problem is that the date isn't on the same format

 

[vletm]

hossam sadek
You could modify those to same 'format'
eg with =SUBSTITUTE(text, old_text, new_text, [instance_num])

 

[hossam sadek]

@vletm thanks,is it possible to give example on the attached sheet so i can understand better

 

[Marcilio]

hossam sadek, Good morning.

Perhaps these procedures can help.

Do it:
B2 --> =MID(A2; 7; FIND(" "; A2; 1) -6)
Copy it down.

Select the desired range in column B.
--> CTRL + C
--> Paster Special
--> Value

Taking advantage that the range is still selected do:

--> Data menu -> Text to Column
--> Fixed with -> Next
--> Next

The column of the DATA PREVIEW will be selected, then CLICK the DATE button by selecting MDY
--> Now just hit FINISH.

All cells are now DATES recognized by Excel

If this procedure is something you always do, maybe someone can write a MACRO (VBA) for you.

Please tell us if it worked for you.
I hope I've helped.

 

[vletm]

hossam sadek
Yes.
Because it was CSV-file then I would work it as CSV-file.
Open This file
Press [ Do It ]-button
Select Your CSV-file
You'll get soon MODified copy of Your original CSV-file.
Note: I don't use those Date-formats ( months are before days ) >> I cannot test, how this will work with ... You.

 

[p45cal]

@hossam sadek , when I opened your file attached to msg#1 I noticed in column B after row 135 that the values were proper Excel dates. If you look at these dates (especially if you format the cells to show the month names instead of month numbers) you'll see that they're not in January as you expect. Those dates have all been misinterpreted by Excel when those values entered the cells.
Looking at the cells above row 135, which Excel hasn't interpreted at all, I can see that the dates are in the month/day/year format.
Your first job, before you try to remove the time element, is to make sure that these rows below row 135 are NOT misinterpreted.
So…
1. how are you getting those data into the sheet?
2. If it's from a file (txt? csv?) then attach such a file too.
3. What version of Excel are you using?

ps. There is a horrible formula you can place in cell D2 and copy down which will extract just the date part and correct misinterpreted excel dates and handle the plain text dates that Excel hasn't interpreted at all:

=IF(ISNUMBER(B2),DATE(YEAR(B2),DAY(B2),MONTH(B2)),DATE(2000+MID(B2,FIND("/",B2,FIND("/",B2)+1)+1,2),LEFT(FIND("/",B2)-1),MID(B2,FIND("/",B2)+1,FIND("/",B2,FIND("/",B2)+1)-FIND("/",B2)-1)))

edit post posting: I've just realised the file you attached is a csv file, so you can ignore all the above .
I will attach a Power Query solution in a short while…

See attached. Right-click the table and choose Refresh.
BUT, because your csv file is unlikely to be in the same place as mine it won't work, so I've placed a picture in the attached of the steps to take to put that right.
If the file is always named the same and in the same location then it will be a case of just refreshing. If not then I can add a little routine to allow you to pick the file (hope you have Excel 2016 or later)…