Custom Format Strings for Conversion into Hours, Minutes & Seconds

Follow the below mentioned steps for custom formatting.

  1. Select Column Properties
  2. Then in  the Data Format Tab, select Treat Numbers as Custom. Then write the data Conversion Format String.

Note: For the below mentioned format to work correctly during export to excel, you should be in latest patch 11.1.1.7.150120 ( Patch 20124371 )

 
Data Conversion Format String
Result
[duration(sec)][opt:dd]:hh:mm:ss
Formats the total of seconds as duration. For example, a duration value of 16500.00 is displayed as 04:35:00, meaning a duration of four hours, 35 minutes, and zero seconds.
opt:dd displays the number of days, but if opt has no value, it is not displayed.
Second is the default unit of time.
[duration(min)][opt:dd]:hh:mm:ss
Formats the total of minutes as duration. For example, a duration value of 275.00 is displayed as 04:35:00, meaning a duration of four hours, 35 minutes, and zero seconds.
opt:dd displays the number of days, but if opt has no value, it is not displayed.
[duration(hour)][opt:dd]:hh:mm:ss
Formats the total of hours as duration. For example, a duration value of 4.58 is displayed as 04:35:00, meaning a duration of four hours, 34 minutes, and 48 seconds.
opt:dd displays the number of days, but if opt has no value, it is not displayed.
[duration(day)][opt:dd]:hh:mm:ss
Formats the total of days as duration. For example, a duration value of 2.13 is displayed as 02:03:07:12, meaning a duration of two days, three hours, seven minutes, and twelve seconds.
opt:dd displays the number of days, but if opt has no value, it is not displayed.

2 comments:

  1. Thank you so much for this !!! I have been looking on how to do this, works great !

    ReplyDelete
  2. when I convert minutes into DD:HH:MM format, by default -1:-1:0 is adding. For example, -30 minutes = 00:00:-30 or -00:00:30 but it is showing as -01:-01:30. Any idea?

    ReplyDelete