none
Delete Seconds off of a Time Column RRS feed

  • Question

  • How do I delete the seconds part (:00) off a column that holds time (05:00:00).   I was able to use the text to column to separate the AM/PM from the time column but I'm at a loss of how to delete the seconds.   I've tried using text to column with the delimiter of colon (:) but for some reason when I do this it changes the 3 columns to 00:00:00.   
    Tuesday, November 5, 2019 7:50 PM

All replies

  • Let's say you have times in T2 and down.

    In another cell in row 2, enter the formula

    =TIME(HOUR(T2),MINUTE(T2),0)

    and fill down. Another formula you could use is

    =FLOOR(T2,1/1440)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Tuesday, November 5, 2019 8:37 PM
  • Hi, 

    Just checking in to see if the information of Hans Vogelaar was helpful. Please let us know if you would like further assistance.

    Regards,

    Emi Zhang


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Thursday, November 7, 2019 9:52 AM
    Moderator
  • Thank all but I found a very simple workaround.  I just simply used the Find-Replace in Excel to delete the hyphen in between my date and time and then simply pasted the data from Excel into SharePoint and oila.  Date came over.  Hope this saves someone else time.  
    Thursday, November 7, 2019 6:07 PM
  • Hi,

    Thanks for sharing workaround here, I suggest you mark it as answer. Other partners who read the forums with the same issue can get more information from the correct result.  

    Thank you.

    Regards,

    Emi Zhang


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to share, explore and talk to experts about Microsoft Office 2019.

    Monday, November 11, 2019 9:08 AM
    Moderator
  • Hi,

    Thanks for sharing workaround here. Here I will provide a brief summary of this post for your information.

    ==================

    Issue Symptom:

    How do I delete the seconds part (:00) off a column that holds time (05:00:00). 

    Solution:

    The workaround provided by user:

    I just simply used the Find-Replace in Excel to delete the hyphen in between my date and time and then simply pasted the data from Excel into SharePoint and oila.  Date came over.  Hope this saves someone else time.  

    Regards,

    Emi Zhang


    Please remember to mark the replies as answers if they helped. If you have feedback for TechNet Subscriber Support, contact tnsf@microsoft.com.

    Click here to learn more. Visit the dedicated forum to shareexplore and talk to experts about Microsoft Office 2019.

    Wednesday, November 13, 2019 8:42 AM
    Moderator