Problems calculating difference between two times

Panorama X
1

I am having great difficulty in converting a Panorama 6 database into Panorama X. The original import of 13,000 records (with 17 fields) seemed to go well, but formulas that always worked fine in 6 are not working in X. The main formula is as follows:

Field "StrtTimeConv"
formulafill time(StartTime)
Field «End Time Conv»
formulafill time(EndTime)
Field "Time Diff"
formulafill timedifference(StrtTimeConv,«End Time Conv»)
Field "Time Diff text"
formulafill timepattern(«Time Diff»,“hh:mm”)
Field «Decimal time»
formulafill «Time Diff»/3600
Field Case

The purpose is to determine a decimal number that represents the difference (in hours) of a starting time and an ending time. In the formula, I first convert the starting time into seconds. Next I convert the ending time into seconds. Next, I subtract start time (in seconds) from end time (in seconds). Next, I express the time difference as hh:mm. Finally, I divide the time difference in seconds by 3600, to achieve my goal of a decimal respresentation of the time difference.

This should be simple, but when I run the formula, I immediately get an error message - time( function: illegal time “”. I cannot understand the problem, as start and end time are always normal times (such as 4:25 PM). I have studied the formulas in Panorama 6 and X and the only difference I see is that the previous FormulaFill is now written with no capitals (formulafill). I made that change, but there is no difference.

What am I missing?

2

I setup a database with the same field names, and some sample start and end times, and your procedure worked for me.

I can duplicate the error by declaring a variable named StartTime and putting something other than a valid time in it. So that’s my best guess. I think you have a global or fileglobal variable named StartTime, and it’s being used in the formula in place of the field.

3

Another possible error would be an empty record, particularly if it’s the first record. time(“”) gives you that error.

4

Interesting. There are some empty records, as not all entries are time entries. Some are just for related expenses, with no start or end time invovled.That was never a problem in Panorama 6 - has something changed in that regard?

I created a copy of the file, deleted all records with blank time fields, and it does then work as it should. Something must have changed in Panorama X that impacts this. Do I need to start by selecting all records withhout empty time fields, run the procedure, and then end it by selecting all records? Or is there a simpler solution?

5

Yes. It does look like something has changed. In Panorama 6, time(“”) returns 0. In fact any invalid time returns zero. The only error is a type mismatch, if you give it a number instead of text.

Selecting the records with non-empty times sounds like the simplest solution to me.

6

I found a different simple solution - I changed all of the empty time fields to “1” (without the quotes).

Now, the start and end times for those fields are the same, so the calculation results in a zero for those records, which is fine. All the others do what I want. Simple, but not quite elegant.

7

Another solution would be to use the catcherror( function, like this:

formulafill catcherror(0,time(...))

I find the catcherror( function to be one of my favorite new functions in Panorama X.

1 Like