Changing Date Formats in Excel

No matter how you display it, a date is a date.

Different Date Formats
1/15/2015
Thursday, January 15, 2015
1/15
01/15/15
January 15, 2015
15-Jan-2015
J-15

So why would you want to change the look of a date and how would you go about doing it?

Well Excel is a versatile tool and allows you to customize your output any almost any way you can imagine.

Perhaps the cell has the date of January 15 displayed as 1/15. For ease of reading perhaps you want it to be January 15.

Changing the date format is easy. First start by selecting the cells with the dates that you want to format.

Press CTRL + 1 to bring up the Format Cells dialog window. Additionally you can access this dialog window from the ribbon (Home Tab > Number).



















In the Category list select Date.

Select the Type of format you want. 

Right now you are saying to yourself... "Hold on, I know that! Why doesn't he tell me something I don't know???"

Well let me put on my mind reading hat and continue....

Lets start with the Type options. Do you see the first two with an * to the left?  Well when you see a date format that has an asterisk then that is a special date format that will change if you change the computers regional and time settings from the control panel. That feature could be handy when you have to send your worksheet the overseas office. 

What about the Location feature of the Format Cells dialog window. Have you ever changed that to another region? If you do, you will notice that the Types available for selection also change to reflect the standard date nomenclature of that region.

Ahhhhh!!!! you say to yourself. Now that's something I did not know. I hope he continues....

Well at the beginning of this post I mentioned that Excel is versatile. With a little imagination you can customize the date format in a way that's perfect for you.

From the dialog window select the Type Customize.
 
Well you can click on the Type that is close to what you want then modify the type anyway you want (highlighted with the yellow arrow). In this example I have changed the date to be yyyy / dd / mmmm. This displays my date as 2015 / 15 / January


Use the following guide to help customize your date... 

Display                                                  Format To Use
 Months as 1–12                                              m
 Months as 01–12                                            mm
 Months as Jan–Dec                                        mmm
 Months as January–December                       mmmm
 Months as the first letter of the month           mmmmm
 Days as 1–31                                                  d
 Days as 01–31                                                dd
 Days as Sun–Sat                                             ddd
 Days as Sunday–Saturday                              dddd
 Years as 00–99                                               yy
 Years as 1900–9999                                       yyyy

So the next time you have a spare minute at work, broaden you date formatting knowledge in Excel and wow your boss with your mad crazy Excel skills.


Beginner, Date Format

Working with Credit Card Numbers

Credit Card numbers are 16-digit numbers and if you ever enter a 15 digit number into a cell, you may discovered that Excel changes the last digit to a zero.

So why would Excel change this number? It comes down to how Excel was coded. Excel can handle only 15 digits of numerical accuracy.

So what do you do if you need to enter a 16 digit number manually? Well first ask yourself if Excel is secure enough to store valuable credit card information. If you still want to proceed, you have 3 easy ways of entering in the information...

  1. Precede the credit card number with an apostrophe. Excel then interprets the data as a text string rather than as a number.
  2. Pre-format the cell or range by using the Text number format. Select the range, choose Home > Number and then select Text from the Number Format drop-down control.
  3. Enter the card number with dashes or spaces. Embedding a non-numeric character such as a dash forces Excel to interpret the entry as text.

BEGINNER