Business Solutions: Excel: Use As a Query Tool.

     by James D. Duncan, CPA          July 2004

Excel-Dates and Concatenation

By James D. Duncan, CPA

In accounting we are always using the dates to describe some entry.  You can put a date in a single place in the sheet and use all of its components in various ways.  For example, a date has month, day and year.  Each of the items is usable in called functions.

Enter a date in A1 and let us see how these functions work.

Now in A3 enter =Month(A1),  A4 enter =Day(A1), and in A5 enter =Year(A1).  The result is the date components are now separated in each cell.

This shows us that we can manipulate the date components.  For example, we number our journal entries each month.  In A7 enter =”JE “ & Month(A1) & “-01” and in A8 enter =”JE “ & Month(A1) & “-02”.  This would be the incrementing numbers for our journal entries for that month.

In A10 enter  ="Record Sales For " & MONTH(A1) & "/" & DAY(A1) & "/" & YEAR(A1).  This gives us a description in addition to the month, day and year.  The ampersand (&) symbol is what gives us the concatenation of the fields.  This joins the fields together in one string for us to use.

            For more on these and other functions, go to Excel help, click on contents, click on function reference and select date and time functions.  The ampersand (&) explanation is found under creating and correcting formulas, creating formulas and then about calculation operators.  Wheeeewwww, need to know the answer before you can find the place to ask the question.  I am afraid our profession is like that.  You know you can do something, just need to find where to ask to find out.

James is a member of the OKCPCUG and an expert in Access and Excel. Click here to reach James.