Excel Magic Trick 1080: Age From Alphanumeric Text String, Has Incomplete Date Year = 20?? or 19??

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

See how to calculate age from a text string that contains both letters and numbers and which has incomplete digits to indicate whether the year is from 19?? Or 20?? Formula comes from Bill Szysz at YouTube. Formula contains the functions: MID, TEXT, IF, ISNUMBER, TODAY, DATEDIF and Custom Number Formatting.

Formula from Bill Szysz!!! :)))))
  1. excelisfun
  2. Excel 2013
  3. Excel 2003
  4. Excel 2007
  5. Excel 2010
  6. excelisfun
Рекомендации по теме
Excel Magic Trick 0:07:18

Excel Magic Trick 1080: Age From Alphanumeric Text String, Has Incomplete Date Year = 20?? or 19??

Excel Magic Trick 0:04:01

Excel Magic Trick 1079 IF Function 4 Letters To Start TextString = Underage, 6 Letters = 18 or Older

Excel Magic Trick 0:03:20

Excel Magic Trick 1084: ISODD or ISEVEN Functions to Help Add Every Other Column

Excel Magic Trick 0:05:05

Excel Magic Trick 1088: Show All B7 values From Across Sheets with Names 01, 02, 03, ...10, 11...

Excel Magic Trick 0:03:47

Excel Magic Trick 1074: Multiply Quantity Times Debit or Credit: 3 Formulas Examples.

Excel Magic Trick 0:01:32

Excel Magic Trick 1061: Copy Formulas Quickly With Name Box and Shift Key (Horizontal or Vertical)

Excel Magic Trick 0:03:39

Excel Magic Trick 1094: Wildcards for Sheet References and INDIRECT function

Excel Magic Trick 0:07:57

Excel Magic Trick 1082: SUM Every Other Column: Four Formula Methods (Add Every Other Column)

Excel Magic Trick 0:03:36

Excel Magic Trick 1076: Convert Date-Time Values to Serial Numbers w TEXT & Custom Number Format

Excel Magic Trick 0:10:35

Excel Magic Trick 1072: Conditionally Format Partial Row Based On Start Month, End Month & Produ...

Excel Magic Trick 0:12:45

Excel Magic Trick 1056 Excel 2013 Slicer Formulas (nowtelugu.com)

Excel Magic Trick 0:07:23

Excel Magic Trick 1012: Automatically Add All Subtotals In A Column (6 Methods)

Excel Magic Trick 0:06:16

Excel Magic Trick 1026: Conditionally Format 1st Occurrence of Yes Vote For Given First & Last N...

Excel Magic Trick 0:06:51

Excel Magic Trick 1065 SUMIFS Month Over Month Sales Calculations From Transactional Data Set

Excel Magic Trick 0:10:08

Excel Magic Trick 1031: Four Way Lookup Formula To Deliver Multiple Items From 1 of 2 Lookup Tables

Excel Magic Trick 0:02:49

Excel Magic Trick 1184: Formula to Create Alpha Numeric Series X20, X25, X39…

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:50

Excel Magic Trick 1078: Total Cost Based On Per Minute Charge & Excel Time Values Nearest 15 Sec...

Excel Magic Trick 0:04:28

Excel Magic Trick 1030 Round To The Nearest $50 For Pricing or To The Nearest Second For Times

Excel Magic Trick 0:09:07

Excel Magic Trick 1091: Count Number of Items In Row For First Occurrence of Each Group

Excel Magic Trick 0:08:21

Excel Magic Trick 1125: Data Validation Custom Formula: Workday Dates in Current Month

excel-358 - Przypisywanie 0:04:22

excel-358 - Przypisywanie (określanie) waluty na podstawie formatu - sposób Billa

EAF #23 - 0:09:49

EAF #23 - Find the Max value in a dataset using criteria - MAX IF Array formula

Excel Formula_Datedif_SS_know Age 0:04:23

Excel Formula_Datedif_SS_know Age

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

Excel Magic Trick 1080: Age From Alphanumeric Text String, Has Incomplete Date Year = 20?? or 19?? 

excelisfun
Автор

Mike thanks for the great video and explanation, Bill the way you solved the puzzle was awesome. As I mentioned before you guys rock!!!  

DavidGarcia-phrs
Автор

By the way, Mike I've just bought your book "Ctrl+Shift+Enter" and is excelent and well explained thank you for sharing your great Excel skills with us and show us that there are many ways to solve dificult tasks having fun with excel.  

DavidGarcia-phrs
Автор

Thank you Mike :)))
Your explanation is great as always.
I don't have such skills like you. :)))

BillSzysz
Автор

Hi mister Girvin:
Thank you for all these videos, I learnt a lot of them, not just how formulas work, but also the smart way you use them. My question is: why did you put "<20"? I think "<14" wich means the two right digits of "2014", works better. In that way, someone who is born in 1915, shows in the result of the formula 1915 and not 2015, because we are not yet in that year.
Correct me if I am wrong, and thank you again.

kikoskiki
Автор

Do you mind sharing what software you are using to create the tutorials? I have a pretty complicated spreadsheet that I think would be easier to do a video series on to make sure the next person knows how to use it if I get hit by a bus.

mdjrboyvb