Date format Reordering
Results 1 to 4 of 4

Thread: Date format Reordering
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Nov 2018
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Date format Reordering

    Hi. Please see link to the relevant file, below:


    https://1drv.ms/x/s!AtIvkRgReIvwgRPV4fmLEL1-gjz-


    I have a problem that I can’t seem to solve. I have this list of times and positions, with the times in hh:mm:ss format. However, the times have the minutes and and seconds in the wrong positions. e.g. Cell B2 shows 17:08:00 (17 hours, 8 minutes and 0 seconds), but this is meant read 00:17:08 (o hours, 17 minutes and 8 seconds)


    Other than re-typing them manually into the correct positions, is there a quick way to rearrange their order correctly?


    Thanks, Olly.

  2. #2
    Board Regular kweaver's Avatar
    Join Date
    May 2018
    Location
    La Jolla, CA
    Posts
    602
    Post Thanks / Like
    Mentioned
    6 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date format Rerdering

    Will this resolve it if filled down the C column? I think there will be an issue when you have a value in column B showing 24 as the hours because that will show up as the 0-hour.
    Possibly an IF statement within the TIME function, but I haven't tried it yet.

    Code:
    =TIME(0,HOUR(B2),MINUTE(B2))
    Last edited by kweaver; Jun 5th, 2019 at 08:46 PM.

  3. #3
    MrExcel MVP Tetra201's Avatar
    Join Date
    Oct 2016
    Posts
    3,372
    Post Thanks / Like
    Mentioned
    15 Post(s)
    Tagged
    1 Thread(s)

    Default Re: Date format Reordering

    Maybe

    =B2/60

  4. #4
    New Member
    Join Date
    Nov 2018
    Posts
    37
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Date format Reordering

    Thank you so much, this worked a treat! Can't believe I didn't think of that - so simple!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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