How to calculate the difference between two dates

[Dean Smith]

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}

[CelesteCS]

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!