Sentence
Is
True
From:
Recent Comments
Conbeam – Python version
I have converted the continuous beam analysis spreadsheet (last presented here) to Python code, linked to Excel VBA User Defined Functions (UDFs) with ExcelPython. To run the Python version you will need an installed copy of Python, including Numpy and Scipy. Everything else is included in the download file, including all the necessary ExcelPython files.
The spreadsheet (including full open source code) can be downloaded from Conbeampy.zip. To run the spreadsheet just unzip everything to any directory.
The file ConBeampy.xlsb includes all the new functions with one example of each.
ConBeampy-check.xlsb also includes checks of the Conbeam function results against results from Strand7, for 15 different configurations of beams with different span arrangements and support conditions. The screenshot below shows there was close agreement for all 15 cases.
Click for full size view
More examples of using each of the included functions will be provided in future posts.
Posted in Beam Bending, Excel, Finite Element Analysis, Frame Analysis, Link to Python, Newton, UDFs, VBA
Tagged Conbeampy, ConBeamU, continuous beam analysis, Excel, ExcelPython, Python, UDF, VBA
Leave a comment
Dealing with dates 3: opening and saving csv files without data corruption
This post makes use of a spreadsheet for reading data from text files, including comma separated value (csv) files. The latest version includes a routine for saving the imported data in csv format. It can be downloaded (including full open-source code) from Text-in2.zip.
Excel allows csv files to be opened and saved directly from the File menu, without going through the File-Import process. Unfortunately the data is treated in the same way as text entered into a spreadsheet cell formatted as General, so any text that looks like a number will be stored as a numerical value, and displayed in the format that Excel deems appropriate. If the file is saved with the original name the data will be changed, which in some cases result in dates being changed, or numeric codes being converted into dates.
This is illustrated with the short csv file below, consisting of four columns:
– Numbers
– Text that looks like numbers
– Dates
– Text that looks like dates
The screenshot below shows the results of opening this file in Excel with default language set to Australian English, then saving it as csv, without making any changes.
It can be seen that changes have occurred in all the columns:
- Fractions are treated as dates; 3/4 becomes 3-Apr
- Leading zeros in numerical text strings are deleted
- Numerical text with a single E is treated as scientific notation.
- “” around a text string is deleted and ignored
- Any separator in a text string is converted to /.
- Four digit years are sometimes converted to two, and vice versa
- All the entries under “Look like dates” have been converted into dates, except for those with numbers separated by spaces.
Changing the default language to US English results in significantly different results for the dates, and strings interpreted as dates:
- The fractions (3/4) are converted to 4-Mar, rather than 3-Apr
- Date strings with two numbers are treated as mm/dd, rather than dd/mm, except when the first number is greater than 12.
- Numeric date strings with three numbers display with the day and month numbers unchanged, but are treated in Excel as being mm/dd, rather than dd/mm.
- Unambiguous date strings, are left with the day/month order unchanged.
CSV files can now be opened and saved without any unintended changes using the Spreadsheet Text-in2 (download link at the top of this post), which has three new features:
- Optional formatting of each column
- Text2Date() function to convert any date string into an Excel date value, optionally with a specified day/month/year sequence.
- Save imported data to a csv file.
If each column of a csv file is imported as text (format code = @), the data is all imported with no changes:
For simple applications numbers imported as text will be treated as numbers by Excel, but for many purposes they must be converted to numbers for everything to work as expected; for instance the Sum function treats all text as having value zer0. Any column containing numeric data can be imported as numbers by entering the appropriate format code; for example enter 0.00 to import as numbers and display to 2 decimal points. See the Text-in2 NumFormats sheet for more examples of number format codes.
The dates are imported as text, but can be converted to Excel date values using the Text2Date function.
When the imported data is saved to a csv file all text remains exactly as in the original file:
Text2Date optionally allows the date order to be specified, with the System local default being used when not specified. Date order options are:
0: mm/dd/yyyy (default for US English)
1: dd/mm/yyyy (default for non-US English)
2: yyyy/mm/dd
The function also has an optional Separator argument, which should be specified for any separator other than /.
The screenshot below illustrates use of Text2Date with DateOrder set to zero. Note that in this case the function returns an error message (“Not a valid date string”) if the first number is greater than twelve, or the second position has a month name, rather than a number:
Also note that in this case the format for column 1 was set to 0.00 (rather than @), so this column has been imported as numbers, rather than text. The fractions in rows 32 and 36 have been converted to decimal values, but the text in column 2 remains unchanged.
More details of the Text-in2 spreadsheet (plus other text functions) can be found at:
Daily Download 32: Text functions
Also see: Extracting numbers with regular expressions
Click for full size view
Dealing with dates 2: Getting cell data type and format code
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:
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:
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 Data types, date formats, Excel, Format codes, Number formats, UDF, VBA, [$-F800]
6 Comments
Dealing with dates -1, date values and formats
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:
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.
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.
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.
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:
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:
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:
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 date formatting, date values, dates, Excel, language settings, regional settings
4 Comments
Newton Excel Bach, not (just) an Excel Blog 