Help in calculating date difference for negative value

[Dilip_Adiga]

Hi Guys,

I am calculating wether task is over due, Days left and Today

I am using Math column or date difference to get the difference between Due data and Now

When Due Date - Now is +1 ill show 1 Day Overdue

But when Due Date - Now is -1 in some cases when due date is smaller than Now I should show 1 day Left

and when its 0 it should show today.

My doubt is how do I remove the - value from difference I get.

How do I do It?

Regards,
Dilip

[Darren_Murphy]

Multiply it by minus one.

[Dilip_Adiga]

I mean I can have mix of + and - right?

If I multiple +1 by -1 then it becomes negative.

Will I have to use if else if?

if I use if else of how can then condition take a math to do calculation?

as per logic you just said ill have to check if it is - and then only multiple it by -1

This is screenshot

Regards,
Dilip

[nathanaelb]

@Dilip_Adiga

I am using Math column or date difference to get the difference between Due data and Now

Dates are finicky. I think I would use a math column instead of date difference column. I wonder if you could wrap the difference in a ABS() function, that would then always return a positive value I think. Then you could use the pluralize text column to automatically pluralize the unit, in your case day/days. Finally, I would play around with an if-then-else and template columns to make it nice for the display editor. That’s a lot of columns for not much, but that’s how I would approach it.

[Dilip_Adiga]

I tried your approach with ABS()

it worked.

Now I tried IF Else IF condition

my condition is as follows

if date is lesser than 0 then Days Overdue

if Date is equals 0 then Today

Else Late

The second condition equals 0 is never getting executed though its 0

Please find screenshot attached.

Am I doing anything wrong

Please find column with name Date Difference

Thank you in advance

Regards,
Dilip

[Jeff_Hager]

You need to ROUND() the number.

[nathanaelb]

I just set this up and I changed my mind, I prefer using the Date Difference column.

Here is the process I used:

• Date Difference column to get a number. I prefer a number to the HH:MM:SS output of the math column.
• round() function to round the number to an integer.
• abs() function to make everything positive (distance to zero).
• Pluralize text column to add the units.
• One “Days left” template column, one “Days overdue” template column.
• An if-then-else column to determine if the layout is to display the “Days left” column, “Days overdue” column or "Today. This is based on “Date difference rounded” being negative, positive or zero.
• The same if-then-else column that this time returns “Overdue”, “Today” or “On time” that will be used for collection grouping.

A lot of columns for not much, maybe someone will come up with something more concise. At least this approach works.

---

Final layout

Data Editor

(continued)

[ThinhDinh]

You won’t have the same amount of control over the template that is shown to the user, but all of this can be easier with a single relative time column.

[Dilip_Adiga]

Thank you for the solution Nathan. Yeah it’s a lot of things for such a small requirement. DO you think it would impact performance in any way?

[Dilip_Adiga]

Thank you for the reply, How do you propose I do it?

Regards,
Dilip

[nathanaelb]

In the grand scheme of things this is not a lot of columns, it’s just a little cumbersome to set up. I don’t think this would affect the performance of the application at all.

[nathanaelb]

In the data editor, add a ‘relative time’ column. This column is simpler and might just be exactly what you need.

[Dilip_Adiga]

Thank you Nathan, for this. I guess this reduces lot of complexity. I’ll try it and get back to you.

Regards,
Dilip

[charlydefilippi]

Try putting the condition " if date difference equals to zero then Today" first than the other condition that you made (“datedifference lower than 0”).