Wasted 2 hours working out the solution until I found this Video, thanks a ton!! Solved in less than a min

@efficiency365

12 күн бұрын

Thanks @sarabjeetsinghkhanuja7379 Check out my other best practices videos as well. Task Management Best Practices - kzread.info/dash/bejne/dn6X2rKtpZDYk8o.html Efficient Tasks Management using the New Planner 2024 - kzread.info/dash/bejne/nZus09x9mdXdYdI.html Microsoft Teams Meetings - 16 Best Practices - kzread.info/dash/bejne/nmd22NNmeZPOqZc.html Microsoft Teams - 15 Best Practices - kzread.info/dash/bejne/iWyJyNqYqtGxqKw.html OneDrive Best Practices Part 1 - kzread.info/dash/bejne/dmuO1MipmLqaptI.html Part 2 - kzread.info/dash/bejne/oHqsmLuKqMaTkpc.html Smart and Effective Email - 5 powerful ways - kzread.info/dash/bejne/aIJ5qLWGoavNgag.html Outlook Calendar Best Practices : Part 1 - kzread.info/dash/bejne/ea6ns9Cek82ngdI.html and Part 2 - kzread.info/dash/bejne/opWYrapxqdabc9Y.html OneNote - Best Practices - kzread.info/dash/bejne/n2Foo7tqk7DPaNY.html Microsoft 365 Best Practices - kzread.info/dash/bejne/nYp3wbucfJi1krg.html Cheers. Doc.

Thank you so much , Nitin . Spent 4-5 hours just to figure out this via different videos . Finally ,found your guide and within few min it was all sorted .

@efficiency365

2 ай бұрын

Thanks @Pandey-pooja Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - kzread.info/dash/bejne/pKh4zrGLfNDQnKg.html Excel Green Marks - Error Checking - Best Practices - kzread.info/dash/bejne/ipmA1s6zlJzSgrw.html Excel Best Practices - Part 1 of 3 - Data Management - kzread.info/dash/bejne/pKaTk8qaZsqcnMY.html Excel Best Practices - Part 2 of 3 - Formulas - kzread.info/dash/bejne/na6T3KOvfay0cc4.html Excel Best Practices - Part 3 of 3 - Analytics - kzread.info/dash/bejne/fIKGl9uxj7PNZKQ.html 10 Excel Settings You Must CHANGE! - kzread.info/dash/bejne/qIym1Lqde9ywds4.html Automatic data clean up with Excel Flash Fill - kzread.info/dash/bejne/gGekwdqYnLu6j8Y.html Instant Excel Audit, Comparison and Analysis - Inquire - kzread.info/dash/bejne/lXiY2LeTqLK7mdY.html Six powerful Excel Navigation Shortcuts - kzread.info/dash/bejne/lIZh26-bd7PXZZM.html Handle millions of rows in Excel - Slow to fast - kzread.info/dash/bejne/a2ecmdSLo6-sZ7w.html Convert crosstab to tabular - Unpivot - Excel Power Query - kzread.info/dash/bejne/n4iosdaTeJDUfrA.html Cheers. Doc

Thank you so much sir. I can't believe I spent more than 3 hours just trying to fix this date problem, glad I finally conquered it. Pheew.

@efficiency365

3 ай бұрын

Thanks @juliesolace Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - kzread.info/dash/bejne/pKh4zrGLfNDQnKg.html Excel Green Marks - Error Checking - Best Practices - kzread.info/dash/bejne/ipmA1s6zlJzSgrw.html Excel Best Practices - Part 1 of 3 - Data Management - kzread.info/dash/bejne/pKaTk8qaZsqcnMY.html Excel Best Practices - Part 2 of 3 - Formulas - kzread.info/dash/bejne/na6T3KOvfay0cc4.html Excel Best Practices - Part 3 of 3 - Analytics - kzread.info/dash/bejne/fIKGl9uxj7PNZKQ.html 10 Excel Settings You Must CHANGE! - kzread.info/dash/bejne/qIym1Lqde9ywds4.html Automatic data clean up with Excel Flash Fill - kzread.info/dash/bejne/gGekwdqYnLu6j8Y.html Instant Excel Audit, Comparison and Analysis - Inquire - kzread.info/dash/bejne/lXiY2LeTqLK7mdY.html Six powerful Excel Navigation Shortcuts - kzread.info/dash/bejne/lIZh26-bd7PXZZM.html Handle millions of rows in Excel - Slow to fast - kzread.info/dash/bejne/a2ecmdSLo6-sZ7w.html Convert crosstab to tabular - Unpivot - Excel Power Query - kzread.info/dash/bejne/n4iosdaTeJDUfrA.html Cheers. Doc

I have had this problem for over two days and this just solved it! I have a column of dates in a csv file and it's formatting differently for the 01/1-12 days and 1/13-up days. When I load it to power BI it just wont recognize the 01/13-up dates and returns an error. I tried formatting it from the CSV but somehow it is not taking custom format. I even did text to columns as well as extract function in power just to get it right but even then I am stuck with a dd/mm/yy. I still couldn't make it to a mm/dd/yy format. I just realized I had to change the regional setting in the control panel to resolve this. Anyhow, your method is simple but it solves this date conundrum that I've had easily. Thank you so much! I am so relieved to say the least! More power to your channel! Subscribed!

@efficiency365

6 ай бұрын

Thanks @ersofrescht4661 Glad you found this useful. Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - kzread.info/dash/bejne/pKh4zrGLfNDQnKg.html Excel Green Marks - Error Checking - Best Practices - kzread.info/dash/bejne/ipmA1s6zlJzSgrw.html Excel Best Practices - Part 1 of 3 - Data Management - kzread.info/dash/bejne/pKaTk8qaZsqcnMY.html Excel Best Practices - Part 2 of 3 - Formulas - kzread.info/dash/bejne/na6T3KOvfay0cc4.html Excel Best Practices - Part 3 of 3 - Analytics - kzread.info/dash/bejne/fIKGl9uxj7PNZKQ.html 10 Excel Settings You Must CHANGE! - kzread.info/dash/bejne/qIym1Lqde9ywds4.html Automatic data clean up with Excel Flash Fill - kzread.info/dash/bejne/gGekwdqYnLu6j8Y.html Instant Excel Audit, Comparison and Analysis - Inquire - kzread.info/dash/bejne/lXiY2LeTqLK7mdY.html Six powerful Excel Navigation Shortcuts - kzread.info/dash/bejne/lIZh26-bd7PXZZM.html Handle millions of rows in Excel - Slow to fast - kzread.info/dash/bejne/a2ecmdSLo6-sZ7w.html Convert crosstab to tabular - Unpivot - Excel Power Query - kzread.info/dash/bejne/n4iosdaTeJDUfrA.html Cheers. Doc

Thank you so much! This has been bothering me for so long, now I finally know the solution!

@efficiency365

10 ай бұрын

Thanks @r0260064 Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - kzread.info/dash/bejne/pKh4zrGLfNDQnKg.html Excel Green Marks - Error Checking - Best Practices - kzread.info/dash/bejne/ipmA1s6zlJzSgrw.html Excel Best Practices - Part 1 of 3 - Data Management - kzread.info/dash/bejne/pKaTk8qaZsqcnMY.html Excel Best Practices - Part 2 of 3 - Formulas - kzread.info/dash/bejne/na6T3KOvfay0cc4.html Excel Best Practices - Part 3 of 3 - Analytics - kzread.info/dash/bejne/fIKGl9uxj7PNZKQ.html 10 Excel Settings You Must CHANGE! - kzread.info/dash/bejne/qIym1Lqde9ywds4.html Automatic data clean up with Excel Flash Fill - kzread.info/dash/bejne/gGekwdqYnLu6j8Y.html Instant Excel Audit, Comparison and Analysis - Inquire - kzread.info/dash/bejne/lXiY2LeTqLK7mdY.html Six powerful Excel Navigation Shortcuts - kzread.info/dash/bejne/lIZh26-bd7PXZZM.html Handle millions of rows in Excel - Slow to fast - kzread.info/dash/bejne/a2ecmdSLo6-sZ7w.html Convert crosstab to tabular - Unpivot - Excel Power Query - kzread.info/dash/bejne/n4iosdaTeJDUfrA.html Cheers. Doc

Other videos just give alternatives to set a format but without defining the issue. this video Master them all. much appreciated for the demonstration. I have changed my pc date format then followed the PowerQuery method. it works perfectly.

@efficiency365

5 ай бұрын

Thanks @jubran5 Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - kzread.info/dash/bejne/pKh4zrGLfNDQnKg.html Excel Green Marks - Error Checking - Best Practices - kzread.info/dash/bejne/ipmA1s6zlJzSgrw.html Excel Best Practices - Part 1 of 3 - Data Management - kzread.info/dash/bejne/pKaTk8qaZsqcnMY.html Excel Best Practices - Part 2 of 3 - Formulas - kzread.info/dash/bejne/na6T3KOvfay0cc4.html Excel Best Practices - Part 3 of 3 - Analytics - kzread.info/dash/bejne/fIKGl9uxj7PNZKQ.html 10 Excel Settings You Must CHANGE! - kzread.info/dash/bejne/qIym1Lqde9ywds4.html Automatic data clean up with Excel Flash Fill - kzread.info/dash/bejne/gGekwdqYnLu6j8Y.html Instant Excel Audit, Comparison and Analysis - Inquire - kzread.info/dash/bejne/lXiY2LeTqLK7mdY.html Six powerful Excel Navigation Shortcuts - kzread.info/dash/bejne/lIZh26-bd7PXZZM.html Handle millions of rows in Excel - Slow to fast - kzread.info/dash/bejne/a2ecmdSLo6-sZ7w.html Convert crosstab to tabular - Unpivot - Excel Power Query - kzread.info/dash/bejne/n4iosdaTeJDUfrA.html Cheers. Doc

Sir, thanks for the video, i was pulling the report from one of the application, upon this video i have changed the regional settings in the application then downloaded the report, it calculates as date format... thank you very much, this really helped me

@efficiency365

Жыл бұрын

Thanks @hariharagupta5638. You cannot keep changing date format at system level for every import. That is why this method is better. Check out my other best practices videos as well. Efficient Tasks Management - kzread.info/dash/bejne/qIB60rmPlrjMgZs.html Microsoft Teams - 15 Best Practices - kzread.info/dash/bejne/iWyJyNqYqtGxqKw.html Smart and Effective Email - 5 powerful ways - kzread.info/dash/bejne/aIJ5qLWGoavNgag.html Outlook Calendar Best Practices : Part 1 - kzread.info/dash/bejne/ea6ns9Cek82ngdI.html and Part 2 - kzread.info/dash/bejne/opWYrapxqdabc9Y.html OneNote - Best Practices - kzread.info/dash/bejne/n2Foo7tqk7DPaNY.html Microsoft 365 Best Practices - kzread.info/dash/bejne/nYp3wbucfJi1krg.html Word Tables Best Practices - The ultimate guide - kzread.info/dash/bejne/p5l_paqxfri1m6Q.html Cheers. Doc.

Awesome content. Thanks!

@efficiency365

3 ай бұрын

Thanks @mikewallace6810 Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - kzread.info/dash/bejne/pKh4zrGLfNDQnKg.html Excel Green Marks - Error Checking - Best Practices - kzread.info/dash/bejne/ipmA1s6zlJzSgrw.html Excel Best Practices - Part 1 of 3 - Data Management - kzread.info/dash/bejne/pKaTk8qaZsqcnMY.html Excel Best Practices - Part 2 of 3 - Formulas - kzread.info/dash/bejne/na6T3KOvfay0cc4.html Excel Best Practices - Part 3 of 3 - Analytics - kzread.info/dash/bejne/fIKGl9uxj7PNZKQ.html 10 Excel Settings You Must CHANGE! - kzread.info/dash/bejne/qIym1Lqde9ywds4.html Automatic data clean up with Excel Flash Fill - kzread.info/dash/bejne/gGekwdqYnLu6j8Y.html Instant Excel Audit, Comparison and Analysis - Inquire - kzread.info/dash/bejne/lXiY2LeTqLK7mdY.html Six powerful Excel Navigation Shortcuts - kzread.info/dash/bejne/lIZh26-bd7PXZZM.html Handle millions of rows in Excel - Slow to fast - kzread.info/dash/bejne/a2ecmdSLo6-sZ7w.html Convert crosstab to tabular - Unpivot - Excel Power Query - kzread.info/dash/bejne/n4iosdaTeJDUfrA.html Cheers. Doc

This was very useful! thanks

@efficiency365

11 күн бұрын

Thanks @akshaynitinvaidya Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - kzread.info/dash/bejne/pKh4zrGLfNDQnKg.html Excel Green Marks - Error Checking - Best Practices - kzread.info/dash/bejne/ipmA1s6zlJzSgrw.html Excel Best Practices - Part 1 of 3 - Data Management - kzread.info/dash/bejne/pKaTk8qaZsqcnMY.html Excel Best Practices - Part 2 of 3 - Formulas - kzread.info/dash/bejne/na6T3KOvfay0cc4.html Excel Best Practices - Part 3 of 3 - Analytics - kzread.info/dash/bejne/fIKGl9uxj7PNZKQ.html 10 Excel Settings You Must CHANGE! - kzread.info/dash/bejne/qIym1Lqde9ywds4.html Automatic data clean up with Excel Flash Fill - kzread.info/dash/bejne/gGekwdqYnLu6j8Y.html Instant Excel Audit, Comparison and Analysis - Inquire - kzread.info/dash/bejne/lXiY2LeTqLK7mdY.html Six powerful Excel Navigation Shortcuts - kzread.info/dash/bejne/lIZh26-bd7PXZZM.html Handle millions of rows in Excel - Slow to fast - kzread.info/dash/bejne/a2ecmdSLo6-sZ7w.html Convert crosstab to tabular - Unpivot - Excel Power Query - kzread.info/dash/bejne/n4iosdaTeJDUfrA.html Cheers. Doc

thank you! it helped me a lot :)

@efficiency365

3 ай бұрын

Thanks @sreenivasrao9805 Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - kzread.info/dash/bejne/pKh4zrGLfNDQnKg.html Excel Green Marks - Error Checking - Best Practices - kzread.info/dash/bejne/ipmA1s6zlJzSgrw.html Excel Best Practices - Part 1 of 3 - Data Management - kzread.info/dash/bejne/pKaTk8qaZsqcnMY.html Excel Best Practices - Part 2 of 3 - Formulas - kzread.info/dash/bejne/na6T3KOvfay0cc4.html Excel Best Practices - Part 3 of 3 - Analytics - kzread.info/dash/bejne/fIKGl9uxj7PNZKQ.html 10 Excel Settings You Must CHANGE! - kzread.info/dash/bejne/qIym1Lqde9ywds4.html Automatic data clean up with Excel Flash Fill - kzread.info/dash/bejne/gGekwdqYnLu6j8Y.html Instant Excel Audit, Comparison and Analysis - Inquire - kzread.info/dash/bejne/lXiY2LeTqLK7mdY.html Six powerful Excel Navigation Shortcuts - kzread.info/dash/bejne/lIZh26-bd7PXZZM.html Handle millions of rows in Excel - Slow to fast - kzread.info/dash/bejne/a2ecmdSLo6-sZ7w.html Convert crosstab to tabular - Unpivot - Excel Power Query - kzread.info/dash/bejne/n4iosdaTeJDUfrA.html Cheers. Doc

Superb explanation.!

@efficiency365

Жыл бұрын

Thanks MANI MARAN JAMBAI. Check out my other best practices videos as well. Efficient Tasks Management - kzread.info/dash/bejne/qIB60rmPlrjMgZs.html Microsoft Teams - 15 Best Practices - kzread.info/dash/bejne/iWyJyNqYqtGxqKw.html Smart and Effective Email - 5 powerful ways - kzread.info/dash/bejne/aIJ5qLWGoavNgag.html Outlook Calendar Best Practices : Part 1 - kzread.info/dash/bejne/ea6ns9Cek82ngdI.html and Part 2 - kzread.info/dash/bejne/opWYrapxqdabc9Y.html OneNote - Best Practices - kzread.info/dash/bejne/n2Foo7tqk7DPaNY.html Microsoft 365 Best Practices - kzread.info/dash/bejne/nYp3wbucfJi1krg.html Word Tables Best Practices - The ultimate guide - kzread.info/dash/bejne/p5l_paqxfri1m6Q.html Cheers. Doc.

Super nice explained SIR

@efficiency365

Жыл бұрын

Thanks

Very usefull video sir...

@efficiency365

Жыл бұрын

Thanks for your feedback. Do explore my blog at efficiency365.com/blog Check out my My Udemy courses Excel to Power BI s.efficiency365.com/upbi Pivot Tables s.efficiency365.com/upt Learn 130 powerful efficiency best practices across Office 365 - read my latest book - s.efficiency365.com/bk Also let me know if you would like me to cover any topics relevant to you. Cheers. Dr Nitin (doc) Paranjape.

@TheAlwaysGoel

Жыл бұрын

@@efficiency365 sir kindly can you explain power pivot basic to pro concept... 👍

Thank u

@efficiency365

Жыл бұрын

Thanks @ianku5208. Check out my other best practices videos as well. Efficient Tasks Management - kzread.info/dash/bejne/qIB60rmPlrjMgZs.html Microsoft Teams - 15 Best Practices - kzread.info/dash/bejne/iWyJyNqYqtGxqKw.html Smart and Effective Email - 5 powerful ways - kzread.info/dash/bejne/aIJ5qLWGoavNgag.html Outlook Calendar Best Practices : Part 1 - kzread.info/dash/bejne/ea6ns9Cek82ngdI.html and Part 2 - kzread.info/dash/bejne/opWYrapxqdabc9Y.html OneNote - Best Practices - kzread.info/dash/bejne/n2Foo7tqk7DPaNY.html Microsoft 365 Best Practices - kzread.info/dash/bejne/nYp3wbucfJi1krg.html Word Tables Best Practices - The ultimate guide - kzread.info/dash/bejne/p5l_paqxfri1m6Q.html Cheers. Doc.

Dr. Nitin, I have an issue where I am exporting very old files from the year 2000 from a paid data repository in excel csv format. The date format in the repository shows as (MM-DD-YY). When I open the csv file, the date format is not entirely intact. Some of the dates are preserved in MM-DD-YY, but most are changed to YYYY-MM-DD. But does this in the worst way, by adding (20) before the month and turning it to a YYYY. For example, a record dated 12-10-01 (Dec, 10, 2001) becomes 2012-10-01 (2012, Oct, 01). The entire year, month and day is wrong. It is really wierd. Then, when I convert the .csi file to Txt, as in your tutorial, the date will appear exactly as it does in the csv file, e.g., in both formats. Its really messed-up. Have you seen anything like this?

@efficiency365

9 ай бұрын

This is a year 2000 (Y2K) problem. Read this article - you will find the solution. learn.microsoft.com/en-us/office/troubleshoot/excel/two-digit-year-numbers

What if it's not a csv but an excel file itself that has dates in different format than my locale. Then when I open it on my Indian locale computer, will it read and store dates correctly? I want it to retain the original format mm/dd/yy and not change it to dd/mm/yy.

@efficiency365

11 ай бұрын

Always use Power Query for import. Then you can use From Locale option on a per column basis.

Sir, I have a csv file which I want to update (add more data). Now, if I follow this method, update the data and then convert it back to a csv using the save as--> csv option, will it work? or do I need to change the formatting again?

@efficiency365

23 күн бұрын

It will work. But after saving the target application needs to recheck data types.

Hi Dr Nitin, Appreciate your content. Would be great help if you could include a way to convert datetime function as 'yyyy/mm/dd hh:mm:ss' . My only solution thus far is to save as a .CSV file as files will reformat datetime into locale format. Thank you

@efficiency365

Жыл бұрын

Dear Chris What is the requirement? Convert from which format to which format? In Power Query, we can use locale based functions, or many other approaches. Please post some input and intended output examples and then I will create the relevant content. Cheers. Doc.

Sir , I am facing issue when dowloading excel csv.CSV changes to wrong dates upon save. for ex : when I enter 12-Oct-1945 , saving in 12-oct-45 format. when I tried to read that cell value it is showing as 12-oct-2045.could you please help with solution

@efficiency365

Жыл бұрын

By default, Excel takes the cutoff year as 29. Below that it will be 20xx after that it will be 19xx. To change the cutoff date, to go control panel - regional settings - Date - cutoff date.

@shilpaanthati8324

Жыл бұрын

​@@efficiency365 Thank you for your response . But sir , is there any way to change the cut off date using angular typescript . End users who is using application not aware of system settings . If there is any solution that is actually a saviour .

@efficiency365

Жыл бұрын

Use Power Query to import data. There you can put your own logic easily.

Sir just wanted to know in CSV there is new line automatically coming when i clone the csv from git how to avoid automatic new line for eg Failed Data Validation FileName cannot be Empty this data will turn to Failed Data Validation FileName cannot be Empty

@efficiency365

Жыл бұрын

Here is how : docs.github.com/en/get-started/getting-started-with-git/configuring-git-to-handle-line-endings

I already tried both methods but didnt work. How to resolve this problem?

@efficiency365

11 ай бұрын

What is the exact problem?

Appreciate the directions. Was one issue that was causing me nightmares in my data analysis. Is there a way to set the Locale permanently.

@efficiency365

Ай бұрын

Thanks @balajirajagopal299 The default is based upon Windows Regional Setting. Check out my popular Excel videos How to enter and edit Excel Formulas - Back to Basics - kzread.info/dash/bejne/pKh4zrGLfNDQnKg.html Excel Green Marks - Error Checking - Best Practices - kzread.info/dash/bejne/ipmA1s6zlJzSgrw.html Excel Best Practices - Part 1 of 3 - Data Management - kzread.info/dash/bejne/pKaTk8qaZsqcnMY.html Excel Best Practices - Part 2 of 3 - Formulas - kzread.info/dash/bejne/na6T3KOvfay0cc4.html Excel Best Practices - Part 3 of 3 - Analytics - kzread.info/dash/bejne/fIKGl9uxj7PNZKQ.html 10 Excel Settings You Must CHANGE! - kzread.info/dash/bejne/qIym1Lqde9ywds4.html Automatic data clean up with Excel Flash Fill - kzread.info/dash/bejne/gGekwdqYnLu6j8Y.html Instant Excel Audit, Comparison and Analysis - Inquire - kzread.info/dash/bejne/lXiY2LeTqLK7mdY.html Six powerful Excel Navigation Shortcuts - kzread.info/dash/bejne/lIZh26-bd7PXZZM.html Handle millions of rows in Excel - Slow to fast - kzread.info/dash/bejne/a2ecmdSLo6-sZ7w.html Convert crosstab to tabular - Unpivot - Excel Power Query - kzread.info/dash/bejne/n4iosdaTeJDUfrA.html Cheers. Doc