How to display number of days for Total Time in Status in Table Transformer

[Adrian Avalos]

Hello. I added Total Time in Status field to Table Transformer, but it displays issues in format 2w 1 d 2h 33m. I'm looking to display this instead as number of days (rounded to nearest day)  Can someone provide a script to do this conversion or provide an alternative?

[Stiltsoft support · Accepted solution]

Hi @Adrian Avalos ,

You may try the following workaround:

Now you set your date format (for the FORMATDATE function) and your worklog settings (for the FORMATWORKLOG function) as following:

Then you may use the following query:

SELECT *,
FORMATDATE(T1.'Time') AS 'Days 1',
FORMATWORKLOG(T1.'Time') AS 'Days 2'
FROM T*

The more complicated query

SELECT *,
ROUND((FORMATWORKLOG(T1.'Time')->split("d")->0)::number) AS 'Days 3',
CEILING((FORMATWORKLOG(T1.'Time')->split("d")->0)::number) AS 'Days 4'
FROM T*

can round the number of days mathematically ('Days 3') and round the number of days to the next day ('Days 4' that is pretty similar to 'Days 1' with the FORMATDATE function):

So, you may "play" with the queries and choose what suits you more. Hope we were able to help. 

[Adrian Avalos]

@Stiltsoft support thanks so much for your help! I went with the more complicated query and it worked