• 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

Change Dates|Power Query|Excel Forum|My Online Training Hub

You are here: Home / Change Dates|Power Query|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 ForumPower QueryChange Dates
sp_PrintTopic sp_TopicIcon
Change Dates
Avatar
jomili
Member
Members
Level 0
Forum Posts: 9
Member Since:
June 27, 2016
sp_UserOfflineSmall Offline
1
March 25, 2021 - 11:15 pm
sp_Permalink sp_Print

I eagerly read the article at https://www.myonlinetraininghu.....ower-query thinking it would be an easy solution to my problem, but doesn't appear to be.  Not knowing any better, I posted a question there, then realized it should have been here.  So here it is.  My apologies.  

My February file came (from the vendor) with the dates in Column C.  Visually, they're 2/1/21, 2/10/21, etc, which is appropriate for February.  However, internally the cell shows a different date.  For instance, C13 shows "02/01/2021 1:36 AM", but the internal date is 1/2/2021 1:36:00 AM, a value of 44198.07.  The internal date needs to be "02/01/2021 1:36 AM", a value of 44228.07.

I thought the post would help with that, but I can't get it to work.  The only solution I've been able to find is the formula =--IF(N(C13),TEXT(C13,"d/m/yy h:m"),C13)

Can Power Query do this, or is there a better way? I appreciate any help I can get.  

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
2
March 26, 2021 - 12:52 pm
sp_Permalink sp_Print sp_EditHistory

Hi Jomili,

Can you please upload a sample file from your vendor so we can see how the dates are stored? Note: it's important the file is original, and hasn't been opened in Excel on your PC (assuming it's a csv or text file).

Thanks,

Mynda

Avatar
jomili
Member
Members
Level 0
Forum Posts: 9
Member Since:
June 27, 2016
sp_UserOfflineSmall Offline
3
March 26, 2021 - 10:47 pm
sp_Permalink sp_Print

I'm so sorry!  I DID add an attachment with my original post, not sure where it went.  Trying it again.  

Avatar
jomili
Member
Members
Level 0
Forum Posts: 9
Member Since:
June 27, 2016
sp_UserOfflineSmall Offline
4
March 26, 2021 - 10:49 pm
sp_Permalink sp_Print

BTW, it didn't come as a CSV or text file, just a vanilla Excel file.  

Avatar
jomili
Member
Members
Level 0
Forum Posts: 9
Member Since:
June 27, 2016
sp_UserOfflineSmall Offline
5
March 30, 2021 - 10:54 pm
sp_Permalink sp_Print

Still desiring some help. 

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
6
March 31, 2021 - 2:56 pm
sp_Permalink sp_Print

Hi Jomili,

Sorry for the delayed reply and thanks for sharing your file. Whoever opened the file in Excel originally has messed up the dates. This data must have originally been in a different format (CSV/Text etc.) and then opened in Excel. The source file had a different date format (mm/dd/yyyy) to the version of Excel on the person's PC who opened the file in Excel (dd/mm/yyyy), and as a result unbeknown to them the dates have been messed up. You can see this when you look at the filters for column C (see screenshot attached), where dates with a day of 13 onward have been imported as text. You can tell they're text because they haven't been included in the month groupings in the filter drop down list.

 jomili_dates.pngImage Enlarger

If you cannot get the original source file that you can import using Power Query, then you'll need to fix the dates manually. You can see that your formula has fixed dates on days 1 through 12, and for dates 13 through 31st your formula returns an error, so you could change your formula to this:

=IFERROR(TEXT(DATE(RIGHT(TEXT(C13,"dd/mm/yyyy"),4),LEFT(TEXT(C13,"dd/mm/yyyy"),2),MID(TEXT(C13,"dd/mm/yyyy"),4,2))+C13-INT(C13),"mm/dd/yyyy hh:mm AM/PM"),C13)

 

Then copy and paste the formula as values, then convert the values to numbers/dates using Text to Columns on the Data tab of the ribbon.

Hope that points you in the right direction.

Mynda

sp_PlupAttachments Attachments
  • sp_PlupImage jomili_dates.png (46 KB)
Avatar
jomili
Member
Members
Level 0
Forum Posts: 9
Member Since:
June 27, 2016
sp_UserOfflineSmall Offline
7
March 31, 2021 - 10:37 pm
sp_Permalink sp_Print

Mynda,

Thanks for the alternative formula, but not sure it's needed.  You said "and for dates 13 through 31st your formula returns an error" but it doesn't in my version of Excel, it works just fine.  Maybe a difference in our versions?

Glad to know I wasn't misapplying Power Query, but actually had a different scenario for which PQ had no solution.  

Avatar
Mynda Treacy
Admin
Level 10
Forum Posts: 4449
Member Since:
July 16, 2010
sp_UserOfflineSmall Offline
8
April 1, 2021 - 11:35 am
sp_Permalink sp_Print

ok, if you say so. I can't test at my end because I have different date settings to you, hence my alternate formula 🙂

Avatar
Anders Sehlstedt
Eskilstuna, Sweden

VIP
Members


Trusted Members
Level 3
Forum Posts: 871
Member Since:
December 7, 2016
sp_UserOfflineSmall Offline
9
April 2, 2021 - 4:29 am
sp_Permalink sp_Print

Hello,

As already mentioned by Mynda, the dates in the file is messed up, from row 2710 to the end of list the date/time value is text, in rows above it is numbers but altered with so instead of 1st of February you have 2nd of January as start date. The reason till why the dates are text from row 2710 is because Excel know that there exists no 13th month so it keeps the data as text instead.

This is a classsical import error, so if you received this Excel file from someone it means that the sending part have imported this data from somewhere, probably from a .csv file.

So either you hold on to your formula solution or you revert back to sender and ask them to do a correct data import next time.

Br,
Anders

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: AndyC
Guest(s) 9
Currently Browsing this Page:
1 Guest(s)
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 871
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
Raj Mattoo
Mark Luke
terimeri dooriyan
Jack Aston
AndyC
Denise Lloyd
michael serna
mashal sana
Tiffany Kang
Leah Gillmore
Forum Stats:
Groups: 3
Forums: 24
Topics: 6219
Posts: 27276

 

Member Stats:
Guest Posters: 49
Members: 31903
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.