Link to home
Start Free TrialLog in
Avatar of Camillia
CamilliaFlag for United States of America

asked on

Excel formula for date as text

I have an excel with dates like this

Sep 25 2021 03:58 AM EDT


Is it possible to have a formula to change the cells to 9/25/2021 


Time doesn't matter. It can be anything. 

Avatar of byundt
byundt
Flag of United States of America image

Try a formula like:
=--LEFT(SUBSTITUTE(A2," ",", ",2),FIND(":",A2)-3)

Open in new window

The preceding formula is adding a comma after the day and truncating the text before the time. It then uses the unary operator (two minus signs in a row) to convert the text into a date/time serial number. You will need to format that with m/d/yyyy number format.
=UPPER(TEXT(CONCATENATE(MID(C4,5,2),"-",LEFT(C4,3),"-", MID(C4,8,4)), "MM/DD/YYYY"))
try this

=UPPER(TEXT(CONCATENATE(MID(C4,5,2),"-",LEFT(C4,3),"-", MID(C4,8,4)), "MM/DD/YYYY")) User generated image
Avatar of Camillia

ASKER

Thanks, let me try. 
Hi,
here a working formula:
=DATEVALUE(SUBSTITUTE(LEFT(A2,FIND(" ",A2,FIND(" ",A2)+4))," ",", ",2))

Open in new window

This will first find the fourth occurence of a space (as the time part needs to be removed),
gets the string before that
replaces the second space with a comma
and then uses the DateValue function to get the internal Excel date number.
When you now format the cell as date you will see the correct one.

HTH
Rainer
I'll try soon and post back
Pratima Pharande,
Your formula needs a tweak to handle the possibility of a single digit day like Sep 9 2021 03:58 AM. You might consider capturing 5 characters for the year. Also, you don't need the UPPER function. And the Asker wanted M/D/YYYY.

The following tweaks to your suggestion return the date as text with the first formula. If you want a date/time serial number, use the second formula and format to suit.
=TEXT(CONCATENATE(MID(C4,5,2),"-",LEFT(C4,3),"-", MID(C4,7,5)), "M/D/YYYY")
 
=--CONCATENATE(MID(C4,5,2),"-",LEFT(C4,3),"-", MID(C4,7,5))

Open in new window


Brad
And if I assume the text always has 3 character months, one or two digit days and four digit years, I can simplify Rainer's formula to either of:
=DATEVALUE(SUBSTITUTE(LEFT(A5,11)," ",", ",2))
 
=--SUBSTITUTE(LEFT(A5,11)," ",", ",2)

Open in new window

How about if I want to keep the time?  AM or PM doesn't matter. Just attach time like this 1/4/2022  1:40:00 AM

Hardcoding 1:40:00 AM is ok too
ASKER CERTIFIED SOLUTION
Avatar of Pratima
Pratima
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
with hardcoding1:40:00 AM 

=TEXT(CONCATENATE(MID(C4,5,2),"-",LEFT(C4,3),"-",MID(C4,8,4)," 1:40:00 AM"),"MM/DD/YYYY HH:MM:SS AM/PM")
If you want to include the time as well as the date, you might use either of the formulas shown below. The first one returns the value as text, while the second converts that text to a date/time serial number that you could format as desired.
=LEFT(SUBSTITUTE(A2," ",", ",2),SEARCH("M",A2)+1)

=--LEFT(SUBSTITUTE(A2," ",", ",2),SEARCH("M",A2)+1)

Open in new window

I had to add a comma between day and year to make the formulas work.
And if I didn't mind hard-coding the time and returned the result as text, I could use:
=TRIM(LEFT(A2,11) & " 1:40:00 AM")

Open in new window

just do text(month(date) + "/" + day(date) + "/" + year(date))
I tried 2 of the solutions and they don't work. This is one of them.

I highlight Cell B
Copy the formula
Highlight Cell A
I get below:

I want the entire column changed. How can I do that in Excel?

User generated image
@Casmilla

JUst copy the formula from B1 to the last entry in the B column
I want the entire ROWS in column A formatted. 
Yes. If you copy the formula all rows will be formatted as mm/dd/yyyy
Partha Mandayam - your instruction is not clear. I will wait for others here to respond Thanks. 
PLace your formula in cell b1
press ctrl -c
then drag cursor from b1 till last row of data
press ctrl -v
NOw the formula from b1 will be copied to all the other rows in the B column
Camillia,
44423 is a date/time serial number. You must format using the short date number format so it appears like 8/15/2021

If you want the dates to appear in all the cells, you need to copy the formula down, unless you have Microsoft 365. If you do have Microsoft 365, you can use a formula in a single cell like:
=--SUBSTITUTE(LEFT(A1:A7,11)," ",", ",2)

Open in new window

Looks like Partha's solution with changing to short date might work. 
If you put a formula in column B adjacent to data in column B, you can copy it down by double-clicking the little green square at the bottom right corner of the selection marquee in column B. The formula will copy down until the first blank cell in column A.

If you format the cell in column B as short date prior to copying it down, the short date format will copy down as well.
Yes, I think I got it