{"id":5483,"date":"2023-05-18T07:52:00","date_gmt":"2023-05-18T05:52:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=5483"},"modified":"2023-06-21T19:17:59","modified_gmt":"2023-06-21T17:17:59","slug":"split-date-and-time-in-excel","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/excel\/split-date-and-time-in-excel\/","title":{"rendered":"How to split date and time in Excel into separate columns?"},"content":{"rendered":"\n

Excel tutorial for separating the date and time into separate columns.<\/em><\/strong><\/h4>\n\n\n\n

How to split date and time in two columns with Excel? Splitting a date from the time with Excel is not as simple as that. We will see how to do it with a practical example in Excel. Indeed, often when working with Excel, the Dates columns contain in practice the “Timestamp” format which corresponds in English to the “Date-Time” format.<\/p>\n\n\n\n

However, to perform calculations on hours or days it is more practical to split the dates and times. For example, to find a time difference between 2 schedules it is easier to manipulate the hours alone. There are many Excel formulas to handle dates and extract each element like YEAR(), MONTH(), DAY() for example. First create the two Excel destination columns for the date and time.<\/p>\n\n\n\n

1. Prepare the Date only column to extract from date time<\/h2>\n\n\n\n

First, fill in the Date column, “Date only” in the example:<\/p>\n\n\n\n

    \n
  1. Enter the formula =ENT(A2)<\/strong><\/li>\n\n\n\n
  2. Assign the Date Format<\/strong> to the Date only<\/em> column.<\/li>\n<\/ol>\n\n\n\n

    The Excel function INT<\/a>()<\/a> means Integer, to be applied to your first cell, here in the A2 cell, containing the Date and Time to be separated.<\/p>\n\n\n

    \n
    \"Split
    Split date using the INT Excel function.<\/em><\/figcaption><\/figure><\/div>\n\n\n

    2. Prepare the Time Only column to split from the date time<\/h2>\n\n\n\n

    Secondly, to prepare the “Time only” column in our Excel file, use the Excel MOD() function<\/a>, for Modulo in mathematics:<\/strong><\/p>\n\n\n\n

      \n
    1. Enter the formula =MOD(A2;1)<\/strong><\/li>\n\n\n\n
    2. Assign the Time Format<\/strong> to the Time only<\/em> column.<\/li>\n<\/ol>\n\n\n
      \n
      \"Split
      Split the time using the Excel Modulo function<\/em><\/figcaption><\/figure><\/div>\n\n\n

      3. Result with Excel date and time in two separate columns <\/h2>\n\n\n\n

      Finally, observe the result with the two columns Date only and Time only perfectly separated in Excel with respectively a Date format and a Time format.<\/p>\n\n\n

      \n
      \"Split
      Split date and time using the date and time format<\/em><\/figcaption><\/figure><\/div>\n\n\n

      Conclusion on isolating time and date in formulas <\/h3>\n\n\n\n

      Finally, this tutorial explains step by step how to separate date and time with Excel formulas and assign the correct display format. To go even further, it’s interesting to see how to calculate differences down to seconds from two dates in T-SQL<\/strong><\/a> this time, using a Date \/ Time \/ Seconds format. Here’s an example in SQL language. More information on the MOD function page on the Microsoft site.<\/p>\n\n\n\n

      \nhttps:\/\/expert-only.com\/en\/t-sql\/calculate-difference-between-two-dates\/\n<\/div><\/figure>\n","protected":false},"excerpt":{"rendered":"

      Excel tutorial for separating the date and time into separate columns. How to split date and time in two columns with Excel? Splitting a date from the time with Excel is not as simple as that. We will see …<\/a><\/p>\n<\/div>","protected":false},"author":1,"featured_media":5509,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[457],"tags":[],"class_list":{"0":"post-5483","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-excel"},"yoast_head":"\nSplit date and time in Excel into separate columns - Excel Tips<\/title>\n<meta name=\"description\" content=\"How to split Excel date and time in two columns? Let's see how to separate a date in two columns with a practical example in an Excel file.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/expert-only.com\/en\/excel\/split-date-and-time-in-excel\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How to split date and time in Excel into separate columns?\" \/>\n<meta property=\"og:description\" content=\"How to split Excel date and time in two columns? Let's see how to separate a date in two columns with a practical example in an Excel file.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/expert-only.com\/en\/excel\/split-date-and-time-in-excel\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and IT Tutorials\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/ExpertOnlyCom\/\" \/>\n<meta property=\"article:published_time\" content=\"2023-05-18T05:52:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-06-21T17:17:59+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/02\/calendar-660670_1920.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1920\" \/>\n\t<meta property=\"og:image:height\" content=\"1080\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Expert-Only\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@expert_only\" \/>\n<meta name=\"twitter:site\" content=\"@expert_only\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Expert-Only\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/expert-only.com\/en\/excel\/split-date-and-time-in-excel\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/expert-only.com\/en\/excel\/split-date-and-time-in-excel\/\"},\"author\":{\"name\":\"Expert-Only\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef\"},\"headline\":\"How to split date and time in Excel into separate columns?\",\"datePublished\":\"2023-05-18T05:52:00+00:00\",\"dateModified\":\"2023-06-21T17:17:59+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/expert-only.com\/en\/excel\/split-date-and-time-in-excel\/\"},\"wordCount\":398,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/expert-only.com\/en\/#organization\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/excel\/split-date-and-time-in-excel\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/02\/calendar-660670_1920.jpg\",\"articleSection\":[\"Excel Tips\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/expert-only.com\/en\/excel\/split-date-and-time-in-excel\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/expert-only.com\/en\/excel\/split-date-and-time-in-excel\/\",\"url\":\"https:\/\/expert-only.com\/en\/excel\/split-date-and-time-in-excel\/\",\"name\":\"Split date and time in Excel into separate columns - Excel Tips\",\"isPartOf\":{\"@id\":\"https:\/\/expert-only.com\/en\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/expert-only.com\/en\/excel\/split-date-and-time-in-excel\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/excel\/split-date-and-time-in-excel\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/02\/calendar-660670_1920.jpg\",\"datePublished\":\"2023-05-18T05:52:00+00:00\",\"dateModified\":\"2023-06-21T17:17:59+00:00\",\"description\":\"How to split Excel date and time in two columns? Let's see how to separate a date in two columns with a practical example in an Excel file.\",\"breadcrumb\":{\"@id\":\"https:\/\/expert-only.com\/en\/excel\/split-date-and-time-in-excel\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/expert-only.com\/en\/excel\/split-date-and-time-in-excel\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/excel\/split-date-and-time-in-excel\/#primaryimage\",\"url\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/02\/calendar-660670_1920.jpg\",\"contentUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/02\/calendar-660670_1920.jpg\",\"width\":1920,\"height\":1080,\"caption\":\"days of calendar\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/expert-only.com\/en\/excel\/split-date-and-time-in-excel\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"en\",\"item\":\"https:\/\/expert-only.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How to split date and time in Excel into separate columns?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/expert-only.com\/en\/#website\",\"url\":\"https:\/\/expert-only.com\/en\/\",\"name\":\"SQL and IT Tutorials\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/expert-only.com\/en\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/expert-only.com\/en\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/expert-only.com\/en\/#organization\",\"name\":\"Expert-Only\",\"url\":\"https:\/\/expert-only.com\/en\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg\",\"contentUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg\",\"width\":381,\"height\":174,\"caption\":\"Expert-Only\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/ExpertOnlyCom\/\",\"https:\/\/x.com\/expert_only\",\"https:\/\/www.youtube.com\/channel\/UCMS5sR_FwAetB0FmciNvUaA\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef\",\"name\":\"Expert-Only\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g\",\"caption\":\"Expert-Only\"}}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Split date and time in Excel into separate columns - Excel Tips","description":"How to split Excel date and time in two columns? Let's see how to separate a date in two columns with a practical example in an Excel file.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/expert-only.com\/en\/excel\/split-date-and-time-in-excel\/","og_locale":"en_US","og_type":"article","og_title":"How to split date and time in Excel into separate columns?","og_description":"How to split Excel date and time in two columns? Let's see how to separate a date in two columns with a practical example in an Excel file.","og_url":"https:\/\/expert-only.com\/en\/excel\/split-date-and-time-in-excel\/","og_site_name":"SQL and IT Tutorials","article_publisher":"https:\/\/www.facebook.com\/ExpertOnlyCom\/","article_published_time":"2023-05-18T05:52:00+00:00","article_modified_time":"2023-06-21T17:17:59+00:00","og_image":[{"width":1920,"height":1080,"url":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/02\/calendar-660670_1920.jpg","type":"image\/jpeg"}],"author":"Expert-Only","twitter_card":"summary_large_image","twitter_creator":"@expert_only","twitter_site":"@expert_only","twitter_misc":{"Written by":"Expert-Only","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/expert-only.com\/en\/excel\/split-date-and-time-in-excel\/#article","isPartOf":{"@id":"https:\/\/expert-only.com\/en\/excel\/split-date-and-time-in-excel\/"},"author":{"name":"Expert-Only","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef"},"headline":"How to split date and time in Excel into separate columns?","datePublished":"2023-05-18T05:52:00+00:00","dateModified":"2023-06-21T17:17:59+00:00","mainEntityOfPage":{"@id":"https:\/\/expert-only.com\/en\/excel\/split-date-and-time-in-excel\/"},"wordCount":398,"commentCount":0,"publisher":{"@id":"https:\/\/expert-only.com\/en\/#organization"},"image":{"@id":"https:\/\/expert-only.com\/en\/excel\/split-date-and-time-in-excel\/#primaryimage"},"thumbnailUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/02\/calendar-660670_1920.jpg","articleSection":["Excel Tips"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/expert-only.com\/en\/excel\/split-date-and-time-in-excel\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/expert-only.com\/en\/excel\/split-date-and-time-in-excel\/","url":"https:\/\/expert-only.com\/en\/excel\/split-date-and-time-in-excel\/","name":"Split date and time in Excel into separate columns - Excel Tips","isPartOf":{"@id":"https:\/\/expert-only.com\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/expert-only.com\/en\/excel\/split-date-and-time-in-excel\/#primaryimage"},"image":{"@id":"https:\/\/expert-only.com\/en\/excel\/split-date-and-time-in-excel\/#primaryimage"},"thumbnailUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/02\/calendar-660670_1920.jpg","datePublished":"2023-05-18T05:52:00+00:00","dateModified":"2023-06-21T17:17:59+00:00","description":"How to split Excel date and time in two columns? Let's see how to separate a date in two columns with a practical example in an Excel file.","breadcrumb":{"@id":"https:\/\/expert-only.com\/en\/excel\/split-date-and-time-in-excel\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/expert-only.com\/en\/excel\/split-date-and-time-in-excel\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/excel\/split-date-and-time-in-excel\/#primaryimage","url":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/02\/calendar-660670_1920.jpg","contentUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/02\/calendar-660670_1920.jpg","width":1920,"height":1080,"caption":"days of calendar"},{"@type":"BreadcrumbList","@id":"https:\/\/expert-only.com\/en\/excel\/split-date-and-time-in-excel\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"en","item":"https:\/\/expert-only.com\/en\/"},{"@type":"ListItem","position":2,"name":"How to split date and time in Excel into separate columns?"}]},{"@type":"WebSite","@id":"https:\/\/expert-only.com\/en\/#website","url":"https:\/\/expert-only.com\/en\/","name":"SQL and IT Tutorials","description":"","publisher":{"@id":"https:\/\/expert-only.com\/en\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/expert-only.com\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/expert-only.com\/en\/#organization","name":"Expert-Only","url":"https:\/\/expert-only.com\/en\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/","url":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg","contentUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg","width":381,"height":174,"caption":"Expert-Only"},"image":{"@id":"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/ExpertOnlyCom\/","https:\/\/x.com\/expert_only","https:\/\/www.youtube.com\/channel\/UCMS5sR_FwAetB0FmciNvUaA"]},{"@type":"Person","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef","name":"Expert-Only","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g","caption":"Expert-Only"}}]}},"_links":{"self":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/5483","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/comments?post=5483"}],"version-history":[{"count":0,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/5483\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media\/5509"}],"wp:attachment":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media?parent=5483"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/categories?post=5483"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/tags?post=5483"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}