Date calculations in automation return wrong values

Dzanan Gvozden_jz3zop

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_jz3zop

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 pieces 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:

@{{/}}

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

@, \n")}}

 

For future vacations, I'm sending the following:

@{{/}}

@ 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: