+ Reply to Thread
Results 1 to 17 of 17

Date with Text inside to Proper Date Format

  1. 05-17-2022, 05:19 AM #1
    rickyilas
    rickyilas is offline
    Forum Contributor
    Join Date
    08-24-2009
    Location
    Manila, Philippines
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    120

    Date with Text inside to Proper Date Format

    Dears,

    I will pls. need to change by formula the date which has a text inside the box.

    A2= mer 13/05/20 = ???

    I've attached the file for the formula pls.

    I cannot highlight and just change format. i think i need formula.

    Thanks in advance.

    Ricky
    Attached Files Attached Files
    Last edited by rickyilas; 05-17-2022 at 01:13 PM.
    Reply With QuoteRegister To Reply
  2. 05-17-2022, 05:36 AM #2
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,933

    Re: Date with Text inside to Proper Date Format

    Administrative Note:

    Members will tailor the solutions they offer to the version (NOT release number) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.
    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. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 05-17-2022, 05:39 AM #3
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,933

    Re: Date with Text inside to Proper Date Format

    In G2 copied across to K2 and down:

    =TEXT(--(MID(A2,5,10)),"dd-mmm-yy")
    Reply With QuoteRegister To Reply
  4. 05-17-2022, 07:09 AM #4
    rickyilas
    rickyilas is offline
    Forum Contributor
    Join Date
    08-24-2009
    Location
    Manila, Philippines
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    120

    Re: Date with Text inside to Proper Date Format

    Dear Aligw,

    Thanks for your reply. My Excel is Microsoft 365 Apps for enterprise. I tried your formula but it's giving me error message. #value.
    Reply With QuoteRegister To Reply
  5. 05-17-2022, 07:49 AM #5
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,787

    Re: Date with Text inside to Proper Date Format

    How about
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  6. 05-17-2022, 08:19 AM #6
    rickyilas
    rickyilas is offline
    Forum Contributor
    Join Date
    08-24-2009
    Location
    Manila, Philippines
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    120

    Re: Date with Text inside to Proper Date Format

    Thanks Fluff!!! It's working perfectly! Thank you all for your help!

    Hi Fluff! Sorry I just saw your result and it was fine but when I went inside the formula, it's going back to the text/date format. I'm confused why your result is fine but when I tried and enter it is not working :-(
    Attached Files Attached Files
    Last edited by rickyilas; 05-17-2022 at 08:29 AM.
    Reply With QuoteRegister To Reply
  7. 05-17-2022, 08:35 AM #7
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,787

    Re: Date with Text inside to Proper Date Format

    Not quite sure why it's doing that, but if I click into the cell with the formula & then press enter it works.
    What is the date format you use?
    Reply With QuoteRegister To Reply
  8. 05-17-2022, 08:41 AM #8
    rickyilas
    rickyilas is offline
    Forum Contributor
    Join Date
    08-24-2009
    Location
    Manila, Philippines
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    120

    Re: Date with Text inside to Proper Date Format

    It's United states [$-en-US]dd-mmm-yy;@
    Maybe it has something to do with my version of excel? I use Microsoft 365.
    Reply With QuoteRegister To Reply
  9. 05-17-2022, 08:42 AM #9
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,222

    Re: Date with Text inside to Proper Date Format

    Please try

    =LET(z,A2:F77,IFERROR(--(MID(z,8,3)&REPLACE(MID(z,5,9),3,3,)),z))
    Attached Files Attached Files
    Linkedin/Bo-Rydobon
    Youtube.com/XcWizard
    Facebook.com/XcWizard
    Reply With QuoteRegister To Reply
  10. 05-17-2022, 08:51 AM #10
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,787

    Re: Date with Text inside to Proper Date Format

    Ok, another option
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Reply With QuoteRegister To Reply
  11. 05-17-2022, 08:56 AM #11
    rickyilas
    rickyilas is offline
    Forum Contributor
    Join Date
    08-24-2009
    Location
    Manila, Philippines
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    120

    Re: Date with Text inside to Proper Date Format

    Thanks Bo_Ry, I tried it and it's working now. I don't think I will ever learn that formula but it will be very useful for me and my colleagues. Thanks again everyone! :-)
    Reply With QuoteRegister To Reply
  12. 05-17-2022, 08:58 AM #12
    rickyilas
    rickyilas is offline
    Forum Contributor
    Join Date
    08-24-2009
    Location
    Manila, Philippines
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    120

    Re: Date with Text inside to Proper Date Format

    Hi Fluff,

    I also tried your last formula and it's working also. No more error. Thanks again!!
    Reply With QuoteRegister To Reply
  13. 05-17-2022, 09:00 AM #13
    Fluff13
    Fluff13 is offline
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,787

    Re: Date with Text inside to Proper Date Format

    Glad to help & thanks for the feedback.
    Reply With QuoteRegister To Reply
  14. 05-17-2022, 10:57 AM #14
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,933

    Re: Date with Text inside to Proper Date Format

    My Excel is Microsoft 365 Apps for enterprise.
    You still haven't updated your profile.

    Thank you for the kind rep comment.
    Attached Images Attached Images
    Last edited by AliGW; 05-17-2022 at 01:07 PM.
    Reply With QuoteRegister To Reply
  15. 05-17-2022, 01:06 PM #15
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,933

    Re: Date with Text inside to Proper Date Format

    Glad to have helped.

    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.
    Reply With QuoteRegister To Reply
  16. 05-17-2022, 01:13 PM #16
    rickyilas
    rickyilas is offline
    Forum Contributor
    Join Date
    08-24-2009
    Location
    Manila, Philippines
    MS-Off Ver
    MS 365 Subscription Insider (Windows 11 64-bit)
    Posts
    120

    Re: Date with Text inside to Proper Date Format

    Thanks Aligw. I've also updated my profile about Excel Version.
    Reply With QuoteRegister To Reply
  17. 05-17-2022, 01:14 PM #17
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2504 (Windows 11 Home 24H2 64-bit)
    Posts
    90,933

    Re: Date with Text inside to Proper Date Format

    Yes, thank you.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Date in Proper Date Format
    By rr1050 in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 04-01-2022, 03:57 PM
  2. [SOLVED] Change date format to get proper order
    By MrBurns in forum Excel General
    Replies: 5
    Last Post: 03-22-2022, 04:02 AM
  3. [SOLVED] Formula to put date in proper format
    By arpanpanchal in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 08-04-2020, 02:38 AM
  4. [SOLVED] Convert date text inside a cell into DATE() type inside a function
    By xChristy in forum Excel General
    Replies: 4
    Last Post: 06-04-2020, 09:44 AM
  5. Not able to change date format into proper excel date format
    By excel5111987 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-18-2011, 08:19 AM
  6. convert a text date into a proper date
    By neowok in forum Excel General
    Replies: 6
    Last Post: 01-09-2009, 07:45 PM
  7. proper date format
    By markogora in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 07-05-2007, 06:41 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1