Excel Magic Trick 1255: Formula to Convert Text Dates With Missing Year & Extra Spaces To Real Dates

preview_player
Показать описание

Learn how to Convert Text Dates With Missing Year & Extra Spaces To Real Dates (Serial Number Dates) with a formula that uses the TRIM, IF, ISNUMBER and SEARCH functions.
  1. excelisfun
  2. Excel
  3. Microsoft Excel
  4. Highline College
  5. Mike Girvin
  6. excelisfun
Рекомендации по теме
Excel Magic Trick 0:05:10

Excel Magic Trick 1255: Formula to Convert Text Dates With Missing Year & Extra Spaces To Real D...

Excel Magic Trick 0:04:24

Excel Magic Trick 1251: SUMIFS & Table Formula Nomenclature (Structured References) to Add Invoi...

Excel Magic Trick 0:05:15

Excel Magic Trick 1256: Grouped Dates in PivotTable With Becomes UnGrouped When I Refresh!!!

#tab name in 0:00:33

#tab name in any cell =MID(CELL('filename',A1),FIND(']',CELL('filename&ap...

Excel Magic Trick 0:01:59

Excel Magic Trick 1261: Flash Fill to Add Spaces to Credit Card Numbers

Excel Magic Trick 0:08:55

Excel Magic Trick 1254: Lookup Item Where 2 Values Are Closest: Helper Column or Array Formula?

Excel Magic Trick 0:11:21

Excel Magic Trick 1263: Power Query M Code: 10 Cross Tabulated Transformed into 1 Proper Data Set

Excel Magic Trick 0:05:33

Excel Magic Trick 1093: Extract Date from Middle of Description: Text To Columns or Formula?

Excel Magic Trick 0:05:46

Excel Magic Trick 1246: Counting with Case Sensitive Criteria: EXACT & SUMPRODUCT

Excel Magic Trick 0:04:08

Excel Magic Trick 1247: Adding with Case Sensitive Criteria: EXACT & SUMPRODUCT

Excel Magic Trick 0:05:16

Excel Magic Trick 1248: Add or Count Case Sensitive Contains Criteria: ISNUMBER, FIND, SUMPRODUCT

Excel Magic Trick 0:02:19

Excel Magic Trick 1258: Add Spaces Between Digits: Custom Number Format & TEXT Function

Excel Magic Trick 0:04:09

Excel Magic Trick 1264: Gross Pay From Start & End Date, Weekends Sun & Mon and Weekly Pay S...

Excel Magic Trick 0:01:58

Excel Magic Trick 1260 Re-Do: Prevent VLOOKUP from Returning Zero from Empty Cell in Lookup Table.

Convert text-based dates 0:00:59

Convert text-based dates into proper dates in Excel

Excel Magic Trick 0:07:10

Excel Magic Trick 1294: Single Cell 'Lookup Adding' w Improper Data Set to Score Myers-Bri...

Excel Magic Trick 0:08:56

Excel Magic Trick 1260: VLOOUP Returns Zero when Lookup Table Contains Empty Cell? 3 Fixes.

Excel Magic Trick 0:08:02

Excel Magic Trick 1259: Can VLOOKUP Lookup Duplicates? Yes! Helper Column or Array Formula?

Excel Magic Trick 0:11:32

Excel Magic Trick 1257: Power Query to Create One Proper Table from 10 Cross Tabulated Tables

Excel Convert Text 0:00:55

Excel Convert Text Date using formula

Merry Christmas Tree 0:01:54

Merry Christmas Tree Fun In Excel RAND Function and Conditional Formatting (Excel Magic Trick 1245)

Indian vs Japanese 0:00:13

Indian vs Japanese Maths 🔥| Vedic Maths Trick for Fast Calculation | Speed Maths #trending #shorts...

Trick_147👍Get Current Activated 0:00:54

Trick_147👍Get Current Activated Sheet Name By Using Single Function |👍#Excel #Expert

Dashboard Automation | 0:03:43

Dashboard Automation | Extract Unique List Using Formula in Excel

Комментарии
Автор

Cleaning data is fun! Thanks for your solution!

pmsocho
Автор

Mike!!! Looks like you have always been awesome...Thanks for the video.

anthonyverdin
Автор

These messy date files are so common. Thanks for sharing your solution Mike.

MySpreadsheetLab
Автор

You're a wizard. I enjoy your vids. Thank you!!

sdpadresfan
Автор

Great tip. Do you have any recent books that you have published?

SamerDebs
Автор

Hi Uncle Michael, where are you?? How are you.

levelzeroD
Автор

Hi there! In my file there are mistakes (#VALUE!) for the cells which have american date formats. How can we fix it?

oksananikolayeva
Автор

Sir, Getting #value error in few of the cells once the date is converted to number format after trim function

yadunandansathyanarayanan
Автор

Thanks, really useful video and will help me and lot's of other people no doubt.

I have recently come across a similar situation that I would love to see a video on how best to solve the issue.

The dates in the raw data come out in multiple formats and I cannot see a quick way in solving the issue (without find/replace etc).
The formats they come out in are General and custom, and some show as 'Dec 9, 2015', '12/14/2015 08:04:41 AM' & for some reason =' "05/29/2015 07:14:05 AM" '.

When you try and covert only some will change to a short date and the biggest issue is the ones with the =" that include the time as I need to delete the =" & ".

Any suggestions on here would be really appreciated but also a video on it would be great as that could help others in future.

Again I would like to thank you for making these video's, they are so useful to a lot of people and really made well so even armatures like me can understand.

FukSN