Category: Software

Excel: How to calculate time differences beyond midnight

Suppose you have a document with a date and time column. Calculating time differences is easy, you can simply subtract an earlier time from a later one1. In the screenshot below, column C shows the difference between the time in consecutive rows (column B). However, you can see that this simple approach fails just after midnight:

Excel: Simple time difference calculation fails at midnight

The time difference in row 4 is negative and also wrong. This becomes very clear when you switch to a 24h time format and enable negative times2:

Excel: Time difference calculation fails at midnight
Did you notice that the dates in column A have changed? This was due to the method I used to enable negative time values (see note 2 below).

Solution: Include the date in the calculation

Internally, dates are represented as whole numbers and times as fractional numbers smaller than 1 (1 would be 24h = 1 whole day). This means you can simply add date and time! Therefore, a better formula to calculate time differences is:

=A2+B2-(A1+B1)

By including the date, this formula works fine after midnight.

What if you don’t have a date column?

You can use a clever approach I found here:

Columns C and D included for explanatory purposes
=A3-A2+(A3<A2)

A3-A2 is the simple time difference calculation we used in the beginning. (A3<A2) returns TRUE only when the next day starts and the time is “smaller” than in the row above (see row 5, column C). What makes this work for our purposes it that TRUE is evaluated as 1 while FALSE is 0 (see column D). As mentioned above, a date/time value of 1 corresponds to 1 day (24h).

In row 5, Excel is therefore calculating the difference between 24h and 23h 59min 59s, which is 1 second.

1 This will work if Excel correctly recognized the value as a time (not a text). You can test this with the ISNUMBER function which should return TRUE.
2 The easiest way to get Excel to show negative times is by enabling the 1904 date system in the advanced options.
3 If you want to see time values of 24h or more, use an elapsed time format with square brackets, e.g. [h]:mm:ss instead of h:mm:ss.

Thoughts on Form W-8BEN-E for companies selling software licenses

Your company is selling software and a customer in the U.S. is asking you for form W-8BEN-E? You should supply it or risk having 30% of your payment withheld. Some customers might also not place an order at all before receiving this form.

Disclaimer: This blog post is presented for educational and entertainment purposes only. I originally wrote the instructions below for my own company. They might be incomplete, wrong or not applicable to your situation.

Preliminary considerations

  • What are software licenses (in tax treaty terms)? For this article, I’m assuming they fall under royalties / copyright.
  • Does your country have a tax treaty with the U.S.? How much will you save by filling out from W-8BEN-E? Download table 1 (tax rates…) from this IRS page, find your country and look up the royalties / copyright tax rate:

    Luxembourg US tax treaty: royalties
    Points at Malta: Ha-ha!

Official information

Other links you might find useful

Instructions for filling out form W-8BEN-E

Part I – Identification of Beneficial Owner

Complete line 1 and 2

Check “Corporation” on line 4 (if you’re not working for a corporation, you’re probably reading the wrong blog post).

Skip line 5 (FATCA status) entirely. Software licenses are excluded from the FATCA definition of “withholdable payment” [see: Experis – Finance FATCA Checklist for Multinational Companies (PDF); EY – Information reporting and withholding: the impact of Foreign Account Tax Compliance Act (FATCA) on multinational organizations (PDF)].

Enter address on line 6.

Provide a US taxpayer identification number (TIN) in the form of an employer identification number (EIN) on line 8 or the tax ID assigned in your country on line 9b.

Part III – Claim of Tax Treaty Benefits

Line 14a: Check the box and enter your country.

Line 14b: Check the box at the beginning. Will you have to check another box below? The official instructions say:

If you are a resident of a foreign country that has entered into an income tax treaty with the United States that contains a limitation on benefits (LOB) article, you must complete one of the checkboxes in line 14b. You may only check a box if the limitation on benefits article in that treaty includes a provision that corresponds to the checkbox on which you are relying to claim treaty benefits. A particular treaty might not include every type of test for which a checkbox is provided.

To find out if your country’s tax treaty includes a LOB article (it most probably does), go to the IRS tax treaties tables page and look up your country in table 4 (limitation on benefits). You can find the complete text of the tax treaty through this page and read the articles mentioned in table 4. The official instructions for form W-8BEN-E also contain summarized versions of the LOB tests which might be useful. You should then be able to figure out which box to check.

Line 15: As far as I understood the instructions, this line has to be filled out only “if the treaty contains different withholding rates for different types of royalties.”[see IRS instructions]. Remember table 1 from the very beginning of this blog post? Look at it again. Are all royalties rates the same? If yes, skip line 15, otherwise, fill it in. As “n/a” is not a rate (I hope), I skipped this line (all rates were the same for Luxembourg).

Part XXX – Certification

Sign, fill in print name and date in U.S. format, check the box at the bottom.

Even though I really don’t want to deal with this topic again, I’ll leave the comments open so that you can correct me on all that’s wrong with my instructions. However, please don’t ask me to help you fill out form W-8BEN-E for your company!

Solution: The target version of the .NET Framework in the project does not match the .NET Framework launch condition version

The problem:

You’re building a setup project in Visual Studio (aka deployment project or installer project) and find the following warning in the build output:

The target version of the .NET framework in the project does not match the NET framework launch condition version...
The target version of the .NET framework in the project does not match the NET framework launch condition version…

Why this is happening:

The project you want to deploy (using the setup project) targets a more recent .NET Framework than specified in the setup project’s launch conditions.

In my case, the project targets .NET Framework 4.7:

application target .NET framework
Double click on ‘Properties’ in the Solution Explorer to show this screen.

Meanwhile, the setup project was still checking for .NET Framework 4.6.1 as a launch condition (requirement on the target machine).

I had previously thought that this was due to

The solution: Update the launch condition version to match the target framework of your project.

In the Setup Project, double click on ‘Microsoft .NET Framework’ to show its properties. Then change the .NET Framework version in the dropdown list:

launch condition .net framework version

One more thing: Check the prerequisites

While this has nothing to do with the warning, you might as well check which version of the .NET framework your setup program will install (if any).

Right click on the setup project and select ‘Properties’. In the Property Pages dialog, click on ‘Prerequisites…’:

setup program prerequisites

 

Update: While I thought this issue only appears when you update you project’s target framework after having already created a setup project, it seems that it also happens when creating a new setup project. Also, changing the target framework version does not seem to update the InstallUrl (not sure if this is important).

Connecting a scale or balance to a Raspberry Pi: project ideas

On digitalscalesblog.com, I showed how easy it can be to send the weight from a scale to a Python script running on a Raspberry Pi (if you choose the right scale). As I had never used Python before, all the script did was print the weight data. That doesn’t mean you have to stop there. Here a some ideas and links to get you going.

  • Use the Raspberry Pi as a serial device server (from RS-232 to TCP/IP over Ethernet or WiFi). Code examples can be found in the pySerial documentation.
  • Build a protocol converter. Most scales use proprietary protocols, but you can convert the data to MQTT or other protocols as shown here. Do not say that you have developed a protocol converter, call it an IoT gateway to get more attention.
  • Use speech synthesis to output the weight as spoken words for visually impaired users.
  • Use speech recognition to send commands to the scale (e.g. the tare command).
  • Install a Pi Camera Module and take a picture of the user every time the scale is overloaded.
  • Turn the scale into a checkweigher by comparing the weight with preset values and making under/accept/over LEDs light up.
  • Transform the weight into a proportional analog signal (voltage) because… I have no idea! Why do people keep asking for weighing scales with an analog output? Seriously, if you know the answer, please tell me.
  • Trigger an alarm or something worse when an object is removed from the scale:

Raiders of the Lost Ark IMAX Movie CLIP – Throw Me the Idol (2012) – Harrison Ford Movie

Solution: Your administrator has blocked this application because it potentially poses a security risk to your computer

The Problem: Your administrator has blocked this application…

You’re trying to install a ClickOnce application and get an error message saying: “Your administrator has blocked this application because it potentially poses a security risk to your computer”:
Your administrator has blocked this application because it potentially poses a security risk to your computer

You are the administrator and don’t remember blocking any applications (if you’re not the admin, contact them instead of reading this article).

The reason this is happening

The ClickOnce trust prompt is disabled on your operating system, so instead of a dialog (prompt) which allows you to either proceed with the installation or not, you just get the message above.

The solution

You can change the ClickOnce trust prompt behavior by editing the registry subkeys under:

\HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\.NETFramework\Security\TrustManager\PromptingLevel

There should be five subkeys, one for each security zone:
Registry trustmanager promptinglevel

Each subkey can have one of the following values:

  • Disabled: No trust prompt is shown, instead you get the error message above.
  • Enabled: A trust prompt is shown.
  • AuthenticodeRequired: A trust prompt is shown only for signed applications (other applications cannot be installed).

Trust prompt example

If the application you’re trying to install has been published on the Internet, you’ll want to set the Internet subkey to “Enabled” (or to “AuthenticodeRequired” if the application has been digitally signed). You should then see a trust prompt instead of the “your administrator has blocked this application” error:
Trust Prompt - do you want to install this application

Free Trust Prompt Tool

As an alternative to editing the registry manually, you can also use a free trust prompt tool I wrote:
Trust Prompt Tool

It allows you to easily display and modify the trust prompt configuration.

Notes:

  • If you don’t trust yourself or other people using your PC, you can change the respective zone setting back to “Disabled” after installing the ClickOnce application. The trust prompt configuration has no effect on installed applications and their updates.
  • You could also add the publisher’s site to your trusted sites and only enable trust prompts for the TrustedSites subkey.
  • Are you wondering why all your trust prompt settings have been changed to “Disabled”? Unfortunately, I have no idea. I’ve had this happen on multiple PCs and I’m pretty sure I didn’t do this myself.