Camillia
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.
=UPPER(TEXT(CONCATENATE(MID(C4,5,2),"-",LEFT(C4,3),"-", MID(C4,8,4)), "MM/DD/YYYY"))
ASKER
Thanks, let me try.
Hi,
here a working formula:
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
here a working formula:
=DATEVALUE(SUBSTITUTE(LEFT(A2,FIND(" ",A2,FIND(" ",A2)+4))," ",", ",2))
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
ASKER
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.
Brad
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))
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)
ASKER
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
Hardcoding 1:40:00 AM is ok too
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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")
=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)
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")
just do text(month(date) + "/" + day(date) + "/" + year(date))
ASKER
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?
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?
@Casmilla
JUst copy the formula from B1 to the last entry in the B column
JUst copy the formula from B1 to the last entry in the B column
ASKER
I want the entire ROWS in column A formatted.
Yes. If you copy the formula all rows will be formatted as mm/dd/yyyy
ASKER
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
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:
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)
ASKER
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.
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.
ASKER
Yes, I think I got it
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.