Category Archives: Excel

Time in hours and minutes between two dates

Posted on September 30, 2018 by

Dates and times in Excel are stored as a date number, which is the number of days since 1 January 1900, with the value after the decimal point representing the time of day. To calculate the number of hours between … Continue reading →

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

New AS 3600 and RC Design Functions 8

Posted on September 23, 2018 by

A revised version of the Australian Concrete Structures code, AS 3600, was recently published, with many significant changes, including: Changes to ULS reduction Factors Creep and shrinkage Curvature and deflection calculations Crack width calculation and stress limits Major  changes to … Continue reading →

Posted in Concrete, Newton, UDFs, VBA | Tagged , , , , , | Leave a comment

Arc-spline update

Posted on September 9, 2018 by

The input for the first version of the Arcspline function required the coordinates of the centre of each arc, as well as the angle of the tangents at each end of the arc.  I have now added a new version with … Continue reading →

Posted in Coordinate Geometry, Excel, Maths, Newton, UDFs, VBA | Tagged , , , , | Leave a comment

Using Redim and 3DFrame

Posted on August 31, 2018 by

I recently received a query about the 3DFrame spreadsheet failing to run.  The problem was that the code was trying to Redim an array that had not been previously created with a Dim statement.  This does not cause a problem … Continue reading →

Posted in Excel, Frame Analysis, VBA | Tagged , , , | 1 Comment

Using VBA Evaluate as an Array Function

Posted on August 24, 2018 by

I recently discovered from a thread at  Chandoo’s Excel Forum that the VBA Evaluate function can be used as an array function.  As a simple example: Range(“A11:A16”).Value = Evaluate(“= B11:B16 + C11:C16”) will add the values in columns B and C, … Continue reading →

Posted in Arrays, Coordinate Geometry, Excel, Maths, UDFs, VBA | Tagged , , , , | 2 Comments

Arcs and arc-splines

Posted on August 17, 2018 by

I have added two user defined functions (UDFs) to the IP2 spreadsheet to generate coordinates for a single  arc, or a series of arcs connected (if necessary) by straight lines.  The new version may be downloaded from: IP2.zip As an example … Continue reading →

Posted in Animation, Charts, Charts, Coordinate Geometry, Drawing, Excel, Maths, UDFs, VBA | Tagged , , , , , , | 1 Comment

New Tricks …

Posted on August 16, 2018 by

… or in some cases, old but forgotten tricks. The first comes from a Quora question, asking for the most underutilised feature in Excel. When de-bugging a VBA routine you can set a break point where the code will stop: … Continue reading →

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