How to calculate the difference between two dates

Hi all, I have a really big favour to ask... Im stuck with math nor do i have a clue on how to correctly express it.

Im also bit of a gumby (new) to Atlassian Wiki markup. Any advice or providing code to achieve what I need is greatly appreciated in advance.

I have a Main Report page (see Main Report Page code below) that has a data entry field for an Incident number and when the 'click to record a easyclaim outage' button is selected, a second page is rendered from a template (see TEMPLATE code below). The second page is a form that includes Start and End Dates/Time fields plus others that are manually entered by the user. Once the record is saved (as a new child page), the first page 'Main Report page' also then displays a filter of all Incident records (new child pages) ever created displaying the record data previously entered by user from created records (child pages).

What I actually require is for the template field 'Outage Duration' to auto calculate the number of days, hours, minutes between the 'Outage Start Date' , 'Outage Start Time' fields and current date/time until the 'Outage End Date/Time fields are popluated.

Main Report Page

{section}
{column:width=50%}
{panel}

h6. Please note: It is \*\*{color:#ff0000}Mandatory{color}\*\* that a system availability record is logged each day before 10am.


h6. KEY: Enter the '{color:#008000}Incident Number{color}' for the Easyclaim Outage or if no outage occured enter the word {color:#000000}'{color}{color:#008000}Available{color}' below.


h6. \*Please enter date(s) applicable on the following page presented.
{add-page-form:template=EasyclaimTemp|parent=@self|live=true|labels=easyclaimoutages|nameWidth=40%}
Click to record a Easyclaim outage
{add-page-form}
{panel}
{column}
{column:width=50%}
{column}
{section}
{table-filter:userfilter=Date of Record, Incident No, Outage Start Date, Outage Start Time, Outage End Date, Outage End Time, Outage Duration, Outage Type, Environment, Description, Resolution|button=true}
{table-plus:columnTypes=dd MMM yyyy,S,dd MMM yyyy,HH:mm,dd MMM yyyy,HH:mm,I,S,S,S,S}
{metadata-report:Creator,Page,Date of Record,Incident No,Outage Start Date,Outage Start Time,Outage End Date,Outage End Time,Outage Duration,Outage Type,Environment,Description,Resolution}
{table-plus}
{table-filter}{html}<a href="#main">Back to Top</a>
{html}


TEMPLATE


||[Return to Easyclaim Availability and Outage|Easyclaim Availability and Outage]||
{tip:title=Legend}

||{fontsize:size=1}Date of Record{fontsize}|{fontsize:size=1}Enter date of this record entry (usually todays date).{fontsize}|
||{fontsize:size=1}Incident No{fontsize}|{fontsize:size=1}Auto-populated{fontsize}|
||{fontsize:size=1}Outage Start Date{fontsize}|{fontsize:size=1}Enter date the outage was initially identified.{fontsize}|
||{fontsize:size=1}Outage Start Time{fontsize}|{fontsize:size=1}Enter time the outage was initially identified.{fontsize}|
||{fontsize:size=1}Outage End Date{fontsize}|{fontsize:size=1}Enter date the outage was resolved.{fontsize}|
||{fontsize:size=1}Outage End Time{fontsize}|{fontsize:size=1}Enter time the outage was resolved.{fontsize}|
||{fontsize:size=1}Outage Duration{fontsize}|{fontsize:size=1}Auto-calculated.{fontsize}|
||{fontsize:size=1}Outage Type{fontsize}|{fontsize:size=1}Define the Outage Type.{fontsize}||
||{fontsize:size=1}Environment{fontsize}|{fontsize:size=1}Define the IT environment.{fontsize}||
||{fontsize:size=1}Description{fontsize}|{fontsize:size=1}Describe the symtom and possible cause(s).{fontsize}||
||{fontsize:size=1}Resolution{fontsize}|{fontsize:size=1}Explanation of cause and what was required to resolve issue.{fontsize}||
{tip}
||[Return to Easyclaim Availability and Outage|Easyclaim Availability and Outage]||
{tip:title=Record Availability and Outage detail here:}

{metadata-list}
|Date of Record| {date-data:name=list field1|format=dd-MMM-yyyy} {date-data} |
|Incident No| {report-info:page:title} |
|Outage Start Date| {date-data:name=list field1|format=dd-MMM-yyyy} {date-data} |
|Outage Start Time| {date-data:name=list field2|format=HH:mm}{date-data} |
|Outage End Date| {date-data:name=list field3|format=dd-MMM-yyyy} {date-data} |
|Outage End Time| {date-data:name=list field4|format=HH:mm}{date-data}|
|Outage Duration| {eval-data:outage duration|format=#000}${Outage End Date}-${Outage Start Date}{eval-data}|


||Outage Type| {list-data:name=outage type|type=select|multiple=false}
{list-option}Planned{list-option}
{list-option}Unplanned{list-option}
{list-data} ||
||Environment| {list-data:name=environment|type=select|multiple=false}
{list-option}Production{list-option} {list-option}Vendor{list-option}
{list-option}Test{list-option}
{list-option}Dev{list-option}
{list-data} ||
||Description|{text-data:name=description|type=area|width=600px} {text-data}||
||Resolution|{text-data:name=resolution|type=area|width=600px} {text-data}||
{metadata-list}
\|\|Reserved for Future Use\|.......................................................................................\|
{tip}
{attachments}


1 answer

Hello!

For any calculations in Confluence, including calculations over tables, you may use CelesteCS Math for Confluence. There are versions for both Confluence Server and Confluence Cloud.

Assume you have a table with three rows and four columns: A till D with two rows and want to summarize elements from B2 to D2. Then here is the formula you should specify in A2:

SUM(Table1.B2:D2)

If you want to calculate average value, use AVG function:

AVG(Table1.B2:D2)

As you see, it is very simple and is similar to EXCEL with equal set of functions.

There is a plenty of other functions in the macro, so you may perform any calculations using formulas in EXCEL style.

Thanks!

Suggest an answer

Log in or Sign up to answer
How to earn badges on the Atlassian Community

How to earn badges on the Atlassian Community

Badges are a great way to show off community activity, whether you’re a newbie or a Champion.

Learn more
Community showcase
Posted Tuesday in Confluence

We want to see the templates you've created in Confluence!

Hi Community, Jessica here from the Confluence Product Marketing team!  July’s community challenge is all about sharing pictures  — and as an extension of our first post on what ...

384 views 17 9
Join discussion

Atlassian User Groups

Connect with like-minded Atlassian users at free events near you!

Find a group

Connect with like-minded Atlassian users at free events near you!

Find my local user group

Unfortunately there are no AUG chapters near you at the moment.

Start an AUG

You're one step closer to meeting fellow Atlassian users at your local meet up. Learn more about AUGs

Groups near you