# Incorrect number format -- how to correct it

I’m trying to calculate the days between one date and another. The problem is that the result of the calculation, “SentAt” minus “ConnectedOn” in the column “invitation…,” isn’t the simple numeral I need.

Please see the “invitation to acceptance” field in the screenshot. What format is that, and how do I make that into a simple number?

Thanks

It really helps if you include the calculation and perhaps an example of what you expect it to look like. When it comes to dates, the English language is particularly ambiguous. For example, if I ask for the number of days between July 15 and July 17, what number would you give me? 3, 2, or 1. I can see different people making a case for all three answers.

That doesn’t make the actual calculation complicated. It just means a person has to know if you are including the starting and ending dates, just one of those two dates, or neither of them (giving the answer 1 in my example above).

As I understand it, the “date” is just a big number representing the number of days from some early starting time. Various Panorama functions convert this number to what you see as day/month/year values.

Because the date is internally just one number, each day that number gets bigger. To find out the number of days between two dates - fields that are date type - you just have to subtract the start date from the end date. But remember those ambiguities I mentioned.

When I want to learn something, the first thing I do is get out of the data concerned - there is too much emotional involvement in the result. I just create a simple example so I see how a relationship works. THEN I apply it to the real data.

In a new database, I created three fields, A, B, and C. I made A and B date types and C a number (integer). I put 7/15/24 in A, 7/17/24 in B, and the result of B - A (end date minus start date) in C using the Morph function under the field menu.

The result was 2. Panorama interprets that request as the number of days between two dates INCLUDING the end date. I’d say that is stretching the definition of “Between”. But knowing Panorama does that, I can modify the simple B-A=C calculation so it’s B-A-1 = C. That gives me 1 day because only one day, 7/16/24, is between 7/15//24 and 7/17/24.

That’s why we need to know what you mean by “between” with an example of two dates.

1 Like

When you subtract a later date from an earlier one, you are going to get a negative number, because later dates are represented by greater numbers. Either subtract in opposite order, or use the abs( function to get the absolute value of the difference.

``````ConnectedOn - SentAt
``````

or

``````abs(SentAt - ConnectedOn)
``````
1 Like

It would be helpful to know the ‘Type’ of the fields.

1 Like

In his screen shot, the first two fields are clearly date fields, and the third could be either integer or float. Integer would make the most sense, because those differences will always have integer values, but there is no reason float wouldn’t work.

1 Like

As Paul said, dates are represented by the number of days since some date over 2 million days ago. It’s actually a standard representation called the Julian day number. An empty cell has a value of zero, so when there is no ConnectedOn value, you are just getting the Julian day number for the SentAt date.

1 Like

Dave is correct, but I think we can go a bit farther. You probably want blank entries for records when ConnectedOn is blank (which is zero). To do that, use this formula:

``````zeroblank(?(ConnectedOn=0,0,ConnectedOn - SentAt))
``````
1 Like

Thanks, Dave. Actually, the screenshot is just an error that I had caught already. It’s of a slightly earlier version from before I fixed it. The current calculation is what you describe.

Thanks, admin Jim. I’ll look up “ConnectedOn=0,0…” etc in the help section.

And thanks RAmeeti and dave.