Problems calculating difference between two times

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?

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.

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

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?

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.

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.

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