Dealing with dates 2: Getting cell data type and format code

Posted on 08/06/2015 by dougaj4

When working with imported data, or data from an opened csv file, it is often important to know the data type that has been used to store the data.  This may not be obvious, for instance in the previous post we saw that a date imported into a cell formatted as text displays as text, but is converted into a date value when used in a simple formula.  However if the cell is included as part of a range it will be treated as text, with a value of zero, even for a single cell range.  For instance, if we format cell A1 as text and enter 8-jun the cell will display as entered (including the lower case j), but in another cell:

  • =A1 will display 8-jun
  • =A1+0 will (in 2015) display  42163 (the date number for 8 Jun 2015)
  • =Sum(A1) will display 0 (because all text is treated as having a value of 0 when used in the Sum function, even if it looks like a date).

If we now format the cell as General, and re-enter 8-jun, the cell will display 8-Jun and =Sum(A1) will display 42163 (if it is formatted as a number), or 8-Jun (if it is formatted as a date).

The User Defined Function (UDF) below will return information about the data type for the values in a selected single column range:

Function GetDType(DRange As Range) As Variant
Dim DTypeA() As Variant, DVal As Variant, NumRows As Long, i As Long

DVal = DRange.Value
NumRows = UBound(DVal)
ReDim DTypeA(1 To NumRows, 1 To 2)

For i = 1 To NumRows
    DTypeA(i, 1) = DVal(i, 1)
    DTypeA(i, 2) = TypeName(DVal(i, 1))
Next i

GetDType = DTypeA
End Function

The function must be entered as an array function (using Ctrl-Shift-Enter), and returns two columns.  The first shows the actual value stored by Excel (as opposed to the value displayed), and the second the data-type:

Dates-2-1

To work with number formats in VBA (which we need to use to open csv files without changing the data) we need to use Excel’s format codes, but there is no built-in way to show the correct format code for any selected cell format.  An excellent and (almost) comprehensive  guide to Excel number formats can be found at: A comprehensive guide to Number Formats in Excel, but for a quick way to find the correct number format, the UDF below will return the format code for any selected cell:

Function GetNumformat(Target As Range, Optional UseLocal As Boolean = True)

If UseLocal Then
    GetNumformat = Target(1, 1).NumberFormatLocal
Else
    GetNumformat = Target(1, 1).NumberFormat
End If

End Function

Examples of some format codes are shown below:

Dates-2-2

Click for full size view

 

Each format comes in a standard and local version; the local version is returned by default.

Full details of how to generate these sometimes lengthy codes are given in the article linked above, other than for the start of the codes associated with Long Date and Time ([$-F800] and [$-F400]).  A search on these codes suggests that the answer to the question of what they mean is, no-one knows (my lord), but a bit of experimentation shows that if the code between the square brackets is applied as a custom format (including the brackets) a date displays in date format on the spreadsheet, but in date number format in the edit bar, whereas applying the full code the date format displays in both the worksheet and the edit bar.

These functions plus routines to allow data from csv files to be quickly imported and saved without corruption will be available for download in the next post in this series.

 

Posted in Excel, UDFs, VBA | Tagged , , , , , , , | 6 Comments

Dealing with dates -1, date values and formats

Posted on 06/06/2015 by dougaj4

Following a discussion at Eng-Tips, I have been having a closer look at how Excel handles the entry and display of dates.  The Eng-Tips discussion concerned changes to data when csv files are opened in Excel.  I will deal with that (together with a way to open and save csv files in Excel without changing anything) in a later posts, but for today I want to look at the basics of how Excel stores and formats dates.

Excel stores dates as a day number, starting from 1st January 1900.  If you enter a sequence of numbers: 1, 2, 3, … in different cells, then format the cells as dates, it will display as below:

Dates-1-2

Note that day 60 displays as 29th Feb 1900, which does not actually exist, since century years not divisible by 400 are not leap years.  The extra day was introduced to maintain compatibility with the earlier spreadsheet Lotus 123, who had probably done it by mistake.

There is also an option (under Options-Advanced-When Calculating This Workbook) to set day 1 to 1st January 1904, which is the standard for Apple Mac versions of Excel.  Everything that follows uses the Windows default.

In addition to the Apple/Windows differences the date formats must also cope with different conventions for writing dates, especially when the month is shown as a number, and must deal with text that may or may not be a date.

The screenshot below illustrates different behaviour depending on format settings, and the first character of the entered text.  Column A was pre-formatted to “Text” (to display the values as entered).  The remaining columns were left in the default format (“General”).

In Columns A and C the text was entered as shown in Column A.  In Columns D and G the same text was entered, except preceded by a space for Column D and an ‘ for Column G.

Columns B, D, F, and H contain a formula subtracting the value in Cell A3 (123) from the value in the adjacent cell.

The default Windows date display for my location (Australian English) was used, i.e. day-month-year.

Dates-1-1

Note that:

  • The values with a non-numeric character (all except Row 3) are displayed as text in Column A, as entered, but are stored by Excel as a date value, as can be seen in Column B.
  • The  values separated by “-” and “/” are treated in exactly the same way in all cases.
  • The values in Rows 4 and 5 (and 7 and 8) are ambiguous.  In Row 4, 23 is treated as being a year (2023), because it cannot be a month, but in Row 5 the 12 is treated as a day, and the 3 as a month (March).
  • Three separated values are treated as day, month, year, in accordance with the Windows language setting.
  • In Column C the entered text is automatically re-formatted to a date format, but the format depends on the text entered.  Note that 1-23 has been converted to 1-Jan-2023, maintaining the day-month-year format, but 12-3 is converted to 12-Mar-2015, i.e. day-month for the current year.
  • In Column E, where the first character entered is a space,  123 is converted to a number, but all the other entries remain as text, and hence return the #VALUE! error if used in a formula.
  • In Column G, where the first character entered is an apostrophe, all the entries display as text (without the ‘), but as for Column A, they are stored as a date value.

The screenshot below shows details of the format automatically applied to the dates in Column C entered as day-month-year.  This is the standard short date format, and the * indicates that the displayed date will be re-formatted, depending on the language setting.

Dates-1-3

The note above says that the starred date formats will change in response to regional date and time settings, but the default setting (at least in Windows 8) is that the regional date and time settings are tied to the selected language, so to change the date display the Windows language should be changed, as shown below.

Dates-1-4

To change to United States English in Windows 8.1 I had first to add US English to the list in the Control Panel – Language dialog, then move it into top position:

Dates-1-5

After changing to US English the display is immediately updated:

  • The date values in Column D all remain unchanged.
  • The date display in Column C rows 6 and 9 changes to the month-day-year format.
  • The other dates in Column C are unchanged.
  • The text displayed in Columns A and G is unchanged in all cases, but all the date values have changed.
  • The text displayed in Column E is also unchanged, but in this case there is no corresponding date value.

After changing the language re-entering the same values in Column C will result in a new date value:

Dates-1-6

Note that the date values in Column D now match those in Columns B and H.  The dates in Rows 4 and 7 are all the same, but in Columns A and G it is displayed as month-day (i.e. Jan-23rd), whereas in Column C it is displayed as month-year (i.e. Jan-2015).

Returning Australian English to top position in the Control Panel table reverses the process, with the dates in Column C maintaining their date value, but changing format in rows 6 and 9, and text in Columns A and G remaining unchanged, but all the associated date values change:

Dates-1-7

In summary, when everyone using a worksheet with dates uses the same language settings, and the same date format, the automatic formatting features of Excel should present no problem, although it is still necessary to take care when entering dates in day-month (or month-day) format that the correct year is entered.

For situations where worksheets may be shared between countries with different formats however great care should be taken, especially when calculations involving date differences are involved.  Further recommendations can be found in an excellent article at Excel Semi-Pro Regional Date Formats in Excel.

Posted in Excel | Tagged , , , , , | 4 Comments

Reversing Bob

Posted on 31/05/2015 by dougaj4

Following our look at Weird Al Jankovic’s palindromic song, Bob, I have written two short VBA User Defined Functions (UDFs) to help writing palindromes (and possibly with other tasks as well).

Reverse() will, as the name suggests, reverse any text entered as the argument, for instance:
” I, man, am regal – a German am I” becomes:
” I ma namreG a – lager ma ,nam ,I”

Reverse also has two optional arguments to start and stop at specified characters, counting from the start and end of the original string, so:
=reverse(“abcdefgh”,2,3) returns:
“fedcb”

Palin() returns a palindrome consisting of the argument string followed by the string reversed, so:
=Palin(“Never od”) returns:
“Never oddo reveN”

Palin has two optional arguments:
“Rev” = False (default) will start with the input string, followed by the reversed string, whereas Rev = True will put the reversed string first.
“RepeatCen” = False (default) will not repeat the last character of the input string, whereas True will repeat the character.

The code for these two functions is quite short:

Function Reverse(Base As String, Optional FirstChar As Long = 1, Optional LastChar As Long = 1) As String
Dim Rtn As String, i As Long

For i = Len(Base) - LastChar + 1 To FirstChar Step -1
    Rtn = Rtn & Mid(Base, i, 1)
Next i
Reverse = Rtn

End Function

Function Palin(Base As String, Optional Rev As Boolean = False, Optional RepeatCen As Boolean = False) As String
Dim Rtn As String, i As Long, LastChar As Long

If RepeatCen = True Then LastChar = 1 Else LastChar = 2

If Rev = True Then
    Rtn = Reverse(Base, LastChar, 1)
    Palin = Rtn & Base
Else
    Rtn = Reverse(Base, 1, LastChar)
    Palin = Base & Rtn
End If

End Function

But since one of the functions is named Palin, we really should do it in Python, which is even shorter:

def palin(pstring, rev, repeatcen):

    lastchar = 1
    if repeatcen == True: lastchar = 0
    strlen = len(pstring)
    if rev == True:
        return pstring[::-1] + pstring[lastchar:strlen]
    else:
        return pstring + pstring[strlen-1-lastchar::-1] 

def reverse(pstring, firstchar, lastchar):
    strlen = len(pstring)
    if firstchar > 1:
        return pstring[strlen-(lastchar):firstchar-2:-1]
    else:
        return pstring[strlen-(lastchar)::-1]

Both versions of the functions can be downloaded from Palin.zip.

The Python version includes all the necessary ExcelPython files, but will need an installed copy of Python.

The files also include the full lyrics of Bob, both forwards and backwards:

Palin1

Posted in Excel, Link to Python, UDFs, VBA | Tagged , , , , , | Leave a comment

Two exceptional musicians

Posted on 25/05/2015 by dougaj4

On Saturday night we were treated to fine performances from two exceptional young musicians.  The first was Stefan Jackiw, performing with The Australian Chamber Orchestra at Angel Place.  Here he is playing a Beethoven piece in 2009:

Then after the concert, walking down a traffic free and pedestrian packed George Street, on the way to see the Circular Quay illuminations the guitarist Tom Ward was busking to a large crowd.  Most of the Youtube clips of his outside performances have terrible sound quality, but it’s not too bad on this one:

(the battered guitar is still going!)

It seems that Tom Ward has not yet been deemed Wiki-worthy, but you can read more about him at last.fm.

He also plays inside sometimes:

 

 

Posted in Bach | Tagged , , , | Leave a comment

Baby Names and Pivot Tables

Posted on 17/05/2015 by dougaj4

boyslast (1)

Baby Boom: An Excel Tutorial on Analyzing Large Data Sets is a comprehensive (and free) tutorial looking at analysing public databases of baby names, using Excel:

There are professional data analysts out there who tackle “big data” with complex software, but it’s possible to do a surprising amount of analysis with Microsoft Excel. In this case, we’re using baby names from California based on the United States Social Security Baby Names Database. In this tutorial, you’ll not only learn how to manipulate big data in Excel, you’ll learn some critical thinking skills to uncover some of the flaws within databases. As you’ll see, the Social Security database, which goes back to 1880, has some weird and wonderful anomalies that we’ll discuss.

The tutorial is comprehensive and well presented, including the following topics:

Download the data andimport it into Excel 
Use Filters 
SanityChecks 
Summarize withPivot Tables 
Add aPivotChart 
AnotherSanity Check 
Explore yourdata and uncover insights 
Determineimportant ratios 
Graph individualdata points and trends 
Visualizeyour data

For more on analysing and visualising data also see the authors blog:
prooffreader.com

About the author: David Taylor is a scientist who does freelance data analysis for paying customers so that he can afford to do fun data analysis for free on his blog, prooffreader.com. (Yes, “prooffreader” is misspelled; that’s the joke!)

 

Posted in Charts, Excel | Tagged , , , , , | Leave a comment