File Date… and little Bites that’ll fill you up

Do you have any reports or spreadsheets that are dated?  I mean, do you have a report that you have to submit every Monday?  Or a monthly Bank Reconciliation?  Well, what if instead of having to change the date each time you save the file (or get embarrassed because you forgot), the date was auto-populated?  Good News Part One: It’s not that hard to do; here’s the formula:

=DATEVALUE(CONCATENATE(MID(MID(CELL(“filename”,A2),FIND(“AddingDate_”,CELL(“filename”,A2))
+LEN(“AddingDate_”),6),3,2),”/”,RIGHT(MID(CELL(“filename”,A2),FIND(“AddingDate_”,CELL(“filename”,A2))
+LEN(“AddingDate_”),6),2),”/”,LEFT(MID(CELL(“filename”,A2),FIND(“AddingDate_”,CELL(“filename”,A2))
+LEN(“AddingDate_”),6),2)))

Okay, here’s the second piece of good news.  That formula is not nearly as hard as it looks.  People ask how I write such long formulas.  The answer is that I dissect the formula, breaking it up into small pieces, taking small bites at a time.  Then I go back and put it all back together.

Check out the attached file: AddingDate_110111

It takes several steps (outlined in the file) to get to the ultimate formula, but here’s the gist of it:

1. Use a common naming convention as a prefix for the file name, with a uniform date format.  Examples might include BankRecon_101231 or WeeklySalesReport_101217 where “BankRecon_” and “WeeklySalesReport_” would be common for all dates.  I like to use a six digit date format with yymmdd.  The reason I put the year first is because it enables me to sort more usefully.  Using month (where Jan 2010 and Jan 2011 would be next to one another) or day as the starting digits can make sorting and finding items more difficult.
2. The formula to obtain the filename of the current file is: Cell(“filename”,A1)
3. Find out the position of the common text, in this case “AddingDate_”; this allows the formula to work no matter where the file is located.  The formula works whether the file is on the desktop (C:\Users\Bobby\Desktop\[AddingDate_110111.xls]Sheet1) or in some special folder (C:\MonthlyReports\2011\[AddingDate_110111.xls]Sheet1)
4. Determine how long the common text is
5. Use the Starting position and length of Common Text to determine where the six-digit date format starts
6. Using the mid() formula with the search text (filename), Starting Position (from step #5 above), and number of digits (six for the 6-digit date format) as parameters, extract the date
7. Strip out the month, day, and year separately
8. Using concatenate(), create date text in “mm/dd/yy” format
9. Using datevalue(), convert text to serial/date
10. Work backwards, substituting abbreviated cell references with underlying formulas until the base level of information is reached. For example, the final formula in cell C13 is datevalue(C12).  We replace “C12″ with the formula that is actually in cell C12.  So the new formula would be datevalue(CONCATENATE(C10,”/”,C11,”/”,C9)).  We then replace “C10”, “C11”, and “C9” with the formula that exists in those cells.  We continue this until we can no longer work backwards.

Once you’ve gotten better at formulas, you’ll be able to combine some of these steps or write them without as many steps.  But this is a great way to learn along the way.  It’s a great way to eat small portions so you don’t get too sick.

1. Format cell in any date format you reach, even adding text (via custom formatting) if you wish