Excel Export - minutes as number not time

AmyDK

I need to export duration as minutes but in Excel this comes out as "00 min" with a custom cell format that reads as a time (00:00:00). Changing the format to number does not work.

If I export to CSV it still comes out with the "min" suffix but obviously no hidden cell format but I have to delete the "min" to get just the number.

Here's how it looks in CSV. I need just the number, not " min":

Could there be a time span option to export the number of minutes just as a number? No "min" suffix, no time format.

Here's my export settings;

Thanks!

WorkingHours version: 2.8.0.0
Operating system: Win10 Version 21H2 (19044.1466)

timo

Using the "633 min" format the value is written as number + a format. I am surprised that you state that it is not a format, but a string. I cannot reproduce this.
You can also change the cell format directly in Excel

AmyDK

Hi Timo thanks for your reply! Believe me I've tried all the formatting options in Excel to get rid of the "min" label but no joy! Hope you like screenshots...

In Excel the format defaults to Custom and if you inspect a cell it is in the time format 00:00:00:

Which is using a custom format:

If you change the format to Number you get this:

General gets this:

If you change the custom format to remove the "min" it visually looks ok but it is of course still just minutes in a time format, not a number:

And then you can't sum on the cells, which is what I need to do. You can see these two cells look like straight numbers:

But a sum on them is of time:

For this example I need a total of 134, not 2 hrs 14 mins.

About the only thing I can find to do is export to CSV then use text-to-columns to remove the "min" from the Duration column. So my original request was whether there can be an additional export option so I can avoid having to do this.

Thanks!
Amy

timo

Maybe this additional explanation helps you in achieving what you try:
In order to format the "duration" correctly in Excel, I need to export the value as fraction of a day. That means, 1 hour is written in the Excel cell as 1/24th (which is 0.04166666666).

What you could do is format the value as "time" - the "min" will be gone, but 1 hour will not show as "1" but as "1:00"

You could also create a separate column that multiplies the "duration" column by 24 - then you will have the "hourly" value in that column. Or you multiply by 24*60=1440 to have the minute value in that column.

If you don't necessarily need the minutes, but the hours, then use this export format which will not export the "fractions of a day" but the hourly value as-is:

I understand that this is a hacky workaround as well and I will keep this feature request open for discussion in case other users have the same request. If there's enough demand for it, I will consider adding another formatting option.

AmyDK

Thanks Timo! I don't understand how you are exporting the duration as fractions of a day - are you exporting as time then formatting to Number in Excel then multiplying by 24?

I think TBH I will just carry on with CSV and text to columns for now, seems easiest and quickest.