{"id":4358,"date":"2023-02-22T15:37:53","date_gmt":"2023-02-22T15:37:53","guid":{"rendered":"https:\/\/www.bizinfograph.com\/resource\/?p=4358"},"modified":"2024-05-24T15:31:30","modified_gmt":"2024-05-24T15:31:30","slug":"how-to-use-datevalue-function-in-excel","status":"publish","type":"post","link":"https:\/\/www.bizinfograph.com\/resource\/how-to-use-datevalue-function-in-excel\/","title":{"rendered":"How to use DATEVALUE Function in Excel? DATEVALUE Function Utilization techniques."},"content":{"rendered":"<p>DATEVALUE Function in Excel is a powerful tool for converting text representations of dates into proper date values, enabling efficient date-based calculations and analysis. By leveraging the DATEVALUE Function, you can transform date data stored as text into a format that Excel recognizes, ensuring accuracy and consistency in your spreadsheets. Say goodbye to manual date conversion processes and hello to streamlined data manipulation with this essential function. Whether you&#8217;re working with imported data, generating reports, or performing date-based calculations, the DATEVALUE Function in Excel provides the flexibility and precision you need. Embrace the convenience of this feature to enhance your data management tasks and unlock new insights from your date-related data. With just a simple formula, you can convert text dates into usable date values, making your Excel experience more efficient and reliable.<\/p>\n<p><strong>\u00a0<\/strong><strong>This Content Covers:<\/strong><\/p>\n<ol>\n<li><a href=\"#definition-of-datevalue-function.\"><strong>Definition of DATEVALUE Function.<\/strong><\/a><\/li>\n<li><a href=\"#syntax,-arguments-and-purpose-of-datevalue-function.\"><strong>Syntax, Arguments and Purpose of DATEVALUE Function.<\/strong><\/a>\n<ul>\n<li><strong>How to use DATEVALUE Function in Excel?<\/strong><\/li>\n<li><strong>By Inserting a Date in the Function<\/strong><\/li>\n<\/ul>\n<\/li>\n<li><a href=\"#by-using-cell-references-in-the-function\"><strong>By using Cell References in the Function<\/strong><\/a><\/li>\n<li><a href=\"#cell-formatting-for-datevalue-function-with-format-cells-dialogue-box.\"><strong>Cell Formatting for DATEVALUE Function with Format Cells Dialogue Box.<\/strong><\/a><\/li>\n<\/ol>\n<h2><strong id=\"definition-of-datevalue-function.\">1. Definition of DATEVALUE Function.<\/strong><\/h2>\n<p>The DATEVALUE function in Excel is an in-built function which converts a date that is represented as a text string into a genuine Excel date. Dates imported from external sources that are in text format can be properly formatted and recognized by Excel using the DATEVALUE function for further analysis. The DATEVALUE Function is listed under <strong>Excel Date\/Time <\/strong>functions.<\/p>\n<h2><strong id=\"syntax,-arguments-and-purpose-of-datevalue-function.\">2. Syntax, Arguments and Purpose of DATEVALUE Function.<\/strong><\/h2>\n<p><strong>Syntax: <\/strong><\/p>\n<p><strong>=DATEVALUE(date_text)<\/strong><\/p>\n<p><strong>Purpose: <\/strong><\/p>\n<p>Transform a textual date into a real date<\/p>\n<p><strong>Arguments: <\/strong><\/p>\n<p><strong>date_text-<\/strong> A valid date in text format.<\/p>\n<h2><strong id=\"by-using-cell-references-in-the-function\">3. How to use DATEVALUE Function in Excel?<\/strong><\/h2>\n<h3><strong>3.1 By Inserting a Date in the Function<\/strong><\/h3>\n<p>Numerous date formats can be used when directly entering dates into the DATEVALUE function.<\/p>\n<p><strong>Step 1: <\/strong>Select a cell, enter a date with your preferred date format inside the DATEVALUE function, and hit Enter key.<\/p>\n<p><img fetchpriority=\"high\" decoding=\"async\" class=\"aligncenter wp-image-4360 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-1.png\" alt=\"DATEVALUE Function in Excel\" width=\"568\" height=\"354\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-1.png 568w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-1-300x187.png 300w\" sizes=\"(max-width: 568px) 100vw, 568px\" \/><\/p>\n<p><strong>Step 2:<\/strong> Each time you enter a different date into a cell with a different date format, that cell will receive serial number for the dates.<\/p>\n<p><img decoding=\"async\" class=\"aligncenter wp-image-4361 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-2.png\" alt=\"DATEVALUE Function in Excel\" width=\"553\" height=\"265\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-2.png 553w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-2-300x144.png 300w\" sizes=\"(max-width: 553px) 100vw, 553px\" \/><\/p>\n<p><strong>Step 3: <\/strong>To give all the numerical dates an appropriate date format appearance, select them all and then select <strong>Long Date<\/strong> or <strong>Short Date.<\/strong><\/p>\n<p><img decoding=\"async\" class=\"aligncenter wp-image-4362 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-3.png\" alt=\"DATEVALUE Function in Excel\" width=\"655\" height=\"287\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-3.png 655w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-3-300x131.png 300w\" sizes=\"(max-width: 655px) 100vw, 655px\" \/><\/p>\n<h3><strong>3.2 By using Cell References in the Function<\/strong><\/h3>\n<p>We will be converting these dates from Data column into legitimate dates using the DATEVALUE function.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-4363 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-4.png\" alt=\"DATEVALUE Function in Excel\" width=\"653\" height=\"343\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-4.png 653w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-4-300x158.png 300w\" sizes=\"(max-width: 653px) 100vw, 653px\" \/><\/p>\n<p><strong>Step 1: <\/strong>Select cell B2 and insert the formula given below inside it and click Enter key.<\/p>\n<p><strong>=DATEVALUE(A2)<\/strong><\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-4364 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-5.png\" alt=\"DATEVALUE Function in Excel\" width=\"666\" height=\"334\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-5.png 666w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-5-300x150.png 300w\" sizes=\"(max-width: 666px) 100vw, 666px\" \/><\/p>\n<p><strong>Step 2: <\/strong>When you hit Enter key, the formula will return a numeric value inside the cell which is the date\u2019s serial number. Now right click on the lower right corner of cell B2 and drag it down to B6.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-4365 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-6.png\" alt=\"DATEVALUE Function in Excel\" width=\"663\" height=\"328\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-6.png 663w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-6-300x148.png 300w\" sizes=\"(max-width: 663px) 100vw, 663px\" \/><\/p>\n<p><strong>Step 3: <\/strong>Here you can see cell B6 resulted in <strong>#VALUE<\/strong> error. This happened because the date in A6 was not formatted as text. If you format it as text then the formula will return a number value just like the rest of them.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-4366 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-7.png\" alt=\"DATEVALUE Function in Excel\" width=\"646\" height=\"314\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-7.png 646w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-7-300x146.png 300w\" sizes=\"(max-width: 646px) 100vw, 646px\" \/><\/p>\n<p><strong>Step 4: <\/strong>Select the number values and choose <strong>Short Date<\/strong> or <strong>Long Date<\/strong> from this drop-down list.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-4367 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-8.png\" alt=\"DATEVALUE Function in Excel\" width=\"643\" height=\"274\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-8.png 643w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-8-300x128.png 300w\" sizes=\"(max-width: 643px) 100vw, 643px\" \/><\/p>\n<h2><strong id=\"cell-formatting-for-datevalue-function-with-format-cells-dialogue-box.\">4. Cell Formatting for DATEVALUE Function with Format Cells Dialogue Box.<\/strong><\/h2>\n<p>Suppose you have created some numeric date values using any of the above-shown methods and you don\u2019t want to use the Long Date and Short Date to format them. Instead, you want to choose a custom date format. You can do that with the help of <strong>Format Cells<\/strong> dialogue box.<\/p>\n<p><strong>Step 1: <\/strong>Select the cells and right click on them. Choose <strong>Format Cells.<\/strong> Or select the cells and use <strong>CTRL+1<\/strong> shortcut to open <strong>Format Cells<\/strong> dialogue box.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-4368 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-9.png\" alt=\"DATEVALUE Function in Excel\" width=\"563\" height=\"322\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-9.png 563w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-9-300x172.png 300w\" sizes=\"(max-width: 563px) 100vw, 563px\" \/><\/p>\n<p><strong>Step 2: <\/strong>Go to <strong>Number Tab&gt;&gt;Date<\/strong> and choose your preferred date format. Click OK.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-4369 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-10.png\" alt=\"DATEVALUE Function in Excel\" width=\"479\" height=\"436\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-10.png 479w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-10-300x273.png 300w\" sizes=\"(max-width: 479px) 100vw, 479px\" \/><\/p>\n<p><strong>Step 3: <\/strong>All the cells containing DATEVALUE formula have been formatted into the selected date format.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter wp-image-4370 size-full\" src=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-11.png\" alt=\"DATEVALUE Function in Excel\" width=\"651\" height=\"217\" srcset=\"https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-11.png 651w, https:\/\/www.bizinfograph.com\/resource\/wp-content\/uploads\/2023\/02\/DATEVALUE-Function-11-300x100.png 300w\" sizes=\"(max-width: 651px) 100vw, 651px\" \/><\/p>\n<h2>Application of DATEVALUE Function in Excel<\/h2>\n<ul>\n<li><strong>Date Conversion<\/strong>: Utilize DATEVALUE Function in Excel to convert text representations of dates into proper date values, ensuring consistency and accuracy in data analysis.<\/li>\n<li><strong>Imported Data<\/strong>: Convert dates imported as text into Excel into a format that can be used for calculations or analysis, improving data integrity and usability.<\/li>\n<li><strong>Data Cleaning<\/strong>: Standardize date formats by converting text dates using DATEVALUE Function, enhancing data cleanliness and consistency in spreadsheets.<\/li>\n<li><strong>Date Comparison<\/strong>: Facilitate date-based comparisons by converting text dates to date values, enabling accurate chronological sorting and analysis.<\/li>\n<li><strong>Conditional Formatting<\/strong>: Apply conditional formatting based on date values derived from text using DATEVALUE Function, enhancing data visualization and analysis.<\/li>\n<li><strong>Chart Creation<\/strong>: Convert text dates to date values for use in creating charts or graphs, allowing for accurate representation and analysis of time-series data.<\/li>\n<\/ul>\n<p>For ready-to-use Dashboard Templates:<\/p>\n<ol>\n<li><a href=\"https:\/\/www.bizinfograph.com\/dashboard-templates#Finance\" target=\"_blank\" rel=\"noopener\">Financial Dashboards<\/a><\/li>\n<li><a href=\"https:\/\/www.bizinfograph.com\/dashboard-templates#Sales\" target=\"_blank\" rel=\"noopener\">Sales Dashboards<\/a><\/li>\n<li><a href=\"https:\/\/www.bizinfograph.com\/dashboard-templates#Human-Resource\" target=\"_blank\" rel=\"noopener\">HR Dashboards<\/a><\/li>\n<li><a href=\"https:\/\/www.executiveknowledge.org\/dashboard-templates\/35\" target=\"_blank\" rel=\"noopener\">Data Visualization Charts<\/a><\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"<p>DATEVALUE Function in Excel is a powerful tool for converting text representations of dates into proper date values, enabling efficient date-based calculations and analysis. By leveraging the DATEVALUE Function, you can transform date data stored as text into a format that Excel recognizes, ensuring accuracy and consistency in your spreadsheets. Say goodbye to manual date &hellip;<\/p>\n<p class=\"read-more\"> <a class=\"\" href=\"https:\/\/www.bizinfograph.com\/resource\/how-to-use-datevalue-function-in-excel\/\"> <span class=\"screen-reader-text\">How to use DATEVALUE Function in Excel? DATEVALUE Function Utilization techniques.<\/span> Read More &raquo;<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-global-header-display":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","footnotes":""},"categories":[572],"tags":[574,573],"class_list":["post-4358","post","type-post","status-publish","format-standard","hentry","category-datevalue-function","tag-datevalue-function","tag-datevalue-function-in-excel"],"_links":{"self":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts\/4358","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/comments?post=4358"}],"version-history":[{"count":6,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts\/4358\/revisions"}],"predecessor-version":[{"id":9251,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/posts\/4358\/revisions\/9251"}],"wp:attachment":[{"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/media?parent=4358"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/categories?post=4358"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.bizinfograph.com\/resource\/wp-json\/wp\/v2\/tags?post=4358"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}