+ Reply to Thread
Results 1 to 3 of 3

formatting of date and time

  1. #1
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal
    MS-Off Ver
    Excel 2003-2019
    Posts
    827

    formatting of date and time

    Hi,

    Actually I am expecting a automation regarding formatting of my data N69:Q111,it should be in the format as stated above of N69.Since below it has rough data which I have to do manually since this is time consuming and like this I have a lot of data.

    Can anyone help in this sort of case.

    With Best Rgds,

    Suresh
    Attached Files Attached Files

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    49,726

    Re: formatting of date and time

    Your data is not showing up like the rest because it is text, not date/time (numeric)

    Try this, to convert it, use 4 helper columns with this, copied down and across...
    =DATE(2014,MID(N69,4,2),LEFT(N69,2))+TIME(MID(N69,7,FIND(".",N69,1)-FIND(" ",N69,1)-1),MID(N69,FIND(".",N69,1)+1,2),0)

    Then, copy/paste values over the original date, and delete the helpers...then format as above
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Forum Contributor
    Join Date
    03-11-2011
    Location
    Nepal
    MS-Off Ver
    Excel 2003-2019
    Posts
    827

    Re: formatting of date and time

    Dear Sir,

    Thanks for the reply.I did with helper column but there is one problem that is of AM & PM case.The raw data does not contain AM & PM.Also,there is date change also problem.And this is possible while doing manually by looking into the pattern of each row.

    I have used your formula in 4 helper column at S69:V111 where in few rows I have highlited with yellow which should be PM and green highlighted date change must be there i.e here in V72 it should change to 8/10 05:00 AM after looking into pattern and also, I have done all manually at 'Expected solution' which is mentioned at X69:AA111 which also my desired solution.

    Hope you understand so that you can futher improvise the existing formula.Workbook is enclosed for your kind reference.

    With Best Rgds,
    Suresh
    Attached Files Attached Files

+ 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. date and time formatting
    By eturn in forum Excel General
    Replies: 7
    Last Post: 01-26-2011, 07:04 PM
  2. Date and time formatting
    By Mayank Trivedi in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 03-10-2008, 03:01 PM
  3. Date and Time formatting
    By aknapp in forum Excel General
    Replies: 1
    Last Post: 07-06-2007, 01:18 AM
  4. Date and Time Formatting
    By brook1 in forum Excel General
    Replies: 2
    Last Post: 10-23-2006, 09:25 AM
  5. Date and time formatting
    By Marco in forum Excel General
    Replies: 7
    Last Post: 02-25-2005, 07:06 PM

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