Date to Day Count
I was wondering if it is possible to calculate the number of days based on one date in another app. We are a real estate firm that would like to use this feature to calculate the number of days a listing is on the market based on listing date. In excel it would be something similar to
"=today - 2/4/15"
equaling 29d.
The Podio support said that it's possible with some javascript. Would someone be able to explain this to me a bit?
Thanks!
-
If you were calculating based on date in the same app, it would be very easy by typing the following into a calculated field:
moment().diff(moment(@Created On),'days');or, more simply:
moment(@Created On).fromNow();Just remember to type in the "@" sign and select "Created On" or any other date field you wish--you can do so by typing the name of the field or clicking with the mouse.
However, there is a major caveat with this: Calculated fields only update when a field that is referenced in them is updated. In this example, @Created On would never be updated, and therefore the age of the item will not increase on a daily basis.
One simple solution is to include a field that you know will change often, such as a note field, as follows:
@Some Other Field;
@Yet Another Frequently Updated Field;moment(@Created On).fromNow();Of course, I would not suggest this in a production environment unless you can somehow rely completely on a certain field being updated with higher frequency than the units in which you wish to calculate age, in this case "day."
There are more advanced solutions, which are more reliable, but they are all work-around approaches, as Podio does not natively support the updating of a calculated field without its referenced fields being updated, or its calculation formula being changed.
-
There are 2 other workarounds to force the calculation to update daily to the real current day. For the first, more comfortable workaround you need the Podio extension GlobiFlow https://podio.com/extensions/2
For the second workaround you could create an app "Date control" with only one item "Current date" with only one field. That should be a date field, which you update manually every day to the current day. Via a relation field in the app where you calculation field is in, you create a relation in every item to the item "Current date" (if you have a Podio Plus account, Podio Workflows can set the relation automatically for you whenever a new item is created). When you now change the date in the item "Current Date" the number of "days old" will be updated. For that way you need the formula:
var current = moment(@max of DatefieldInAppDateControl); var start = moment(@YourDateField); current.diff(start, 'days');@Sevan:
For "current date" I would prefer moment() instead of .fromNow(), cause there is an important difference:
.fromNow() delivers the result as a text string, .diff delivers a number. If you need the result for further calcutions, it's better to have a number.
Another difference: With .diff you can define more comfortable in which unit the result should be shown, If you use .fromNow it sets the unit automatically (or you have to write some more code) and it can happen that you den't get "days", but "weeks" or "months".Rainer
rg@delos-consulting.com -
@Rainer
Love your solution, but when it gets confusing when it rounds it off to 1 days instead of 1.7 days closer to the date. Which I can get that value if I pass true e.g.
var current = moment(@max of DatefieldInAppDateControl); var start = moment(@YourDateField); current.diff(start, 'days', true);However it will come out as 1.765555555555555555555,
Would you know of way to make it get down to 1 day and 20 hours when it is less than 2 days?
Update: For now I've got GobiFlow to set yesterdays date instead of today so it rounds down correctly.
-
Hey Rainer,
Works well, but I can't get it to work with negative numbers. I was looking on here http://stackoverflow.com/questions/4228356/integer-division-in-javascript, but couldn't really decipher it.
-
moment(@Created On).fromNow(); worked perfectly, but is there a way to stop the calculation once the ticket has reached a certain stage? for instance we want to know the duration of a ticket until the stage is set to "Sold" so once the dropdownlist option of "Sold" is triggered, we would want the calculation to stop
-
Hi Richard,
moment(@Created On).fromNow() doesn't work if you want to show a daily updated value (more about this see here ). Example:
Created on = Oct 15, 2016. If moment(@Created On).fromNow() is the only code in your calculation field it will show: a few seconds ago. One hour later: a few seconds ago. 2 days later: a few seconds ago.You need another variable in the code which updates the calculation, like your category fied stage. Every time you select another stage it changes the time value, but between 2 selection updates the time value will be the same every day.
If "sold" isn't the last stage and the time count should stop at "sold" you need an additional date field e.g "Sold Date" which must be empty till "Sold" is selected. Then create a Podio Workflow:When item updated
and field Stage changed
and future Stage contains "Sold"
update Item here
"Sold date" = current time (now)Then create a calculation field:
var stage = @stage;
var soldField = @sold date;
var soldDate = moment(soldField);
var cOn = moment(@created on);
var unit = "days";
soldField == null && stage != null ? moment().diff(cOn,unit) : soldField != null && stage != null ? SoldDate.diff(cOn, unit) : ""I prefer .diff() instead of .fromNow() cause .from() shows the relative difference. Means: If the difference is 25 days it will show "a month ago" - but it will also show "a month ago" if the difference is 45 days.
Rainer
-
No, can't confirm. Cause daily update isn't possible (only with workarounds). In this forum are several threads discussing this problem. E.g. here https://help.podio.com/hc/en-us/community/posts/204569657-Definite-solution-for-today-handling-in-calculated-fields-days-since-days-until-countdown-incl-possible-solution
-
Rainer,
that's a bummer. I don't mind the idea of having a "Today's Date" app that updates daily to reflect today's date but how do you reference this back to the parent app? I created a new app, and added a date field to the template, but there was no way to reference it in the calculation field using the @ symbol.
-
You need a relationship field in the Parent App which points to the Today-App. Depending on how many items there are in the Parent App you can use Gflobiflow or an Excel-import to connect all exitisting Parent items with the today item. Then create one flow which automatically connects each new Parent-item with the Date-item.
I recommend this structure: Most of my clients have an Employee-App and a contacts app. Many other apps are related to the emloyee app or the contacts app. If you connect the employee app and/or the contact app with the time app you can create a calculation field in Employees or Contacts which shows the date (@max of date field). This allows to get the current date in each other app which is connected to Employees or Contacts.Sometimes I go a step further: I have one central Report-App with one item only where all items from many apps in many workspaces are related to. This way I can create reports ans analysis across dozens of apps - and this central report item has a Today's date field too, so all related apps have access to today's date.
Rainer
rg@delos-consulting.com
Please sign in to leave a comment.
Comments
15 comments