Hello Gurus,
I have challenge converting the Text to Date format, i have DMMYYYY, DDMYYYY, DDMMYYYY date formats.
I have two text with Len=7, ex: 0422024, 4102024, the result should be 04022024 and 04102024.
I got the formula, but i want to combine two formulas by using IF condition.
in C1 the the output should be =If (Len(A1)=7, then Date formula1 (B1) or Date formula 2 (B2))
thanks
Are you still using Excel 2010?
There is no way that Excel can determine accurately how to divide up those first three numbers in the string.
Last edited by AliGW; 03-19-2024 at 02:26 AM.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
What should 1212024 by converted to... and why?
Hi,
If A1 is 1112024, is your expected result 01112024 or 11012024?
Thanks for your quick reply.
@AliGw, I have changed to MS 365 as my Excel version
@Rick, 1212024 to 01212024 as MMDDYYYY
@phuocam, the expected result should be 01112024
You are missing my point.
In the case of 111 as the three opening numbers, HOW is Excel meant to decide whether this represents the first of the eleventh or the eleventh of the first? In other words, how does it know that it should be 01112024 and not 11012024?
That one is clear - must be 12012024 because we are working in DDMMYYYY format. Can't be the first of the 24th.What should 1212024 by converted to... and why?
Last edited by AliGW; 03-19-2024 at 03:05 AM.
Hi Ali, By default in my source the 1112024 is DDMYYYY, please see the attachment, i hope it give you the understanding
Why on EARTH are you removing the / ??? That's causing you all of the problems!
In F3 copied down:
=DATE(TEXTAFTER(A3,"/",2),TEXTBEFORE(TEXTAFTER(A3,"/"),"/"),TEXTBEFORE(A3,"/"))
This also works:
=DATEVALUE(A3)
Last edited by AliGW; 03-19-2024 at 03:42 AM. Reason: Workbook updated.
No reply ...
If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.
Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.
Try in F3 (for excel 2016 or older):
drag down
Please Login or Register to view this content.
Quang PT
Why? DATEVALUE works in 2003 and newer!
with this:Originally Posted by AliGW
"for excel 2016 or older"
I mean this formula for user who has 2016 or older (2016,2013,2007,2003...)
= do not have 2019 or 365
First of the 24th??? I know the question has moved on, but look at the number I posted again... 1212024 It could be converted to either 01212024 or 12012024, hence the question I asked.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks