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

Excel 2013 - Understanding the Protected View

You may have already encountered an Excel feature known as Protected View.

So what is Protected View. Weill its all about protecting you from malware.

Now you are saying to yourself... Hold on, I don't own any mid evil armor. What is all the fuss about malware and why should I care?

In a simplified definition, Malware refers to something that can harm your computer. There are many ways that hackers can manipulate Excel files so that harmful code can execute. The Protected View essentially prevents these types of attacks by opening a file in a protected environment. This is often called a sandbox by developers.

When you open an Excel workbook that you downloaded from the web, you’ll see a warning message above the Formula bar.  In addition, Excel’s title bar displays the message.

If you’re certain that the file is safe you can click Enable Editing.

If you don’t enable editing, you’ll be able to view the contents of the workbook, but you won’t be able to make any changes to it.

What causes Protected View?

Protected View kicks in for the following:

1. Files downloaded from the Internet
2. File attachments opened from Outlook
3. Files opened from potentially unsafe locations, such as your Temporary Internet Files Folder
4. Files that are blocked by File Block Policy 
5. Files that have expired digital signatures

So be sure to know where that file comes from and when in doubt, don't enable editing.

BEGINNER, PROTECTED VIEW

6 digit year analysis

As an analysis it’s my job to analyze data. Quite often I run across a common problem with dates exported from a mainframe job. Often the date is 6 characters long without any delimiter. Why oh why didn’t Y2K resolve this problem. Y2K??? you ask inquisitively?

For those not knowing, Y2K was a problem back in the late 1990s where computer programmers had to correct computer code. The problem was simple. When computers were new, memory was expensive so coders only represented the year with only 2 digits. Who would have thought that the code written in the late 70’s and early 80’s would still be in use?

As a result when the year 2000 rolled around, all the old code would show 00 for the year. We were told dire consequences would ensue; planes would drop from the sky, bank accounts would empty, worldwide panic. Fortunately none of that happened. But happily for thousands of programmers, secure jobs were available to verify millions of lines of code. The fix was simple; change the year to be 4 digits.

However there was also another fix, recode the handling of two digits so that the computer would know that 00 did not represent 1900, but rather 2000. And that leads me to today’s problem.

Whenever I see dates represented with only 6 digits I start by breaking the data down into columns for Month Day and Year. However I don’t know which column is the Month, Day or Year.

So I start by looking at the first column and see if there are numbers greater the 12. If there are, then the column can’t be the Month since there are only 12 months in a year.  In this example one cell has a 13 in it so this column cannot represent a month.

Continuing with my example the third column also has numbers greater than 12 so that leaves the second column as my month.

Examples

03	09	17
12	06	11
10	05	14
07	07	13
09	03	13
03	11	06
03	06	27
10	06	09
05	06	01
05	08	28
04	05	23
01	08	30
08	07	15
05	06	28
11	12	31
13	06	07
07	01	03
08	05	01
04	04	30
11	08	09

So now the mystery is which is the day column and which is the year column. Here is where you need to know your data. With my work I mostly deal with data from the years 1995 through 2014. So 95 – 14. Column 1 has numbers 1 – 13. However column 3 has numbers that include 31, 30, 27 and 28. Hmmmm. It looks like a day column. And it’s easy enough to verify. If the second column is the month (and we have already determined that it is), then I can verify the month in the row with the 31 in the third column. If that month has 31 days, then I then know the day is the third column of data. In my above example I have a 12 in the second column (December). 

11

12

31

So the second column is the Month and the 31 confirms that the third column is the day. That leaves the first column as the year.

So could I code this solution in Excel? 

I start by identifying the max value in each column.

	A	B	C	D	E	F	G
1	3	9	17		Max Col 1	Max Col 2	Max Col 3
2	12	6	11		13	12	31
3	10	5	14		=MAX(A:A)	=MAX(B:B)	=MAX(C:C)
4	7	7	13
5	9	3	13

Well right off I know column 2 has to be month since it’s max value is 12 and column 1 and 3 max value is greater than 12.

I also see column 3 has a max value of 31. So that looks like the day.

So very quickly I can identify which columns are which.

Year  Month  Day

Count value types in a column of data

There are times when you want to know how many negative or positive values there are in a column. When would you want this information, well who knows but I bet you will sleep better know how to do it.

You want to use the COUNTIF function. You simply identify the column of data that you want to count and then for the criteria put >0. Now in order for this to work, you need to put quotes around >0.

And I bet by now you have guessed that you could count the negative values just by reversing the >0

=COUNTIF(B:B,">0")     Returns the number of positive numbers in a range
=COUNTIF(B:B,"<0")     Returns the number of negative numbers in a range

BEGINNER, COUNTIF