Calc or Excel help wanted

Forumite Members › General Topics › Tech › Software Talk › Calc or Excel help wanted

Viewing 6 posts - 1 through 6 (of 6 total)
  • Author
    Posts
  • August 4, 2018 at 2:56 pm #24206
    Ed PEd P
    Participant
      @edps
      Forumite Points: 39

      It is too many years since I last used Excel data table manipulation in anger.

      I have a data table of readings in which the first column is a date-time value

      How can I split that value into two columns of ‘Date’ and ‘Time’

      For example if A:2 contains 2018/06/01 07:25  How do I split it to:

      B:2 = 2018/06/01 and C2 =07:25?

       

      August 4, 2018 at 3:32 pm #24212
      RichardRichard
      Participant
        @sawboman
        Forumite Points: 16

        I used to have to do that on the fly with data coming in from various sources in the mid 1980’s, but that was using a database program, which parsed the data stream, found dates and time, created files then spewed the formatted data into the database file(s) for the next component to manipulate into various studies, returns and reports with the correct formatting. Would something like this be of interest to get you started:https://exceljet.net/formula/convert-date-string-to-date-time

         

        August 4, 2018 at 3:39 pm #24213
        Ed PEd P
        Participant
          @edps
          Forumite Points: 39

          Thanks Richard that looks like it will work.

          August 4, 2018 at 4:41 pm #24216
          Dave RiceDave Rice
          Participant
            @ricedg
            Forumite Points: 7

            Does this help? The formula in Column B is shown in C.

            Excuse the typo, I moved things about. It’s A1 not A4

            August 4, 2018 at 4:51 pm #24218
            Ed PEd P
            Participant
              @edps
              Forumite Points: 39

              Thanks to all for pointing me in the ‘right’ direction. I accidentally found a counter-intuitive trick in Calc that worked.

              a) Select range then use the Data->Text to Columns command in the Data item header.!

              This allows you to specify the space between date and time as a delimiter and split the result into two columns. The first needs no change and remains as ‘Date’ but the second new column has to be reformatted as Time. Totally daft but it works and I’ve no idea why!

              August 5, 2018 at 5:18 pm #24302
              blacklion1725blacklion1725
              Participant
                @blacklion1725
                Forumite Points: 2

                Dave’s sugestion is how I always do this.

              • Author
                Posts
              Viewing 6 posts - 1 through 6 (of 6 total)
              • You must be logged in to reply to this topic.