{"id":326,"date":"2018-06-27T17:23:07","date_gmt":"2018-06-27T17:23:07","guid":{"rendered":"http:\/\/officetuts.net\/excel\/?p=326"},"modified":"2024-03-29T13:43:49","modified_gmt":"2024-03-29T13:43:49","slug":"convert-number-to-date","status":"publish","type":"post","link":"https:\/\/officetuts.net\/excel\/formulas\/convert-number-to-date\/","title":{"rendered":"Convert Number to Date in Excel"},"content":{"rendered":"\n<p>Excel <a href=\"https:\/\/officetuts.net\/excel\/en\/training\/how-excel-stores-date-and-time\/\">treats date and time as numbers<\/a>&nbsp;which are then formatted into the Date and Time formats. This gives you the opportunity to convert numbers to dates. That means that you can also perform some mathematical operations on them.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Format cells window<\/h2>\n\n\n\n<p>The first way to create a date from a number is to use the Format Cells feature. It will <a href=\"https:\/\/officetuts.net\/excel\/examples\/convert-text-or-number-to-date\/\">convert a&nbsp;number to date<\/a> without using formulas.<\/p>\n\n\n\n<p>Look at the following example.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"154\" height=\"140\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/number-example.png\" alt=\"\" class=\"wp-image-327\"\/><\/figure>\n\n\n\n<p>It consists of several numbers. Let\u2019s copy them to column&nbsp;<strong>B<\/strong>.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"218\" height=\"140\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/number-and-date-example.png\" alt=\"\" class=\"wp-image-328\"\/><\/figure>\n\n\n\n<p>Select cells from&nbsp;<strong>B2<\/strong>&nbsp;to&nbsp;<strong>B5<\/strong>&nbsp;and use the&nbsp;<strong><span class=\"shortcut\">Ctrl + 1<\/span><\/strong>&nbsp;keyboard shortcut.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"525\" height=\"460\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/format-cells-window.png\" alt=\"\" class=\"wp-image-329\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/format-cells-window.png 525w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/format-cells-window-300x263.png 300w\" sizes=\"(max-width: 525px) 100vw, 525px\" \/><\/figure>\n\n\n\n<p>From&nbsp;<strong>Date,<\/strong>&nbsp;you can choose one of the different data formats. This will give us the following result.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"229\" height=\"140\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/number-and-date-example2.png\" alt=\"\" class=\"wp-image-330\"\/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">Convert number to date with formula<\/h2>\n\n\n\n<p>The&nbsp;<strong>TEXT<\/strong>&nbsp;function <a href=\"https:\/\/officetuts.net\/excel\/examples\/convert-long-numbers-to-text\/\">converts a value to text<\/a> in a specified format. For example, the formula&nbsp;will return the following text string.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=TEXT(A2, \u201cdd-mm-yy\u201d)<\/pre>\n\n\n\n<p><strong>16-02-64<\/strong><\/p>\n\n\n\n<p>We can create a date from text using the&nbsp;<strong>DATE<\/strong>&nbsp;function.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=DATE(year, month, year)<\/pre>\n\n\n\n<p>You can join both functions to create the following formula.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">=DATE(TEXT(A2,\"yy\"),TEXT(A2,\"mm\"),TEXT(A2,\"dd\"))<\/pre>\n\n\n\n<figure class=\"wp-block-image\"><img loading=\"lazy\" decoding=\"async\" width=\"330\" height=\"140\" src=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/date-formula.png\" alt=\"\" class=\"wp-image-331\" srcset=\"https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/date-formula.png 330w, https:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/date-formula-300x127.png 300w\" sizes=\"(max-width: 330px) 100vw, 330px\" \/><\/figure>\n\n\n\n<p>This formula gives us the same result as the cell formatting.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Excel treats date and time as numbers&nbsp;which are then formatted into the Date and Time formats. This gives you the opportunity to convert&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23],"tags":[190],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v22.3 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Convert Number to Date in Excel<\/title>\n<meta name=\"description\" content=\"Learn how to treat date and time as numbers, and convert them with or without formulas using Excel. Use the Format Cells feature or the TEXT function.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/officetuts.net\/excel\/formulas\/convert-number-to-date\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Convert Number to Date in Excel\" \/>\n<meta property=\"og:description\" content=\"Learn how to treat date and time as numbers, and convert them with or without formulas using Excel. Use the Format Cells feature or the TEXT function.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/officetuts.net\/excel\/formulas\/convert-number-to-date\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-06-27T17:23:07+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2024-03-29T13:43:49+00:00\" \/>\n<meta property=\"og:image\" content=\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/number-example.png\" \/>\n<meta name=\"author\" content=\"Tomasz Decker\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Tomasz Decker\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"1 minute\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/convert-number-to-date\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/convert-number-to-date\/\"},\"author\":{\"name\":\"Tomasz Decker\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"headline\":\"Convert Number to Date in Excel\",\"datePublished\":\"2018-06-27T17:23:07+00:00\",\"dateModified\":\"2024-03-29T13:43:49+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/convert-number-to-date\/\"},\"wordCount\":200,\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/convert-number-to-date\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/number-example.png\",\"keywords\":[\"pinterest\"],\"articleSection\":[\"formulas\"],\"inLanguage\":\"en-US\"},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/convert-number-to-date\/\",\"url\":\"https:\/\/officetuts.net\/excel\/formulas\/convert-number-to-date\/\",\"name\":\"Convert Number to Date in Excel\",\"isPartOf\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/convert-number-to-date\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/convert-number-to-date\/#primaryimage\"},\"thumbnailUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/number-example.png\",\"datePublished\":\"2018-06-27T17:23:07+00:00\",\"dateModified\":\"2024-03-29T13:43:49+00:00\",\"description\":\"Learn how to treat date and time as numbers, and convert them with or without formulas using Excel. Use the Format Cells feature or the TEXT function.\",\"breadcrumb\":{\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/convert-number-to-date\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/officetuts.net\/excel\/formulas\/convert-number-to-date\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/convert-number-to-date\/#primaryimage\",\"url\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/number-example.png\",\"contentUrl\":\"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/number-example.png\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/officetuts.net\/excel\/formulas\/convert-number-to-date\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/officetuts.net\/excel\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Convert Number to Date in Excel\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/officetuts.net\/excel\/#website\",\"url\":\"https:\/\/officetuts.net\/excel\/\",\"name\":\"\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/officetuts.net\/excel\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42\",\"name\":\"Tomasz Decker\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g\",\"caption\":\"Tomasz Decker\"},\"logo\":{\"@id\":\"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/\"},\"description\":\"Spreadsheet and Python enthusiast.\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Convert Number to Date in Excel","description":"Learn how to treat date and time as numbers, and convert them with or without formulas using Excel. Use the Format Cells feature or the TEXT function.","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:\/\/officetuts.net\/excel\/formulas\/convert-number-to-date\/","og_locale":"en_US","og_type":"article","og_title":"Convert Number to Date in Excel","og_description":"Learn how to treat date and time as numbers, and convert them with or without formulas using Excel. Use the Format Cells feature or the TEXT function.","og_url":"https:\/\/officetuts.net\/excel\/formulas\/convert-number-to-date\/","article_published_time":"2018-06-27T17:23:07+00:00","article_modified_time":"2024-03-29T13:43:49+00:00","og_image":[{"url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/number-example.png"}],"author":"Tomasz Decker","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Tomasz Decker","Est. reading time":"1 minute"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/officetuts.net\/excel\/formulas\/convert-number-to-date\/#article","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/formulas\/convert-number-to-date\/"},"author":{"name":"Tomasz Decker","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"headline":"Convert Number to Date in Excel","datePublished":"2018-06-27T17:23:07+00:00","dateModified":"2024-03-29T13:43:49+00:00","mainEntityOfPage":{"@id":"https:\/\/officetuts.net\/excel\/formulas\/convert-number-to-date\/"},"wordCount":200,"publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"image":{"@id":"https:\/\/officetuts.net\/excel\/formulas\/convert-number-to-date\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/number-example.png","keywords":["pinterest"],"articleSection":["formulas"],"inLanguage":"en-US"},{"@type":"WebPage","@id":"https:\/\/officetuts.net\/excel\/formulas\/convert-number-to-date\/","url":"https:\/\/officetuts.net\/excel\/formulas\/convert-number-to-date\/","name":"Convert Number to Date in Excel","isPartOf":{"@id":"https:\/\/officetuts.net\/excel\/#website"},"primaryImageOfPage":{"@id":"https:\/\/officetuts.net\/excel\/formulas\/convert-number-to-date\/#primaryimage"},"image":{"@id":"https:\/\/officetuts.net\/excel\/formulas\/convert-number-to-date\/#primaryimage"},"thumbnailUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/number-example.png","datePublished":"2018-06-27T17:23:07+00:00","dateModified":"2024-03-29T13:43:49+00:00","description":"Learn how to treat date and time as numbers, and convert them with or without formulas using Excel. Use the Format Cells feature or the TEXT function.","breadcrumb":{"@id":"https:\/\/officetuts.net\/excel\/formulas\/convert-number-to-date\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/officetuts.net\/excel\/formulas\/convert-number-to-date\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/formulas\/convert-number-to-date\/#primaryimage","url":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/number-example.png","contentUrl":"http:\/\/officetuts.net\/excel\/wp-content\/uploads\/sites\/2\/2018\/06\/number-example.png"},{"@type":"BreadcrumbList","@id":"https:\/\/officetuts.net\/excel\/formulas\/convert-number-to-date\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/officetuts.net\/excel\/"},{"@type":"ListItem","position":2,"name":"Convert Number to Date in Excel"}]},{"@type":"WebSite","@id":"https:\/\/officetuts.net\/excel\/#website","url":"https:\/\/officetuts.net\/excel\/","name":"","description":"","publisher":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/officetuts.net\/excel\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/41b0b6996aaa4c4127f86f3d24452d42","name":"Tomasz Decker","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/18cbe22837193574870ae40ba56bf712?s=96&d=mm&r=g","caption":"Tomasz Decker"},"logo":{"@id":"https:\/\/officetuts.net\/excel\/#\/schema\/person\/image\/"},"description":"Spreadsheet and Python enthusiast."}]}},"_links":{"self":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/326"}],"collection":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/comments?post=326"}],"version-history":[{"count":8,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/326\/revisions"}],"predecessor-version":[{"id":17386,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/posts\/326\/revisions\/17386"}],"wp:attachment":[{"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/media?parent=326"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/categories?post=326"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/officetuts.net\/excel\/wp-json\/wp\/v2\/tags?post=326"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}