+ Reply to Thread
Results 1 to 3 of 3

Separating Time and Date in Cells.

  1. #1
    Registered User
    Join Date
    MS-Off Ver
    Excel for Mac Ver. 16.17

    Separating Time and Date in Cells.

    Im dealing with the timestamps from Google form, which come in the following format.

    Screenshot 2020-07-13 at 01.08.45.png

    How do I separate the times and the dates? Whenever I try to do something with the values of the cells (e.g. using the functions LEFT, DATE, etc.), Excel tends to read the cells as general values, e.g. the cell A1 would have value 44024.99806, and LEFT(A1) would give 4. Im using Excel Ver. 16.17 for Mac, if that helps.

  2. #2
    Forum Moderator jeffreybrown's Avatar
    Join Date
    Cibolo, TX
    MS-Off Ver
    Excel 2016

    Re: Separating Time and Date in Cells.

    For the date >> =INT(A1)
    for the time >> =MOD(A1,1)
    Regards, Jeff

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)

    Re: Separating Time and Date in Cells.

    In B1 and C1 copied down:


    Formatting or column B: TT.MM.JJ
    Formatting fir column C: hh:mm

    Enthusiastic self-taught user of MS Excel who's always learning!

    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

+ 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. Parse a date+time field into separate date and time cells
    By meowmeow22 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 07-31-2019, 10:37 AM
  2. Separating date and hours using pivot table and showing time hourly
    By snkhan in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 09-20-2018, 10:35 AM
  3. [SOLVED] Separating Time and Date for Timestamp
    By Grimace in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-16-2014, 08:37 PM
  4. Modifying and separating Date & Time from single cell
    By happyc in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 05-08-2013, 07:26 AM
  5. Format cells problem with separating Date and Time
    By sajeel in forum Excel General
    Replies: 1
    Last Post: 02-09-2010, 02:34 PM
  6. separating date and time from one cell to two
    By bdebened in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 11-12-2009, 01:30 AM
  7. Separating date from a Date & Time stamp
    By JT in forum Excel General
    Replies: 3
    Last Post: 02-20-2005, 01:06 PM

Tags for this Thread


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