LinkBack URL
About LinkBacks
Hello,
I have a date/time stamp stored as text in A1 "Tuesday, September 19, 2017 10:00 PM (IMP)"
i want to separate it in four columns >> Tue, 19-Sep-2017, 10:00 PM, IMP
I used datedif but its not working. please help me convert this.
Thanks
Hello,
I have a date/time stamp stored as text in A1 "Tuesday, September 19, 2017 10:00 PM (IMP)"
i want to separate it in four columns >> Tue, 19-Sep-2017, 10:00 PM, IMP
I used datedif but its not working. please help me convert this.
Thanks
hi sabha. you probably need to upload a sample excel file which has more egs and your desired outcome. otherwise, you would have different scenarios that we need to guess. right now, it looks like you can do this in B1:
=LEFT(A1,FIND(",",A1)-1)
C1:
=--LEFT(SUBSTITUTE(A1,B1&", ",""),SEARCH("???? ??:",SUBSTITUTE(A1,B1&", ",""))+3)
D1:
=--TRIM(MID(A1,FIND(":",A1)-2,8))
E1:
=SUBSTITUTE(MID(A1,FIND("(",A1)+1,LEN(A1)),")","")
format the cells accordingly as date or time
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
If I understand correctly try this formula in B1 and fill across to D1. Format as "ddd", "d-mmm-yyyy", and "h:mm AM/PM".Then in E1 this formula.Formula:
Please Login or Register to view this content.Formula:Please Login or Register to view this content.
Dave
Sorry for the incomplete sample. My data is not so large so i am not uploading a sample file. Listing below what I have in range A1:A28Originally Posted by benishiryo
Tuesday, September 19, 2017 10:00 PM (IMP)
Saturday, April 22, 2017 10:00 PM (IMP)
Saturday, August 26, 2017 10:00 PM (ORD)
Saturday, February 25, 2017 10:00 PM (ORD)
Saturday, January 28, 2017 10:00 PM (SAM)
Saturday, July 01, 2017 10:00 PM (SAM)
Saturday, June 03, 2017 10:00 PM (SAM)
Saturday, March 18, 2017 10:00 PM (ORD)
Saturday, May 06, 2017 10:00 PM (SAM)
Saturday, September 16, 2017 10:00 PM (IMP)
Thursday, April 20, 2017 10:00 PM (SAM)
Thursday, August 17, 2017 10:00 PM (ORD)
Thursday, February 23, 2017 10:00 PM (SAM)
Thursday, January 12, 2017 10:00 PM (IMP)
Thursday, July 20, 2017 10:00 PM (SAM)
Thursday, June 22, 2017 10:00 PM (ORD)
Thursday, March 23, 2017 10:00 PM (SAM)
Thursday, May 04, 2017 10:00 PM (SAM)
Thursday, September 07, 2017 10:00 PM (IMP)
Tuesday, April 25, 2017 10:00 PM (SAM)
Tuesday, August 29, 2017 10:00 PM (ORD)
Tuesday, February 14, 2017 10:00 PM (ORD)
Tuesday, January 03, 2017 10:00 PM (ORD)
Tuesday, July 04, 2017 10:00 PM (IMP)
Tuesday, June 27, 2017 10:00 PM (SAM)
Tuesday, March 28, 2017 10:00 PM (SAM)
Tuesday, May 30, 2017 10:00 PM (ORD)
Tuesday, September 05, 2017 10:00 PM (IMP)
I tried your solution but it gives me #Value! everywhere
Formula in E1 works fine
It works at my end. See attached.
Are there regional settings differences eg "," for ";" argument separators?
Strange!
The formula does not work on my laptop even if I open your attachment. I dont know how to check the regional settings but will try to figure it out
Note: I am using Microsoft Office Home and Student 2016 Version 1707 (Build 8326.2096)
Last edited by sabha; 09-21-2017 at 05:23 AM. Reason: added excel version
and did you try my solutions in post #2?
Yes... The date doesn't show up. I also checked your attachment... this is how i get
Tuesday, September 19, 2017 10:00 PM (IMP) Tuesday #VALUE! 10:00 PM IMP
Here is the screenshot of my regional settings
regional.png
Hi,
Try the following:
In B1:
In C1:
Please Login or Register to view this content.
In D1:Please Login or Register to view this content.
In E1:Please Login or Register to view this content.
See the attached file.Please Login or Register to view this content.
It worked !
Thanks a lot !!
I liked the approach of @FlameRetired as it was a single formula that can be copied down and across but I dont know why it does not work on my laptop. I am still trying to find why is this happenning. Please shed some light on it.
Try an online search. There are recent version/builds causing problems in Excel 2016. I don't remember which one it is and I don't remember what the problems are.
Yes, definitely... I was pretty impressed with the approach you had in your formula. I am going to open that file in other computer too to see the result and will also do an online search and will also try to update msoffice. Thanks for your suggestion. God bless !
Here's a site that addresses this. It is Version 1707.
https://social.msdn.microsoft.com/Fo...forum=exceldev
Thanks for the link. But Iam scared to do a rollback/revert. Instead of a downgrade, it will be better when office upgrades. I will tell my brother to look into this as i am not so very tech savvy. Thank you.
Here is another one
Enter in B1 and drag formula across to cell F1 and copy down
Format Col D as Custom, dd-mmm-yyyy and format Col E as Custom, hh:mm AM/PM
Formula:Please Login or Register to view this content.
v A B C D E F 1 Tuesday, September 19, 2017 10:00 PM (IMP) Tue Sep 19-Sep-2017 10:00 PM IMP 2 Tuesday, September 19, 2017 10:00 PM (IMP) Tue Sep 19-Sep-2017 10:00 PM IMP 3 Saturday, April 22, 2017 10:00 PM (IMP) Sat Apr 22-Apr-2017 10:00 PM IMP 4 Saturday, August 26, 2017 10:00 PM (ORD) Sat Aug 26-Aug-2017 10:00 PM ORD 5 Saturday, February 25, 2017 10:00 PM (ORD) Sat Feb 25-Feb-2017 10:00 PM ORD 6 Saturday, January 28, 2017 10:00 PM (SAM) Sat Jan 28-Jan-2017 10:00 PM SAM 7 Saturday, July 01, 2017 10:00 PM (SAM) Sat Jul 01-Jul-2017 10:00 PM SAM 8 Saturday, June 03, 2017 10:00 PM (SAM) Sat Jun 03-Jun-2017 10:00 PM SAM 9 Saturday, March 18, 2017 10:00 PM (ORD) Sat Mar 18-Mar-2017 10:00 PM ORD 10 Saturday, May 06, 2017 10:00 PM (SAM) Sat May 06-May-2017 10:00 PM SAM 11 Saturday, September 16, 2017 10:00 PM (IMP) Sat Sep 16-Sep-2017 10:00 PM IMP 12 Thursday, April 20, 2017 10:00 PM (SAM) Thu Apr 20-Apr-2017 10:00 PM SAM 13 Thursday, August 17, 2017 10:00 PM (ORD) Thu Aug 17-Aug-2017 10:00 PM ORD 14 Thursday, February 23, 2017 10:00 PM (SAM) Thu Feb 23-Feb-2017 10:00 PM SAM 15 Thursday, January 12, 2017 10:00 PM (IMP) Thu Jan 12-Jan-2017 10:00 PM IMP 16 Thursday, July 20, 2017 10:00 PM (SAM) Thu Jul 20-Jul-2017 10:00 PM SAM 17 Thursday, June 22, 2017 10:00 PM (ORD) Thu Jun 22-Jun-2017 10:00 PM ORD 18 Thursday, March 23, 2017 10:00 PM (SAM) Thu Mar 23-Mar-2017 10:00 PM SAM
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
It worked on my office computer. I need to see if it works on my personal laptop at home as previous two solutions did not work but worked on office pc. Thanks !
another option (for Ex2010, 2013 with PowerQuery add-in or Ex2016 with PQ built-in)
Last edited by sandy666; 09-22-2017 at 02:55 AM. Reason: file update
I did had a look as it seemed to be interesting. However, I am still trying to figure out how you did it. May be I need to read and research on PQ. Thank you for the solution though. Cheers !
Here is step-by-step:
WelcomePlease Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks