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.

5 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
    Replies
    1. I'm facing the same issue. any update on this?

      Delete
  3. Yay! So glad you have this here. It is exactly what I needed. Thanks!

    ReplyDelete
  4. I'm Абрам Александр a businessman who was able to revive his dying lumbering business through the help of a God sent lender known as Benjamin Lee the Loan Consultant. Am resident at Yekaterinburg Екатеринбург. Well are you trying to start a business, settle your debt, expand your existing one, need money to purchase supplies. Have you been having problem trying to secure a Good Credit Facility, I want you to know that Mr Benjamin will see you through. Is the right place for you to resolve all your financial problem because am a living testimony and i can't just keep this to myself when others are looking for a way to be financially lifted.. I want you all to contact this God sent lender using the details as stated in other to be a partaker of this great opportunity Email: 247officedept@gmail.com  Or  WhatsApp/Text +1-989-394-3740.

    ReplyDelete