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
C:\Users\Bobby\Desktop\Blog\[AddingDate_110109.xlsx]Sheet1
Advertisements

About bbluford
I am an executive finance professional with a love for process and application development (MS Access, Excel, Quickbooks), mostly as it relates to Accounting and Business Functions. I also love to write and share ideas with other people in this world. I'm an admitted Gym Rat who works out excessively. The best summation of me is that I love to teach and to learn.

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

  1. Pingback: Dirty Data Again « BobbyBluford.com

  2. Pingback: YOY Comparison for Most Recent Days « BobbyBluford.com

  3. Pingback: What quarter is it? « BobbyBluford.com

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: