Integer to Date/Time & Military Time

Trouble viewing images? Click on images to enlarge.

In this article we cover a few methods to get a time value out of date/time data stored as an integer. If you would like to download the sample view used in the article, click here. 

The methods below are going to take data that is stored as an integer for seconds.

Example: 600 = 10 minutes or 3600 = 1 Hour

Finished Reports Method:

1. Add a label into Finished Reports.

2. Click the Label Tasks drop down. (Select the label and click the blue arrow)

3. Open the expression editor and paste the following expression.

FormatString('{0:00}:{1:00}:{2:00}', Floor(ToDecimal([Column_0])/3600), Floor((ToDecimal([Column_0])-3600*Floor(ToDecimal([Column_0])/(3600)))/(60)), ToDecimal([Column_0])%60)

4. Replace all instances of [YourField] with the field you want to use then click OK.

5. Preview the Finished Report.

Alternate Finished Reports Method (Recommended):

1. Create a new calculated field. (Right click in Field List and select Add Calculated Field)

2. Set the Data Source, and Field Type.

3. Open the expression editor and paste the following expression.

addseconds('1/1/1900', [YourField])

4. Replace [YourField] in the expression with the field you are using.

5. Drag the field into FR and format the expression to {0:HH:mm:ss}

Military Time

1. Create a new calculated field. (Right click in Field List and select Add Calculated Field)

2. Set the Data Source, and Field Type.

3. Open the expression editor and paste the following expression.

addhours('1/1/1900', [YourField]) - use your integer field for hours

4. Replace [YourField] in the expression with the field you are using.

5. Drag the field into FR and format the expression to {0:hh:mm tt}

Visualize Method:

1. Create A Calculated Field

2. Paste the following expression into the expression editor.

Iif(
ToInt([YourField] % (24 * 60 * 60) / (60 * 60)) < 10, '0' +
ToStr([YourField] % (24 * 60 * 60) / (60 * 60)),
ToStr([YourField] % (24 * 60 * 60) / (60 * 60)))
+ ':' +
Iif(
ToInt([YourField] % (60 * 60) / 60) < 10, '0' +
ToStr([YourField] % (60 * 60) / 60), ToStr([YourField] % (60 * 60) / 60))
+ ':' +
Iif(
ToInt([YourField] % 60) < 10, '0' + ToStr([YourField] % 60) , ToStr([YourField] % 60))

3. Replace [YourField] with the field you are using.

4. Drag the field into your Visualize fields and preview the results.