• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Convert Date/time field into Date|General Excel Questions & Answers|Excel Forum|My Online Training Hub

You are here: Home / Convert Date/time field into Date|General Excel Questions & Answers|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumGeneral Excel Questions & Answe…Convert Date/time field into Date
sp_PrintTopic sp_TopicIcon
Convert Date/time field into Date
Avatar
Sunil B
Member
Members
Level 0
Forum Posts: 16
Member Since:
March 27, 2020
sp_UserOfflineSmall Offline
1
March 27, 2020 - 11:54 am
sp_Permalink sp_Print

Need some help in converting the date/time field of a CSV into date. In CSV it is a text field.

Also the format of the date/time field  is:  mm-dd-yyyy hh:mm:ss a/p

 When I convert the field to date where ever the dd is less than or equal to 12 it works fine but for others it gives error.

 

Attaching a sample file for reference. I am actually using power query to import the data.

 

*Not sure how to insert a file so pasting the sample data

"number","opened_at","short_description"
"INSFDSFDSFDS","01-10-2019 03:16:10 PM","TEST directory results - network names and logos"
"INSDFDSF","06-24-2019 10:13:18 AM","Issues with enet Administrators)"
"INCSDFDSFDS","07-15-2019 11:36:05 AM","Data Load"
"INDFSGERREE","08-09-2019 04:22:40 PM","Please load file for Data"

sp_AnswersTopicSeeAnswer See Answer
Avatar
Philip Treacy
Admin
Level 10
Forum Posts: 1514
Member Since:
October 5, 2010
sp_UserOfflineSmall Offline
2
March 27, 2020 - 12:46 pm
sp_Permalink sp_Print

Hi Sunil,

To attach a file click on the Attachments button below where you type you qs.  Then Add Files-> Start Upload.

Phil

Avatar
Sunil B
Member
Members
Level 0
Forum Posts: 16
Member Since:
March 27, 2020
sp_UserOfflineSmall Offline
3
March 27, 2020 - 1:02 pm
sp_Permalink sp_Print sp_EditHistory

Thanks for directing me. Attached is the sample file.

 

Thanks

Sunil

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
4
March 28, 2020 - 9:18 am
sp_Permalink sp_Print

Hello Sunil,

I would use Power Query to import the csv file and to convert the date/time column to date. Have you tried using PQ?
Check out this blog article, it might give you a good help.

/Anders

Avatar
Sunil B
Member
Members
Level 0
Forum Posts: 16
Member Since:
March 27, 2020
sp_UserOfflineSmall Offline
5
March 28, 2020 - 1:59 pm
sp_Permalink sp_Print

I have tried using PQ also but it does not work. The PQ first defines it as a Text and when I convert it to Date/Time the 1st, 3rd and 4th record errors out.

 

Thanks

Sunil

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
6
March 28, 2020 - 4:52 pm
sp_Permalink sp_Print

Hello Sunil,

As I don't know how you converted the date/time column, but as shown in attached sample file where I have used your .csv file, I don't see any errors in the date after the conversion.

Br,
Anders

Avatar
Sunil B
Member
Members
Level 0
Forum Posts: 16
Member Since:
March 27, 2020
sp_UserOfflineSmall Offline
7
March 28, 2020 - 5:34 pm
sp_Permalink sp_Print

Hi Anders thanks for helping me out. Attached is the link of the video with the process and the steps that I am trying to implement it.

https://www.youtube.com/watch?.....IvoFIyWYcU

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
8
March 28, 2020 - 10:51 pm
sp_Permalink sp_Print

Hello Sunil,

The errors you get in the video is because the first row is text. If you make the first row as headers and after that convert the date/time column to date you will be successful.

Br,
Anders

Avatar
Sunil B
Member
Members
Level 0
Forum Posts: 16
Member Since:
March 27, 2020
sp_UserOfflineSmall Offline
9
March 29, 2020 - 12:14 am
sp_Permalink sp_Print

Hi Andres I tried but still the same issue 🙁 

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
10
March 29, 2020 - 5:10 am
sp_Permalink sp_Print

Hello Sunil,

I made a simple screen recording too, so you can see the difference. Your row 1 is the column headers, for me I get the column headers correct during import so my row 1 is the first data row. You need to either remove the first line or make it as headers.

As you might also notice is that for me I don't get the AM/PM displayed during import, it is because of different regional settings used in Sweden. The time is still showing correct.

If you just fix the first row you should be good to go.

Br,
Anders

sp_PlupAttachments Attachments
  • sp_PlupMedia Media1-1.mp4 (1 MB)
Avatar
Sunil B
Member
Members
Level 0
Forum Posts: 16
Member Since:
March 27, 2020
sp_UserOfflineSmall Offline
11
March 29, 2020 - 12:18 pm
sp_Permalink sp_Print

Not sure why but it is very strange and it is driving me nuts. Such a simple thing and I am unable to implement it.

Few of the observations:

1. For you it automatically takes the first row as the header while importing but for me it does not.

2. Also for you it automatically takes the 2nd field as date/time but for me it does not.

 

Not sure what the issue is.

Andres thanks for investing time in me to sort the things out.

 

Thanks

Sunil

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 870
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
12
March 29, 2020 - 11:05 pm
sp_Permalink sp_Print sp_EditHistory

Hello Sunil,

I have played around with the regional settings and when switching over to English (United Kingdom) settings I get the same issue as you have. This is not the case when using my default Swedish settings. When setting local to English (United States) it all works fine again.

As it seems my .mp4 file can't be uploaded I try to explain in text where to check.

In Excel, go to Data tab and choose Get Data and then Query Options.
Check and try with different localizations in the Regional Settings.

With Swedish and United States locale the date/time type is set correctly, but with English locale it is identified as text. The first and fourth rows are identified, but the months and days are wrong. The second and third rows are not identified correctly as it reads day 24 as month 24, etc.

With Swedish and United States locale this is not an issue. It seems you need to play around to get it to work.

Br,
Anders

sp_AnswersTopicAnswer
Answers Post
sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Roy Lutke, Richard West, Jeff Krueger, Tom VAN LOO, Kylara Papenfuss, Nada Perovic
Guest(s) 7
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
John Chisholm
vexokeb sdfg
John Jack
Malcolm Toy
Ray-Yu Yang
George Shihadeh
Naomi Rumble
Uwe von Gostomski
Jonathan Jones
drsven
Forum Stats:
Groups: 3
Forums: 24
Topics: 6212
Posts: 27236

 

Member Stats:
Guest Posters: 49
Members: 31889
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.