Convert number of month to name of month in Excel

Lets assume Date in A1 cell then use the follwing formulas

• =TEXT(A1,”MMMMM”)
  for the first letter of the month
• =TEXT(A1,”MMMM”)
  for the month spelled out completely
• =TEXT(A1,”MMM”)
  for the 3 letter abbreviation of month

Continue Reading...

How to Copy and Paste values without Formulas in Excel?

In Microsoft Excel, when you perform a normal copy and paste on a cell that has been calculated by a formula, the formula gets pasted as well. Sometimes, when you copy the contents of a cell, you want to paste just the value and not the underlying formula that is displayed in the formula bar like copy the resulting value of a formula to a cell on another worksheet. You can get an invalid cell reference error (#REF!) error.

You can avoid this error by pasting the resulting values of formulas without the formula in destination cells.

1. Select the cells or ranges you wish to copy.
2. On the Home tab, under Clipboard, select Copy.
3. Select the cell you wish to paste your values to.
4. Click the lower half of the large Paste button; from the extended menu that appears, select Values. Then click OK.

Or use a Shortcut Menu

1. An even quicker way to paste values is by using a mouse shortcut:
2. Point to the border of the selected range, and the pointer should change to a four-headed arrow.
3. Press the right mouse button, and drag the cells slightly to the right.
4. Keep pressing the right mouse button, and drag the cells back to their original location.
5. Release the right mouse button and a shortcut menu will appear.
6. Click on Copy Here as Values Only.
7. The formulas are replaced by the values of their results.

Continue Reading...

How to calculate person's age in Excel

There are many ways to calculate a person's age in Excel.

To calculate the age of a person on date since their birthdate:

=INT((date-birthdate)/365.25)

To figure their age as of today, date can be replaced with a function like TODAY() or NOW(). The INT() function is used to return only the number of complete years.

Let's say suppose Cell A1 contains "13-11-1984", then you can find person's age by enterring the following formula as

=INT((A1-birthdate)/365.25)

To calculate age in terms of years, months, and days, DATEDIF is a handy function. The following formula is similar to the one above.

=DATEDIF(A1,TODAY(),"Y") & " years, " & DATEDIF(A1,TODAY(),"YM") & " months, " & DATEDIF(A1,TODAY(),"MD") & " days"

Continue Reading...

Convert the case of text (Upper, Lower or Proper) in Excel

If you want to convert text from uppercase to lowercase or from lowercase to proper case to make it more readable. To change the case of text, you can use the UPPER, LOWER, or PROPER functions as shown below:

1. Let say cell A1 contains "suneel gudipati"
2. To convert the text into upper case enter the formula =UPPER(A1)
3. It changes text to all Upper case as "SUNEEL GUDIPATI"
4. Similarly to convert into lower case enter the formula =LOWER(A1)
5. It changes text to all lowercase as "suneel gudipati"
6. Similarly to convert into Title case enter the formula =PROPER(A1)
7. It changes text to all lowercase as "Suneel Gudipati"

Continue Reading...