+ Reply to Thread
Results 1 to 5 of 5

Removing date and Seconds from cells that include Date and time (x/xx/xxxx 12:34:56)

  1. #1
    Registered User
    Join Date
    04-29-2014
    Location
    Pennsylvania, US
    MS-Off Ver
    Excel 2010
    Posts
    7

    Removing date and Seconds from cells that include Date and time (x/xx/xxxx 12:34:56)

    I have only one column (A) with thousands of cell entries. Each Cell contains a date and time. For example, Cell A1 = 1/24/13 18:45:22

    I want the new cells to only have the time, but have the seconds removed (and not simply hide the seconds). This is because I'll need to run Conditional Formatting to find every cell with a similar time - but I only need similar times within the same minute...it doesn't matter if the seconds are off.

    The new cell should just be A1 = 18:45

    Any ideas how how to accomplish this? Thanks!



    Side note:
    The real reason I need this is because I need to find every entry that has the same time (with hour and minutes), which I figure I'd use conditional formatting for. My problem is that many entries have the same time but the seconds are off.

    Example:
    1/22/13 12:23:11
    1/24/13 18:45:22
    1/24/13 18:45:36
    1/26/13 22:33:41

    In this example, I'd want Conditional Formatting to highlight cell A2 and A3 because they have the same time of 18:45 (not including the seconds). I figured the best way to go about this would be to remove the date and remove the seconds, and run the search that way. however, any suggestions to accomplish my same end goal without deleting the date and seconds would be just as helpful. Thanks!

  2. #2
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Removing date and Seconds from cells that include Date and time (x/xx/xxxx 12:34:56)

    Use

    =TEXT(A1,"hh:mm")
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  3. #3
    Registered User
    Join Date
    04-29-2014
    Location
    Pennsylvania, US
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Removing date and Seconds from cells that include Date and time (x/xx/xxxx 12:34:56)

    Thanks. That worked, except I rant into the problem where I didn't realize so many times are similar in later cells. Would you know how to remove the seconds (not simply hide) while still keeping the date in place...so it would read 1/24/13 18:45 instead of just 18:45

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Removing date and Seconds from cells that include Date and time (x/xx/xxxx 12:34:56)

    Hi,

    =A1-MOD(A1,1/1440)

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  5. #5
    Registered User
    Join Date
    04-29-2014
    Location
    Pennsylvania, US
    MS-Off Ver
    Excel 2010
    Posts
    7

    Re: Removing date and Seconds from cells that include Date and time (x/xx/xxxx 12:34:56)

    Thanks everyone!

+ 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. Include Time with Date
    By N1k100 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-29-2010, 03:27 PM
  2. Replies: 3
    Last Post: 08-06-2009, 03:02 PM
  3. How to convert date/time to seconds
    By [email protected] in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-18-2006, 08:30 PM
  4. Need help: convert seconds to date and time
    By misty1 in forum Excel General
    Replies: 2
    Last Post: 11-26-2005, 12:12 AM
  5. How do i convert a number of seconds to a date/time?
    By Margo in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-04-2005, 09:06 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