nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Table Transformer Calculations Off By 1

Adrian Avalos_ywpjg0

Hello. I setup the below formula in Table Transformer to calculate the difference between two date fields. It works for the most part, but for some reason the top piece of the formula in bold outputs a result of one additional day for some results? Would someone be able to help modify the formula in table transformer so the calculations are correct?

CASE
WHEN T1.'Due Date' IS NOT NULL AND DATEDIFF(day,T1.'Resolved',T1.'Due Date') < -1
THEN FORMATWIKI("{cell:align=center; textColor=#cc1010}" + ROUND(DATEDIFF(day,T1.'Resolved',T1.'Due Date')) + "{cell}")
WHEN T1.'Due Date' IS NOT NULL AND DATEDIFF(day,T1.'Resolved',T1.'Due') > -1
THEN FORMATWIKI("{cell:align=center; textColor=#008000}" + ROUND(DATEDIFF(day,T1.'Resolved',T1.'Due Date')) + "{cell}")
END AS 'Time Between Due and Resolved'

Accepted answers

Stiltsoft support

Hi @Adrian Avalos ,

For which dates the formula is not correct? The DATEDIFF function is a standard SQL function that we don't somehow specifically adjust.

All comments

Stiltsoft support

Hi @Adrian Avalos ,

For which dates the formula is not correct? The DATEDIFF function is a standard SQL function that we don't somehow specifically adjust.

Stiltsoft support

Hi @Adrian Avalos ,

Got an idea from our developers: the DATEDIFF function may be influenced by the winter time/summer time transition. There could be a day that is 23 hours long. You may google this peculiarity regarding this SQL function.

So, you may try to use the ROUND() function before each DATEDIFF function in the query.

Adrian Avalos_ywpjg0

Hi @Stiltsoft support thank you for looking at this. Do you know how I can edit this piece to account for the 23 hour long day? I included the ROUND function but am still not getting the correct date difference.

ROUND(DATEDIFF(day,T1.'Resolved',T1.'Due Date'))

Stiltsoft support

You may try to use some workarounds: for example, check if your date period includes the day with the transition and manually correct the result difference (add or subtract 1 day from the result).

You may also create a table with test dates that are close and include your transition day (10, March? 31, March? I guess it differs for different countries), calculate the DATEDIFF using an hour interval, see what you get, correct it if required (+ or - 1 hour) and then divide by 24 hours to get days.

Welcome!

It looks like you're new here. Sign in or register to get started.

Quick Links

Find more posts tagged with

confluence

cloud

confluence-cloud

filter

table

table-filter

table-filter-and-charts

table-transformer

table-filter-charts-spreadsheets