Calculating dates from a start date

sian hamer November 1, 2018

I currently create a confluence page per software release by copying the previous release page. The start / end dates for each task is essentially a start date + n days.

Is there any way to calculate this automatically? ie setting the start date and the page calculates all the following dates?

I am not particularly technical, so if anyone knows a simple solution it would be great.

Thanks

 

2 answers

1 vote
Andrey Khaneev _StiltSoft_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 16, 2019

This can be done with Table Transformer macro (Table Filter and Charts app). Here is example:

  1. The source table 2019-09-16_10h08_28.png
  2. Adding the Table Transformer macro 2019-09-16_10h09_41.png
  3. Add this query 2019-09-16_10h14_58.png

 

SELECT *, 
(T1.'Start Date' + "5d")::Date->toLocaleDateString() AS 'End Date'
FROM T1

4. Ensure that there are 24 hours in day are set 2019-09-16_10h17_48.png5. Get the result 2019-09-16_10h21_12.pngThe calculated date can be formatted in different ways if needed.

Andrey Khaneev _StiltSoft_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
September 16, 2019

Something wrong with two first screenshots. Here they are

2019-09-16_10h08_28.png2019-09-16_10h09_41.png

erzn April 22, 2020

Hi @Andrey Khaneev _StiltSoft_ ,

 

is it possible to take time format as like: MMMM d, yyyy as Start Date?

I used the correct pattern in "Settings" as i got the pattern.

 

My Data:

Anmerkung 2020-04-22 145101.png

Andrey Khaneev _StiltSoft_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 22, 2020

Hi @erzn,

Please, set the date format to M dd, yy in the macro settings (for dates like Apr 16, 2020).

Like erzn likes this
erzn April 22, 2020

Hi @Andrey Khaneev _StiltSoft_ thanks for your quick response!

I still get invalid Date as a result:

Anmerkung 2020-04-22 150641.pngAnmerkung 2020-04-22 150815.png

Andrey Khaneev _StiltSoft_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 22, 2020

Strange, I tried to reproduce the issue, but it works in both English and German languages. Maybe you are using an outdated version of the Table Filter and Charts app. Could you try to update it?

erzn April 22, 2020

Unfortunately not.

Maybe it has something to do with the fact that the source data comes in from the Jira macro? The data is pulled in regularly via the Jira macro and the table is then included within the transformer app.

Andrey Khaneev _StiltSoft_
Rising Star
Rising Star
Rising Stars are recognized for providing high-quality answers to other users. Rising Stars receive a certificate of achievement and are on the path to becoming Community Leaders.
April 22, 2020

Please, contact StiltSoft support, it will be easier to resolve the issue there.

Like erzn likes this
Etienne Daloze February 27, 2023

@Andrey Khaneev _StiltSoft_ , thanks for sharing your knowledge.

I have something similar:

  • I have a report on the state of the pages of my space (via Document States Report macro)
  • Most of the pages have a Comala workflow with a validity period. This validity period is defined via a page property (called Validity)  following ISO8601 P3M style
  • I would like to add to the report 2 columns :
    • stale date (=updated + Validity)
    • remaining days till stale date = stale date - today

Following what you share here, I've tried to add 3 months --> 

SELECT *, 
(T1.'Updated' + "3M")::Date->toLocaleDateString() AS 'stale date'
FROM T1

I think that I may calculate the "3M" value by eliminating the P at the beginning of the Validity page property - if I can get that value; that's another story.

If I add "5d" like in your example, I get something. If I add "3m", it adds 3 minutes.

But if I had "3M", I get the "Invalid Date" result. How do I add months to a date?

Thanks for your help.

Yours, 

 

Etienne

Katerina Rudkovskaya _Stiltsoft_
Marketplace Partner
Marketplace Partners provide apps and integrations available on the Atlassian Marketplace that extend the power of Atlassian products.
February 28, 2023

Hi @Etienne Daloze,

If I understand your question right, you have smth like this in your report:

Tue 18-1.png

You may use the following solution for the case:

Tue 18-2.png

SELECT *,
DATEDIFF(day, "today", 'Stale Date') AS 'Remaining days till stale date'
FROM (SELECT *,
FORMATDATE(DATEADD(month, (SUBSTRING('Validity', 2, 1)::number), 'Updated')) AS 'Stale Date'
FROM T*)

Tue 18-3.png

Hope it helps.

Like # people like this
1 vote
Courtney Langdon September 11, 2019

I would like this ability, too. I'm trying to convince a group of users to utilize Confluence instead of Excel for their project information, but not being able to calculate future dates based on a start date in Confluence (especially when Excel can) is a deal breaker for them. :(

Any work-arounds? Potential future release?

Thanks!

Suggest an answer

Log in or Sign up to answer
TAGS
AUG Leaders

Atlassian Community Events