Converting Text Dates to a Numeric Dates

Converting Text Dates to Numeric Dates

Sometimes when you import data, particularly dates,  Excel treats the data as text. You can use the DATEVALUE() function to convert text dates to real numeric dates.
 DATEVALUE() returns the serial number of the selected date. Once you have the serial date, you can format it to display as a date.

In this example, the data in Column A are dates that Excel treats as text. (A good way to tell if Excel is treating something as a number or text is to check the alignment. Text is left aligned whereas numbers are left aligned.)  In column B2, I used the formula =DATEVALUE(A2) to change the text in cell A2 into a serial number which displays as 39894.

39894 is the serial number of the date in cell A2.



To convert it to something meaningful, you just need to go the Number group  on the Home Tab

and click on the drop-down arrow beside General



In this particular example, I then selected the short date

The result is shown below:

DATEVALUE() only works on text dates. If you try it on a column of text and numeric dates, you will get a #VALUE error on the entries that are numeric.

LEN

I was getting my U.S. Christmas mailing list in order and noticed a problem on some of my zipcodes. Many of New England's zip codes begin with a zero so when I pulled my addresses into Excel to sort and filter them, Excel dropped the 0 from the beginning of the zipcode so for example, instead of 02186, Excel displayed 2186.
(It did this because it assumed the ZipCode was a number instead of text.)

The fix is easy.

In this case, I clicked in cell I2  and entered =IF(LEN(H2)=5,H2,"0"&H2)

LEN() counts the number of characters. So, I told Excel -if the number of characters in cell H2 total 5 then just display the contents of cell H2. However, if the number of characters in H2 do not total 5 then include a 0 at the beginning of the H2 cell contents. I used an ampersand (&) to tell Excel to join the 0 and the zipcode together  however you could also have used the Concatenate() function- I just think the & is faster.  Both LEN() and Concatenate() are Text functions.

Once I copied the formula down, I then selected all the zipcodes in Column I, copied them and then did Paste Values so that the formulas were overwritten with the actual numbers. I did that so that when I deleted Column H, my new zipcodes would display.

An alternative way would be to just hide Column H. 

 
This  IF assumed residential US zipcodes. If you had business zip codes which would have more than 5 characters or were including European postal codes then you would have to change the IF a bit.

Freezing Columns In an Access Table

You can FREEZE one or more of the columns in an Access table so that they become the leftmost columns and are visible at all times no matter where you scroll.

 

• Open a table in Datasheet view.
• Select the columns you want to freeze by clicking the field selector (top of the column) for that column
•  To select more than one column, click the column field selector and then, without releasing the mouse button, drag to extend the selection.
• Right-click and select Freeze Columns

Access handles this a bit differently from Excel - what it does is literally move that column so that it becomes the left most column in the table.

• To unfreeze a column, Right-click on the  frozen column heading and select Unfreeze Columns

And of course, Access does NOT move the column back to its original position - you have to move it manually.