Calculating dates from a start date

[sian hamer]

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

[Courtney Langdon]

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!

[Andrey Khaneev _StiltSoft_]

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

1. The source table 
2. Adding the Table Transformer macro 
3. Add this query 

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

4. Ensure that there are 24 hours in day are set 5. Get the result The calculated date can be formatted in different ways if needed.

[Andrey Khaneev _StiltSoft_]

Something wrong with two first screenshots. Here they are

[erzn]

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:

[Andrey Khaneev _StiltSoft_]

Hi @erzn,

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

[erzn]

Hi @Andrey Khaneev _StiltSoft_ thanks for your quick response!

I still get invalid Date as a result:

[Andrey Khaneev _StiltSoft_]

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]

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_]

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

[Etienne Daloze]

@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_]

Hi @Etienne Daloze,

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



You may use the following solution for the case:



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*)



Hope it helps.