100 (or more) Computer Tips in 100 (or more) Days
I'm sure you're aware that a date is made up of a day, month and year. Using Excel's date functions you can parse (break apart) the information into these individual segments and more.Once they're parsed they can then be used for pivot tables or other analysis.
The amazing programmers at Microsoft have given us more than 350 functions to make our life easier. Twenty-two of these functions are in the Date & Time group. Using these functions we can parse the three date segments (day, month, year) into individual columns so that you can use the elements individually. If you needed to create a birthday list, having the birth month as a separate piece of data, you would be able to easily identify all of the January birthdays.
MONTH, DAY and YEAR Functions
These date functions are among the easiest of Excel's functions to use because they contain one argument which is the cell address of the date in your worksheet. You might need to start by inserting some columns into your worksheet to parse the information. Start in the cell to the right of your date field and from the Formulas tab, Function Library group click on the Date & Time option. Scroll on down to the MONTH, DAY or YEAR function and click. In the Function Builder dialog box click on the first date in your list and press the Enter/Return key. You will probably see something like the result I got, which looks a little crazy.
The problem with the result is the format. When you insert a new column in an Excel worksheet, the format of that column is the same as the column to the left. The results that were returned in the cells in J, K and L column are correct but the formatting is bad. If you know that, according to Microsoft, the world began on January 1, 1900, day three of the world would be 1/3/1900. Since the month of the date in I column is 3 than the result is correct. The same is true of the result in K column. The day of the date in I column is 6 or expressed in a date format it would be 1/6/1900. All I need to do is change the format on these cells and I'm good.
To change the format you need to select columns J through L. After they are selected go to the Home tab and in the Number group choose a General format. Now the results are displayed properly. All you have to do is select these three cells by dragging across them. Release your mouse button and double click the little fill handle in the lower right side of the selected cells. Presto! The cells fill down and you're ready to sort by any of these fields.
WEEKNUM Function
When doing an analysis of the information you have, it might be helpful to be able to group the information by week. If that is the case then you need to know what week number, from 1 to 52, the date represents. The WEEKNUM function will return that based on the date in your worksheet. Like the other date functions we're reviewed, the WEEKNUM function uses the one argument. Again you might want to insert a new column for this information. After you have done that, click in the first cell and insert the WEEKNUM function from the Formulas tab, Function Library, Date & Time group. Format the results if necessary and fill the formula down using the fill handle. Now you can analyze the information by weeks of the year.
You'll notice when you are in the Function Builder dialog box that there is an Return_Type field. This field is optional and is used to determine how the first week of the year is calculated. Leaving it blank assumes you are using System 1 as described below. Type a 2 in this field if you want to use System 2.
- System 1 The week containing January 1 is the first week of the year, and is numbered week 1.
- System 2 The week containing the first Thursday of the year is the first week of the year, and is numbered as week 1. This system is the methodology specified in ISO 8601, which is commonly known as the European week numbering system.
Suppose you wanted to see the production that occurred on a particular day of the week so that you could see productivity trends. You would need to know what day of the week a particular date represented. For that you would use the WEEKDAY function to parse that information out. Insert a new column for the weekday information. After you have done that, click in the first cell and insert the WEEKDAY function from the Formulas tab, Function Library, Date & Time group. Format the results if necessary and fill the formula down using the fill handle.By default Sunday is represented as 1, Monday as 2 and so forth. You can use the Return_Type option in the Function Builder dialog box to change the numbering sequence. Visit the Microsoft website if you need more information on changing the sequence for returning the weekday number.
I hope you can use these functions to further analyze information you have that includes dates.
My second book, Excel's Amazing Pivot Tables, should be out in a few months. Click the Subscribe button on the right so I can let you know when it's available for purchase.
Happy Computing!
Diane
Don't forget to click the Subscribe to 100 Computer Tips in 100 Days via email link on the right side of the screen so you get email alerts when Diane adds a new post.
Want more tech news or interesting links? You'll get plenty of both if you keep up with Diane McKeever, the writer of 100 Computer Tips in 100 Days, by subscribing to her Facebook posts or visiting her web page, www.dianemckeever.com .
No comments:
Post a Comment