Excel Magic Trick 1196


YYMMDD Text Dates To Serial Number Dates? 2 More Formulas & Other Tips…

DESCRIPTION

See how take two columns of county numbers and kip codes and show unique list of county codes vertically, and then for each county code, list zips codes horizontally. We saw how to do this in EMT 1195, but here are two other possibilities:
(00:08) Problem set up and look back at EMT 1195
(00:46) XLarium formula: LEFT, RIGHT, YEAR, TODAY and TEXT function
(03:06) Investigate the Custom Number Format: “0000-00-00”, “00-00-00”, and “0-00-00”
(04:42) David Milbrandt formula: DATEVALUE, MID, RIGHT and LEFT Functions.
(06:32) Check whether three cells are equal. Compare three cells. Cell Content the same in all three cells?
(07:45) Keyboards to copy formula down a column
(08:05) Check whether there are any FALSE values using COUNTIFS function
(08:35) Look at how DATEVALUE interprets dates and whether or not the two digit year should be in 2000 or 1900? 0 – 29 = 2000 and 30 – 99 = 1900.
Refers to video: Excel Magic Trick 1195: Are YYMMDD Text Dates 1900 or 2000? Formula or Text To Columns Solution?

WORKBOOKS




Be the first to comment

Leave a Reply