Date calculations in automation return wrong values

[Dzanan Gvozden]

So I have an automation where I report on number of days people are out of office.

Every day I send a message to slack saying something like this:

John Smith is on vacation from Thu, May 9 until Wed, May 15, lasting X days.

In my automation I look for vacation entries:

project = OOO and type = Sub-task and ("Start date[Date]" >= startOfDay(1) AND "Start date[Date]" <= endOfWeek(1)) ORDER BY updated DESC

Then I iterate through the values in a variable:

{{#lookupIssues}}>:dnd: *{{assignee.displayName}}* from *{{Start Date.format("EEE, MMM d")}}* until *{{End Date.format("EEE, MMM d")}}*, lasting {{#if (equals(Start Date, End Date))}}
1d {{/}} {{#if (not(equals(Start Date, End Date)))}} *{{End Date.plusDays(1).diff(Start Date).days.abs}}* days.{{/}}{{/}}

 The problem is the value for the number of days the vacation will last is absolutely wrong. It look like Jira is dishing out completely wrong numbers. For example:

Any help is greatly appreciated.

[Dzanan Gvozden · Accepted solution]

I managed to solve the issue above, and I'll explain the solution here as it might be useful to someone else, facing the same challenge.

Basically, I'm extracting two piece of information from our Our of Office project:

1. Who is on vacation today?

   1. project = OOO AND type=Sub-task AND "Start date" <= startOfDay() AND "End date" >= endOfDay() ORDER BY updated DESC

2. Who is on vacation in the upcoming days & next week?

   1. project = OOO and type = Sub-task and ("Start date[Date]" >= startOfDay(1) AND "Start date[Date]" <= endOfWeek(1)) ORDER BY updated DESC

For today, I'm sending the following to Slack:

{{#lookupIssues}}>:dnd: *{{assignee.displayName}}* from *{{Start Date.format("EEE, MMM d")}}* until *{{End Date.format("EEE, MMM d")}}*, and *{{End Date.plusDays(1).diff(now).days.abs}}* days remaining. @@{{/}}

Please note @@ are used to enforce new line and this field is followed by:

{{vacations.replaceAll("@@", "\n")}}

For future vacations, I'm sending the following:

{{#lookupIssues}}>:dnd: *{{assignee.displayName}}* from *{{Start Date.format("EEE, MMM d")}}* until *{{End Date.format("EEE, MMM d")}}*, lasting *{{#=}}ABS({{End Date.plusDays(1).diff(now).days}}-{{Start Date.diff(now).days}}){{/}}* days. @@{{/}}

The @@ are used for the same purpose as above.

What I'm doing here is using a math expression do find the difference between now and end date and now and start date, as using now doesn't have the same random results as explained in the original post.

Now, this works, because future vacations will only look for entries where the start date is higher then today, so we'll never encounter a problem where now is later than the start date (these entries are being sent under point number 1 (who's on vacation today)).

I know this might be confusing for some users and I'd be happy to answer any questions you might have.

Below are the screenshots of the automation: