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?
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
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.
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.
Hi Bruno
And the AutoSum works fine? I will try it.
Thanks a lot
It should work. I have tested that as well.