{"id":2264,"date":"2021-04-09T11:37:58","date_gmt":"2021-04-09T11:37:58","guid":{"rendered":"http:\/\/marstraining.in\/?p=2264"},"modified":"2021-04-09T11:52:14","modified_gmt":"2021-04-09T11:52:14","slug":"how-to-resolve-date-issues-in-ms-excel","status":"publish","type":"post","link":"https:\/\/marstraining.in\/?p=2264","title":{"rendered":"How To Resolve Date Issues In MS Excel?"},"content":{"rendered":"

Date format<\/strong> is always a big issue, especially when you are extracting or receiving raw data from different sources(software). It becomes more challenging especially when we get raw data from SAP, ERP, ORACLE, FOXPRO and accounting software etc. We keep on struggling to correct our date format so that we can proceed with report and sometime it takes a lot of time. Remember Date field is very important field and play a vital role in report. In fact your report is incomplete without Date field. We can\u2019t imagine our report unless we correct raw data Date field format as per our system format.<\/p>\n

This article will explain you \u201cHow to correct Date format\u201d especially when it is not matching with your system Date format.<\/p>\n

    \n
  1. In your Excel worksheet, select a column of text entries you want to convert to dates(It can correct one column entry at a time).<\/li>\n
  2. Go to the\u00a0Data <\/em>tab->Data Tools<\/em>\u00a0group, and click\u00a0Text to Columns.<\/strong><\/strong>\"\"<\/a><\/li>\n
  3. In step 1 of the\u00a0Convert Text to Columns Wizard<\/em>, select\u00a0Delimited <\/strong>and click\u00a0Next<\/em>.\"\"<\/a><\/li>\n
  4. In step 2 of the wizard, uncheck all delimiter boxes and click\u00a0Next<\/em>.\"\"<\/a><\/li>\n
  5. In the final step, select\u00a0Date <\/strong>under\u00a0Column data format,<\/em>\u00a0choose the format\u00a0corresponding to your dates<\/strong>, and click Finish.<\/em>In this example, we are converting the text dates formatted as “01 02 2015” (month day year), so we select MDY<\/em>\u00a0from the drop down box.<\/li>\n<\/ol>\n

    \"\"<\/a><\/p>\n

    Now, Excel recognizes your text strings as dates, automatically converts them to your default date format and displays right-aligned in the cells. You can\u00a0change the date display format\u00a0in the usual way via the\u00a0Format Cells<\/em>\u00a0dialog.<\/p>\n

    Format of a valid date field can be changed by going to format cell. There are various pre-defined formats or an user can customize format by selecting custom format from the list. The keyboard shortcut for format cell is CTRL+1<\/strong><\/p>\n

    Important Note.<\/strong>\u00a0For the\u00a0Text to Column<\/em>\u00a0wizard to work correctly, all of your text strings should be formatted identically. For example, if some of your entries are formatted like\u00a0day\/month\/year <\/em>format while others are\u00a0month\/day\/year<\/em>, you would get incorrect results.<\/p>\n

     <\/p>\n","protected":false},"excerpt":{"rendered":"

    Date format is always a big issue, especially when you are extracting or receiving raw data from different sources(software). It becomes more challenging especially when we get raw data from SAP, ERP, ORACLE, FOXPRO and accounting software etc. We keep on struggling to correct our date format so that we can proceed with report and […]<\/p>\n","protected":false},"author":1,"featured_media":2269,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"ocean_post_layout":"","ocean_both_sidebars_style":"","ocean_both_sidebars_content_width":0,"ocean_both_sidebars_sidebars_width":0,"ocean_sidebar":"0","ocean_second_sidebar":"0","ocean_disable_margins":"enable","ocean_add_body_class":"","ocean_shortcode_before_top_bar":"","ocean_shortcode_after_top_bar":"","ocean_shortcode_before_header":"","ocean_shortcode_after_header":"","ocean_has_shortcode":"","ocean_shortcode_after_title":"","ocean_shortcode_before_footer_widgets":"","ocean_shortcode_after_footer_widgets":"","ocean_shortcode_before_footer_bottom":"","ocean_shortcode_after_footer_bottom":"","ocean_display_top_bar":"default","ocean_display_header":"default","ocean_header_style":"","ocean_center_header_left_menu":"0","ocean_custom_header_template":"0","ocean_custom_logo":0,"ocean_custom_retina_logo":0,"ocean_custom_logo_max_width":0,"ocean_custom_logo_tablet_max_width":0,"ocean_custom_logo_mobile_max_width":0,"ocean_custom_logo_max_height":0,"ocean_custom_logo_tablet_max_height":0,"ocean_custom_logo_mobile_max_height":0,"ocean_header_custom_menu":"0","ocean_menu_typo_font_family":"0","ocean_menu_typo_font_subset":"","ocean_menu_typo_font_size":0,"ocean_menu_typo_font_size_tablet":0,"ocean_menu_typo_font_size_mobile":0,"ocean_menu_typo_font_size_unit":"px","ocean_menu_typo_font_weight":"","ocean_menu_typo_font_weight_tablet":"","ocean_menu_typo_font_weight_mobile":"","ocean_menu_typo_transform":"","ocean_menu_typo_transform_tablet":"","ocean_menu_typo_transform_mobile":"","ocean_menu_typo_line_height":0,"ocean_menu_typo_line_height_tablet":0,"ocean_menu_typo_line_height_mobile":0,"ocean_menu_typo_line_height_unit":"","ocean_menu_typo_spacing":0,"ocean_menu_typo_spacing_tablet":0,"ocean_menu_typo_spacing_mobile":0,"ocean_menu_typo_spacing_unit":"","ocean_menu_link_color":"","ocean_menu_link_color_hover":"","ocean_menu_link_color_active":"","ocean_menu_link_background":"","ocean_menu_link_hover_background":"","ocean_menu_link_active_background":"","ocean_menu_social_links_bg":"","ocean_menu_social_hover_links_bg":"","ocean_menu_social_links_color":"","ocean_menu_social_hover_links_color":"","ocean_disable_title":"default","ocean_disable_heading":"default","ocean_post_title":"","ocean_post_subheading":"","ocean_post_title_style":"","ocean_post_title_background_color":"","ocean_post_title_background":0,"ocean_post_title_bg_image_position":"","ocean_post_title_bg_image_attachment":"","ocean_post_title_bg_image_repeat":"","ocean_post_title_bg_image_size":"","ocean_post_title_height":0,"ocean_post_title_bg_overlay":0.5,"ocean_post_title_bg_overlay_color":"","ocean_disable_breadcrumbs":"default","ocean_breadcrumbs_color":"","ocean_breadcrumbs_separator_color":"","ocean_breadcrumbs_links_color":"","ocean_breadcrumbs_links_hover_color":"","ocean_display_footer_widgets":"default","ocean_display_footer_bottom":"default","ocean_custom_footer_template":"0","_vp_format_video_url":"","_vp_image_focal_point":[],"_joinchat":[],"ocean_post_oembed":"","ocean_post_self_hosted_media":"","ocean_post_video_embed":"","ocean_link_format":"","ocean_link_format_target":"self","ocean_quote_format":"","ocean_quote_format_link":"post","ocean_gallery_link_images":"off","ocean_gallery_id":[],"footnotes":""},"categories":[36],"tags":[],"class_list":["post-2264","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-blogs","entry","has-media","owp-thumbs-layout-horizontal","owp-btn-normal","owp-tabs-layout-horizontal","has-no-thumbnails","has-product-nav"],"yoast_head":"\nHow To Resolve Date Issues In MS Excel? - MARStraining<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/marstraining.in\/?p=2264\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"How To Resolve Date Issues In MS Excel? - MARStraining\" \/>\n<meta property=\"og:description\" content=\"Date format is always a big issue, especially when you are extracting or receiving raw data from different sources(software). It becomes more challenging especially when we get raw data from SAP, ERP, ORACLE, FOXPRO and accounting software etc. We keep on struggling to correct our date format so that we can proceed with report and […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/marstraining.in\/?p=2264\" \/>\n<meta property=\"og:site_name\" content=\"MARStraining\" \/>\n<meta property=\"article:published_time\" content=\"2021-04-09T11:37:58+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-04-09T11:52:14+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/marstraining.in\/wp-content\/uploads\/2021\/04\/Date-Format-Picture-1.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"750\" \/>\n\t<meta property=\"og:image:height\" content=\"422\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Prof RASHID RIZWI\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Prof RASHID RIZWI\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/marstraining.in\\\/?p=2264#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/marstraining.in\\\/?p=2264\"},\"author\":{\"name\":\"Prof RASHID RIZWI\",\"@id\":\"https:\\\/\\\/marstraining.in\\\/#\\\/schema\\\/person\\\/a26a42a0305d3d78bfb7e54d5b9520a1\"},\"headline\":\"How To Resolve Date Issues In MS Excel?\",\"datePublished\":\"2021-04-09T11:37:58+00:00\",\"dateModified\":\"2021-04-09T11:52:14+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/marstraining.in\\\/?p=2264\"},\"wordCount\":366,\"commentCount\":6,\"publisher\":{\"@id\":\"https:\\\/\\\/marstraining.in\\\/#organization\"},\"image\":{\"@id\":\"https:\\\/\\\/marstraining.in\\\/?p=2264#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/marstraining.in\\\/wp-content\\\/uploads\\\/2021\\\/04\\\/Date-Format-Picture-1.jpg\",\"articleSection\":[\"Blogs\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/marstraining.in\\\/?p=2264#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/marstraining.in\\\/?p=2264\",\"url\":\"https:\\\/\\\/marstraining.in\\\/?p=2264\",\"name\":\"How To Resolve Date Issues In MS Excel? - MARStraining\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/marstraining.in\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/marstraining.in\\\/?p=2264#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/marstraining.in\\\/?p=2264#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/marstraining.in\\\/wp-content\\\/uploads\\\/2021\\\/04\\\/Date-Format-Picture-1.jpg\",\"datePublished\":\"2021-04-09T11:37:58+00:00\",\"dateModified\":\"2021-04-09T11:52:14+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/marstraining.in\\\/?p=2264#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/marstraining.in\\\/?p=2264\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/marstraining.in\\\/?p=2264#primaryimage\",\"url\":\"https:\\\/\\\/marstraining.in\\\/wp-content\\\/uploads\\\/2021\\\/04\\\/Date-Format-Picture-1.jpg\",\"contentUrl\":\"https:\\\/\\\/marstraining.in\\\/wp-content\\\/uploads\\\/2021\\\/04\\\/Date-Format-Picture-1.jpg\",\"width\":750,\"height\":422},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/marstraining.in\\\/?p=2264#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/marstraining.in\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"How To Resolve Date Issues In MS Excel?\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/marstraining.in\\\/#website\",\"url\":\"https:\\\/\\\/marstraining.in\\\/\",\"name\":\"MARStraining\",\"description\":\"We Enhance Professionals\",\"publisher\":{\"@id\":\"https:\\\/\\\/marstraining.in\\\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/marstraining.in\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/marstraining.in\\\/#organization\",\"name\":\"MARStraining\",\"url\":\"https:\\\/\\\/marstraining.in\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/marstraining.in\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/marstraining.in\\\/wp-content\\\/uploads\\\/2019\\\/12\\\/logo.png\",\"contentUrl\":\"https:\\\/\\\/marstraining.in\\\/wp-content\\\/uploads\\\/2019\\\/12\\\/logo.png\",\"width\":175,\"height\":50,\"caption\":\"MARStraining\"},\"image\":{\"@id\":\"https:\\\/\\\/marstraining.in\\\/#\\\/schema\\\/logo\\\/image\\\/\"}},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/marstraining.in\\\/#\\\/schema\\\/person\\\/a26a42a0305d3d78bfb7e54d5b9520a1\",\"name\":\"Prof RASHID RIZWI\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/1518a55275e34b8fb9f8045626baba9f033b94c3b4fd450134a386a5f5c5dbdc?s=96&d=mm&r=g\",\"url\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/1518a55275e34b8fb9f8045626baba9f033b94c3b4fd450134a386a5f5c5dbdc?s=96&d=mm&r=g\",\"contentUrl\":\"https:\\\/\\\/secure.gravatar.com\\\/avatar\\\/1518a55275e34b8fb9f8045626baba9f033b94c3b4fd450134a386a5f5c5dbdc?s=96&d=mm&r=g\",\"caption\":\"Prof RASHID RIZWI\"},\"url\":\"https:\\\/\\\/marstraining.in\\\/?author=1\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"How To Resolve Date Issues In MS Excel? - MARStraining","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:\/\/marstraining.in\/?p=2264","og_locale":"en_US","og_type":"article","og_title":"How To Resolve Date Issues In MS Excel? - MARStraining","og_description":"Date format is always a big issue, especially when you are extracting or receiving raw data from different sources(software). It becomes more challenging especially when we get raw data from SAP, ERP, ORACLE, FOXPRO and accounting software etc. We keep on struggling to correct our date format so that we can proceed with report and […]","og_url":"https:\/\/marstraining.in\/?p=2264","og_site_name":"MARStraining","article_published_time":"2021-04-09T11:37:58+00:00","article_modified_time":"2021-04-09T11:52:14+00:00","og_image":[{"width":750,"height":422,"url":"https:\/\/marstraining.in\/wp-content\/uploads\/2021\/04\/Date-Format-Picture-1.jpg","type":"image\/jpeg"}],"author":"Prof RASHID RIZWI","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Prof RASHID RIZWI","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/marstraining.in\/?p=2264#article","isPartOf":{"@id":"https:\/\/marstraining.in\/?p=2264"},"author":{"name":"Prof RASHID RIZWI","@id":"https:\/\/marstraining.in\/#\/schema\/person\/a26a42a0305d3d78bfb7e54d5b9520a1"},"headline":"How To Resolve Date Issues In MS Excel?","datePublished":"2021-04-09T11:37:58+00:00","dateModified":"2021-04-09T11:52:14+00:00","mainEntityOfPage":{"@id":"https:\/\/marstraining.in\/?p=2264"},"wordCount":366,"commentCount":6,"publisher":{"@id":"https:\/\/marstraining.in\/#organization"},"image":{"@id":"https:\/\/marstraining.in\/?p=2264#primaryimage"},"thumbnailUrl":"https:\/\/marstraining.in\/wp-content\/uploads\/2021\/04\/Date-Format-Picture-1.jpg","articleSection":["Blogs"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/marstraining.in\/?p=2264#respond"]}]},{"@type":"WebPage","@id":"https:\/\/marstraining.in\/?p=2264","url":"https:\/\/marstraining.in\/?p=2264","name":"How To Resolve Date Issues In MS Excel? - MARStraining","isPartOf":{"@id":"https:\/\/marstraining.in\/#website"},"primaryImageOfPage":{"@id":"https:\/\/marstraining.in\/?p=2264#primaryimage"},"image":{"@id":"https:\/\/marstraining.in\/?p=2264#primaryimage"},"thumbnailUrl":"https:\/\/marstraining.in\/wp-content\/uploads\/2021\/04\/Date-Format-Picture-1.jpg","datePublished":"2021-04-09T11:37:58+00:00","dateModified":"2021-04-09T11:52:14+00:00","breadcrumb":{"@id":"https:\/\/marstraining.in\/?p=2264#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/marstraining.in\/?p=2264"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/marstraining.in\/?p=2264#primaryimage","url":"https:\/\/marstraining.in\/wp-content\/uploads\/2021\/04\/Date-Format-Picture-1.jpg","contentUrl":"https:\/\/marstraining.in\/wp-content\/uploads\/2021\/04\/Date-Format-Picture-1.jpg","width":750,"height":422},{"@type":"BreadcrumbList","@id":"https:\/\/marstraining.in\/?p=2264#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/marstraining.in\/"},{"@type":"ListItem","position":2,"name":"How To Resolve Date Issues In MS Excel?"}]},{"@type":"WebSite","@id":"https:\/\/marstraining.in\/#website","url":"https:\/\/marstraining.in\/","name":"MARStraining","description":"We Enhance Professionals","publisher":{"@id":"https:\/\/marstraining.in\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/marstraining.in\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/marstraining.in\/#organization","name":"MARStraining","url":"https:\/\/marstraining.in\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/marstraining.in\/#\/schema\/logo\/image\/","url":"https:\/\/marstraining.in\/wp-content\/uploads\/2019\/12\/logo.png","contentUrl":"https:\/\/marstraining.in\/wp-content\/uploads\/2019\/12\/logo.png","width":175,"height":50,"caption":"MARStraining"},"image":{"@id":"https:\/\/marstraining.in\/#\/schema\/logo\/image\/"}},{"@type":"Person","@id":"https:\/\/marstraining.in\/#\/schema\/person\/a26a42a0305d3d78bfb7e54d5b9520a1","name":"Prof RASHID RIZWI","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1518a55275e34b8fb9f8045626baba9f033b94c3b4fd450134a386a5f5c5dbdc?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1518a55275e34b8fb9f8045626baba9f033b94c3b4fd450134a386a5f5c5dbdc?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1518a55275e34b8fb9f8045626baba9f033b94c3b4fd450134a386a5f5c5dbdc?s=96&d=mm&r=g","caption":"Prof RASHID RIZWI"},"url":"https:\/\/marstraining.in\/?author=1"}]}},"_links":{"self":[{"href":"https:\/\/marstraining.in\/index.php?rest_route=\/wp\/v2\/posts\/2264","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/marstraining.in\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/marstraining.in\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/marstraining.in\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/marstraining.in\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2264"}],"version-history":[{"count":3,"href":"https:\/\/marstraining.in\/index.php?rest_route=\/wp\/v2\/posts\/2264\/revisions"}],"predecessor-version":[{"id":2272,"href":"https:\/\/marstraining.in\/index.php?rest_route=\/wp\/v2\/posts\/2264\/revisions\/2272"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/marstraining.in\/index.php?rest_route=\/wp\/v2\/media\/2269"}],"wp:attachment":[{"href":"https:\/\/marstraining.in\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2264"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/marstraining.in\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2264"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/marstraining.in\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2264"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}