+ Reply to Thread
Results 1 to 7 of 7

Want to covert date format from 13.01.2022 00:00:58.799 this to this 1/13/2022 0:00

  1. #1
    Registered User
    Join Date
    01-09-2022
    Location
    UK
    MS-Off Ver
    2019
    Posts
    7

    Want to covert date format from 13.01.2022 00:00:58.799 this to this 1/13/2022 0:00

    Hi,

    I have date and time in Column A. The format of this date and time is
    13.01.2022 00:00:58.799
    i wan to covert this format to this
    1/13/2022 0:00

    (Also please note that seconds and milliseconds should be removed)

    (Sample file is attached for further clarification)
    Attached Files Attached Files

  2. #2
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Want to covert date format from 13.01.2022 00:00:58.799 this to this 1/13/2022 0:00

    This rounddown to nearest minute:
    Please Login or Register  to view this content.
    If roundup: replace FLOOR by CEILING
    If <30s, down; >=30s, up: replace FLOOR by MROUND
    Quang PT

  3. #3
    Registered User
    Join Date
    01-09-2022
    Location
    UK
    MS-Off Ver
    2019
    Posts
    7

    Re: Want to covert date format from 13.01.2022 00:00:58.799 this to this 1/13/2022 0:00

    Hi bebo021999

    i tried this but this is not working. I have very few knowledge about excel, may be i am missing something. Can you please put this formula in excel file. and give me the working excel file.

  4. #4
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    22,464

    Re: Want to covert date format from 13.01.2022 00:00:58.799 this to this 1/13/2022 0:00

    B2

    =DATE(MID(A2,7,4),MID(A2,4,2),LEFT(A2,2))+MROUND(TIMEVALUE(MID(A2,12,8)),"00:01")

    and copy down.

  5. #5
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: Want to covert date format from 13.01.2022 00:00:58.799 this to this 1/13/2022 0:00

    See attachment
    Attached Files Attached Files

  6. #6
    Valued Forum Contributor
    Join Date
    01-07-2022
    Location
    Europe
    MS-Off Ver
    Office 365
    Posts
    473

    Re: Want to covert date format from 13.01.2022 00:00:58.799 this to this 1/13/2022 0:00

    Those above formula will strip out the seconds/milliseconds.

    If it still displays in the wrong format e.g. 13.01.2022 00:01:00.000 - then you need to change the Number Formatting.

    To do this, select the relevant range, and go to format cells > number format > custom, and enter this into Type:
    m/d/yyyy hh:mm
    <<< If you have valued anyone's contributions in this thread, please click * to thank them for their efforts

  7. #7
    Forum Expert
    Join Date
    10-06-2008
    Location
    Canada
    MS-Off Ver
    2007 / 2013
    Posts
    5,498

    Re: Want to covert date format from 13.01.2022 00:00:58.799 this to this 1/13/2022 0:00

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Call in the Cavalry - 2022
    By AliGW in forum The Water Cooler
    Replies: 52
    Last Post: 01-18-2023, 09:35 PM
  2. 2022
    By Glenn Kennedy in forum The Water Cooler
    Replies: 25
    Last Post: 01-09-2022, 05:13 PM
  3. How to make a pivot table using 2021.xls and 2022.xls
    By apekuri in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 01-03-2022, 06:51 PM
  4. Happy Holidays and a healthy and happy 2022
    By Keebellah in forum Excel General
    Replies: 0
    Last Post: 12-20-2021, 05:26 AM
  5. On off duty cycle chart not working for 2022
    By sham_33rtks in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 12-15-2021, 09:45 AM
  6. [SOLVED] =TEXT Formula for 2022
    By Stenner in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-26-2021, 04:49 AM
  7. [SOLVED] =?iso-2022-jp?B?V2hlcmUgZG9lcyAiYSwbJEIhSRsoQiBjb21lIGZyb20/?=
    By Mike in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 04-29-2006, 08:30 AM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1