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.
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)
It would be helpful to know the ‘Type’ of the fields.
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.
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.
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))
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.
designer, Thanks for your explanation.
Here are the links you’ll want to look at:
Looking at the documentation for the zeroblank( function, I think it could be misleading in this case. In the event that ConnectedOn, and SentAt are the same date, the result assigned to «invitation to acceptance» will be blank. The documentation seems to imply that at least one of the fields in the formula must be blank before the result will be blank.
I’ve submitted a correction.
Dave is being charitable by using the word “misleading” - I would characterize this help page as wrong. I think this help page was originally written by an employee over 30 years ago and I never caught this error. Oops.
The correction Dave submitted fixes the error, and I went ahead and added more detail. The web version of this help page is now updated, and these changes will be incorporated into the built-in help in the next release.