+ Reply to Thread
Results 1 to 9 of 9

Date/Time Formatting

  1. #1
    Registered User
    Join Date
    09-26-2016
    Location
    SC
    MS-Off Ver
    365
    Posts
    14

    Date/Time Formatting

    Hello,
    I have a spreadsheet that has the date/time in the following format: Dec 4, 2022, 7:30:00 PM. I need the information in the following format: 12/4/2022 7:30:00 PM. Is there a way to change the formatting without adding multiple columns to the spreadsheet?

    Thanks in advance for any help on this!

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.90.2 (24102719))
    Posts
    8,956

    Re: Date/Time Formatting

    is this a real date or text ?
    if you click on the cell and format to general
    does it change to a number

    format as MM/DD/YYYY hh:mm am/pm

    otherwise without adding a column and TEXT to columns doesn't change to a real date - may need vba, not my area
    should be able to do with one additional column and function - if text

    BUT
    ote: Images are difficult to see , and also requires that I input all the data myself, which means I may make an error, which is very time consuming, and from my point of view less likely to get a response, if a complicated spreadsheet.

    A sample sheet would help here

    The forum does allow for spreadsheets to be uploaded direct to the forum -

    Please see the Yellow Banner at the top of the page explaining how to attach a sample spreadsheet

    A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

    Here are the instructions, found at the top of the page again
    Post a small Excel sheet (not a picture) showing realistic & representative sample data WITHOUT confidential information (10-20 rows, not thousands...) and some manually calculated results. For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Now follow the instructions at the top of that screen.
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Valued Forum Contributor
    Join Date
    06-23-2021
    Location
    Romania
    MS-Off Ver
    2021, 365 v 2208
    Posts
    722

    Re: Date/Time Formatting

    Dec 4, 2022, 7:30:00 PM is text or is number?
    You can check this with the function ISTEXT
    Diana Tanase


    If the solutions offered helped you to solve your problem, then mark the thread as SOLVED (thread tools in the top menu) and you can click on * to add reputation to those who helped you, as a way to say thank you !

  4. #4
    Registered User
    Join Date
    09-26-2016
    Location
    SC
    MS-Off Ver
    365
    Posts
    14

    Re: Date/Time Formatting

    It's data that's in a csv. format. I hope the file uploaded. Thanks for the help.
    Attached Files Attached Files

  5. #5
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.90.2 (24102719))
    Posts
    8,956

    Re: Date/Time Formatting

    you can probably use power query to change the date on import, and stay in column - dont know that yet, only just on my Mac
    OR VBA

    but i have used
    =DATEVALUE(MID(A2,SEARCH(",",A2,1)-2,2)*1&"-"&LEFT(A2,3)&"-"&MID(A2,SEARCH(",",A2,1)+2,4))+TIMEVALUE(MID(A2,SEARCH(",",A2,1)+7,11))

    and they maybe a better way using substitute or a shorter formula

    but seems to work

    only issue is its in a different column
    Attached Files Attached Files

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    16,108

    Re: Date/Time Formatting

    When I opened your .csv (text) file into my spreadsheet using LO Calc, I specified that the date field should be imported as MDY dates, and Calc automatically recognized the date/times and stored them correctly as serial numbers. In my case, it automatically chose an "mm/dd/yy hh:mm AM" number format, but I could easily select whatever number format I want after the data are correctly imported.

    Excel can do the same thing using the get data from text command and using the text import wizard. Or Power Query should have no trouble importing this text data into Excel. The key idea is to find an import strategy into the spreadsheet so that your spreadsheet application can readily recognize the date/time information and store it correctly.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  7. #7
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.90.2 (24102719))
    Posts
    8,956

    Re: Date/Time Formatting

    my first use of power query, only found it had been added to my mac version yesterday - after a form question

    so i just tried to use it
    and it worked perfectly as suggested by MrShorty

    also gave me a reason to try using it for real life reason ... thanks

    would not work with import - and changing to MDY - not sure why - but tried that first before the formula

    you are showing in your profile 2013 version - is that what you have ?
    Attached Files Attached Files

  8. #8
    Registered User
    Join Date
    09-26-2016
    Location
    SC
    MS-Off Ver
    365
    Posts
    14

    Thumbs up Re: Date/Time Formatting

    MS Office 365. Not familiar with LO Cal either. I'll try the import method through Excel though. Thanks for all of your help, I'll let you know how it goes.

    Thanks Again!!

  9. #9
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.90.2 (24102719))
    Posts
    8,956

    Re: Date/Time Formatting

    you need to update your profile from 2013 please - there are so many new functions that make solutions much easier - its important to know that

+ 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. [SOLVED] Create conditional formatting using time / date-time
    By Halidat in forum Excel General
    Replies: 3
    Last Post: 10-13-2021, 11:12 AM
  2. [SOLVED] Date / Time formatting
    By glomb175 in forum Excel General
    Replies: 4
    Last Post: 04-06-2018, 09:56 AM
  3. [SOLVED] Conditional Formatting with Date/Time with Date/Time of another cell
    By jnepsa in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 02-20-2017, 10:48 AM
  4. formatting of date and time
    By paradise2sr in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-10-2014, 03:25 AM
  5. Date and time formatting
    By Mayank Trivedi in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 03-10-2008, 03:01 PM
  6. Date and Time Formatting
    By brook1 in forum Excel General
    Replies: 2
    Last Post: 10-23-2006, 09:25 AM
  7. date and time formatting
    By rantz in forum Excel General
    Replies: 3
    Last Post: 11-28-2005, 02:35 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1