Best Microsoft Excel Bloggers

Thursday, February 4, 2010

Calculating Age or Years of service


When you are calculating age or years of service, you can't just subtract the date from the current date. You will not get the correct answer if you do that. Instead, you need to use =yearfrac() which will give you a fraction based on the number of days between the Start Date and the End Date.

The syntax is =yearfrac(today(),A1,1))
My brother's birthday is next week so let's see how old he actually is:
38.97 sounds better than 39 doesn't it?  Now, if you wanted it as a whole number you could simply use the INT() function with it.
=INT((YEARFRAC(TODAY(),A1,1)))
Now- be careful - if you use the INT, his age would display as 38. After all, he is not 39 yet. Don't rush him. Just wait until next year. Egad. my baby brother is getting old.
 ========================================================
For those of you who remember Lotus, you can also use DATEDIF(). This function provides you with a little more flexibility.
DATEDIF() is not documented in Excel and has to be typed in manually.
Rumor has it that it was a very popular Lotus function and back when people were transitioning there was a huge outcry over its loss and Microsoft added it in - grudgingly..ergo.. no documentation.

DATEDIF() allows you to display the date in terms of years, months or days.
The syntax is DATEDIF(start date, end date, time interval)                                                                         
Time intervals include:
"Y" The number of complete years
"M" The number of complete months
"D" The number of days.
"MD" The difference between the days in start_date and end_date.
          The months & years of the dates are ignored.
"YM" The difference between the months in start_date and end_date.
          The days & years of the dates are ignored.
"YD" The difference between the days of start_date and end_date.
          The years of the dates are ignored.





If you are really into it, you can join these functions all together using & and quotes
and get something like this:
=DATEDIF(A1,NOW(),"Y")&" years" &" "&DATEDIF(A1,NOW(),"ym") &" months "
and the resulting answer would display as 38 years 11 months

Remember, the & joins items together and you need quotes around text. I left a space before and after the word years and months so that everything would not run together. If you don't press your space bar to get the spaces, your answer will look like this: 38years11months instead of 38 years 11 months.

Tuesday, February 2, 2010

WEEKNUM()

One Date Function that some of you may find useful is =WEEKNUM()
This function returns the week number in the year.
If you want to find out what the current week number is you use the =TODAY() or =NOW() with WEEKNUM.

So, in the example below, =WEEKNUM(TODAY())  returned the value of 6 because today is February 2 2010.

Warning: Microsoft points out that the WEEKNUM function is based on the American standard where January 1 is considered the first week of the year even though this year the first week of 2010 only had two days in it. The European standard differs as the first week of the new year has to have at least 4 days in it so this function should not be used if you are presenting or using the data where European standards are used.

Monday, February 1, 2010

I could have sworn I had blogged about Date Functions awhile ago but apparently not.
Let me start with some basics.
A lot of people struggle with Date Functions and I think a lot of the problem arises because of formatting problems as as the fact that many of the answers are volatile.

If you don't know about =TODAY() or =NOW(), you should check them out. I use them constantly.
=TODAY() returns today's date. It is a volatile function. By volatile, I mean that it changes.
Coming from Lotus - oh so long ago- I hated these functions. I think I spent 2 hours once trying to figure out what I was doing wrong - only to realize that yes you really do need the empty parentheses to get the function to work!

If I type =TODAY() in an Excel spreadsheet today, which is February 1, it will display February 1. But, if I save the spreadsheet and open it February 2, it will display February 2 without me having to do anything. Open it on March 15th and it will display March 15th.

=NOW()
 works the same way, only it displays the current date and the current time.

People tend to be a little wary of these because sometimes the display looks hinky..(is that word?)
=TODAY() only displays the date so if you format it as date and time, it always displays 12:00 which of course looks strange.

Sometimes, if you are using these functions in a mathematical calculation, Excel tries to help you out and just confuses you. For example, if I type =TODAY() + A1 and A1 contains the value 10, I expect the answer to be February 11, 2010 since 10 days from today, February 1, would be February 11th. However, sometimes Excel surprises you and gives you an answer like the one below:
Generally the person who typed the formula, assumes they did something wrong. Well, you didn't. Excel sees that you are adding and so instead of displaying it in a date format - it displays it in a number format. All you have to do is reformat it as a number and it will display properly as 2/11/10. The good news is that Excel 2007 seems to have taken care of this problem but sometimes when you expect a number Excel displays it as a date or vice versa. So, before you decide it is user error on your part, re-format the cell. 

Wednesday, January 27, 2010

Custom Formats

MORE CUSTOM FORMATS
 


Wow... I am on a roll here........




Another useful format that some people just love is #,####,

This format takes a longer number and essentially truncates it for you so that you do not see the thousands. Excel still reads it as the longer number but you don't see it- even if I do calculations unless you re-format it.

Column A shows the actual number but when copied that number into Column B and then applied the custom format #,####, it redisplayed the shortened number. So, if you don't like dealing with a lot of extraneous zeroes, take a look at this format. Although you can change the display units in a chart itself, this is another way to show simpler units on a chart you are presenting.

  • Right-click on the cell(s)
  • Select Format Cells
  • Under Category, select Custom Formats
  • Enter #,####, in the Type
  • Click OK



The # tells Excel it is a number - don't forget that last comma... that's important as that is telling Excel that there are additional numbers after that but not to display them.

Monday, January 25, 2010

Keeping Leading Zeroes

LEADING ZEROES


Excel drops leading zeroes which can be a problem sometimes when you want to sort invocie numbers, account numbers or zip codes. If you want Excel to keep the leading zeroes then you need to create a custom format.
  • Select the range you want to format

  • Right-click and select Format Cells

  • Select the Number tab

  • Under Category, select Custom

  • Enter the number of zeroes that your longest entry will have
    (In my example, I said 5)

  • Click OK.

If you are just working with zipcodes, an little bit easier method is to select Special under Category and then select Zipcode. (Social security and telephone formats are also there.)


Ms. Excel- Resident Excel Geek