[OfficeUtils] [OfficeUtils] How to format duration hh:mm

Forge Component
(28)
Published on 2 Apr (15 hours ago) by Bruno Gonçalves
28 votes
Published on 2 Apr (15 hours ago) by Bruno Gonçalves

Hello all

I am trying to export to excel durations in the format hh:mm, and then I want to sum those durations.

 I have changed my excel template cells to the custom format [hh]:mm so that it is possible to sum the values, the problem is that those cells are inserted as TEXT, so to assume the base format a will need to update all cells (F2 enter). I have tried also to inset them as time, but this is not time is a duration so the sum doesn't work as well.

Any idea how can I achieve this? Is it possible to insert a cell as a custom format ([hh]:mm) in OfficeUtils?


Solution

Hi Rita,

The problem you are facing is not related with the custom format definition, but with the way you are setting the value.

Instead of providing the value in the "hh:mm" form, you should provide it as a decimal (using "Excel_Export_SetCellDecimal") where 1.0 represents 24 hours. For example, the duration "36:30" has the 1.520833333 decimal equivalent, which you can obtain by dividing the [duration in minutes] by the [number of minutes in a day].

Bruno

Solution

Hi Bruno,

Thanks for your reply but If I do that I will have decimal values (1.520833333) in my Excel file instead of the format 36:30, since this is for a client they want something already formatted and ready to use. So I think that that won't solve my problem.


Hi Rita,

If you set the cell format as "[hh]:mm" on the template, and then use "Excel_Export_SetCellDecimal" to write "1.520833333", you will get "36:30" displayed on the exported excel. 

I have tested this already and it worked for me.

Bruno

Hi Bruno 

And the AutoSum works fine? I will try it. 


Thanks a lot


It should work. I have tested that as well.

Bruno