Excel Export Problem

boris

working hour version 2.11.23
Mac book pro M1
sequoia 15.2

When Exporting from Working hours to Excel, the column with the duration of the work is suddenly formatted as text so that I cannot add sums and work with the numbers. that never happened before., What can be the problem? D I do something wrong? That appeared for the first time after I updated to Sequoia.

Thank you for the wonderful app

timo

This has been an intentional change, it was announced in the Changelog and explained here.
You can use the option to split hours and minutes into separate columns to work with the numbers, or doesn't that work?

boris

Hi Timo. Thank you very much for the fast reply. Unfortunately the option with the 2 columns doesn't really work for me, because it then just adds the minutes and hours together separately without changing after 60 minutes into an additional hour.... I am using your app for my billing as a lawyer. Each "Task" is for a separate client and I use the "Details" Section to list what exactly I have done during the overall amount of time on that day. That however means, if I export the data for one month, I need to separate each Task and sum up the hours for that task in that month and I do that in Excel. Do you have any better idea how I could handle this? I would be VERY thankful for any tip and advice.

timo

boris Unfortunately the option with the 2 columns doesn't really work for me, because it then just adds the minutes and hours together separately without changing after 60 minutes into an additional hour

Can't you use formulas to e.g. divide the minutes by 60 in order to get the total hours...?

boris Each "Task" is for a separate client and I use the "Details" Section to list what exactly I have done during the overall amount of time on that day. That however means, if I export the data for one month, I need to separate each Task and sum up the hours for that task in that month and I do that in Excel. Do you have any better idea how I could handle this? I would be VERY thankful for any tip and advice

This is unrelated to your first question, right? I'm not sure if I understood correctly, but if you just want to export for one single client, you can simply filter by that task before exporting - or why doesn't that work?

jenbooks

Yeah, I have the same problem with exporting to excel - workflow is trashed. Any possibility to make export as text an option instead of the only way to export?

timo

jenbooks I don't want to add additional options to the export if possible - it makes the export UI more complex and I know already, that many users are overwhelmed by its options.
Can you please elaborate what kind of workflow you had and why the "hour and minute in separate columns" suggestion doesn't work for you?

susanne123

Hi Timo,

for me this is also tedious. Your workaround works, but now I have to create a third column and fourth column next to column 1 for the hours and column 2 for the minutes. Then, in column 3 I have to divide the value of column 2 by 60. In column 4 I have to add the values of column 1 and column 3. This now gives me the value I used to have before the update. Then I have to copy the two cells of column 3 and 4 and paste them to sometimes hundreds of lines below. Then I have to hide column 1 to 3, so that only column 4 is visible. This takes me a minute in total and every time I do an export. All this just to get what I used to have and to be able to treat numbers as numbers.

Please make it a choice to export the numbers either as numbers or as text. It's crazy to force the majority of users to use a workaround so that the minority of users can enjoy what the majority used to have.

And exporting numbers as numbers should be the default. The minority of users who are using the app Numbers and want to also do calculations with the numbers (just like us Excel users) should have the option of exporting numbers as text, but it should not be the default.

BTW, I'm a Mac user and I'm using Excel and not Numbers.

Thank you,
Susanne

Sonja

For me it doesn't work too, because I use it with a pivottable. Pivottables doesn't support the formular with calculated field ("berechnetes Feld", =WERT(Dauer)*24). Normally yes, but not if its formatted as text.
It's no option for me to edit complete timesheets every export with a formular.
This i use to write invoices, actually I can't use WH to calculate my working hours :-(, so I have to calculate manually for any invoices.

Can I please use an older version?

timo

I explained above why I don't want to reverse to the old logic, because then I would again receive complaints from users on iOS and macOS that the duration isn't formatted properly.
But I wonder if it would work for you if you could using this time span format:

This writes the duration in decimal format (so 1 h 30 min would be written as 1.5). I haven't tested this myself, but this should display correctly in "Numbers" app - could you work with that number if it's written as "number" in the Excel file?

Sonja

timo I tested it before I wrote the answer and it doesn't work. It is formatted as text too. I’m using a German version, with a comma instead of a dot, and Excel is set to "from operating system". Could that be a cause?
Sorry for being so persistent, but a time tracking system where I can't add times is completely useless to me

timo

Sonja I tested it before I wrote the answer and it doesn't work. It is formatted as text too

Yes, currently the duration is written as text, no matter what time span format you select.
My question was if it would help you if I made that change in the next update - sorry, this wasn't really clear in my last post.
I just tested this on macOS and iOS with the Numbers app and it looks like it's formatted correctly, so I will make that change with the next update.

Sonja Sorry for being so persistent, but a time tracking system where I can't add times is completely useless to me

I'm pretty confused. Why can't you add times? You were referring to a edge-case (pivottables in excel) that is only used by a very small amount of users in the last post. To be honest, it's the first time ever I even heard that term

Sonja

timo ok, maybe english is not my language.
When is the next update? 😉, yes it would be okay.
And of course, I also don’t understand what the "Numbers App" is, as I exclusively use Windows and Android.

I try ChatGPT: Okay, I'll take the example without a pivot table: I export with the time span set to "10.55" and open the Excel table. Now, I select multiple cells containing "Duration." Excel recognizes them as text and therefore does not calculate a sum. Even a formula referencing the duration does not treat it as a value.

The pivot table is more flexible for me and makes it easier to create measurements than exporting via groups. I’ve built my tables around this workflow over the last five years...

timo

Sonja yes it would be okay

good! 🙂

Sonja When is the next update?

I can't say yet, maybe in the next 2-3 weeks...?

Sonja I also don’t understand what the "Numbers App" is

See Wikipedia: English / German

HannahKP

timo
Hi timo, I have the same issue as Sonja. I can't format the figures in the Duration column as numbers to be able to calculate them. It's great to see that you are going to roll out a fix/workaround in the next update. It's the only issue I've had with an otherwise brilliant app.

timo

HannahKP The new update is now available. Could you please test it and let me know if it works for you or not? And if not, why exactly?

HannahKP

timo
Will do, once I have the updated version.

Doug

I am having the same problem with the formatting of the Duration field. I have a Master Time Tracker sheet that consolidates the data across all of my clients into a single data sheet. This allows me to set up data queries and other tabs/tables all indexing the same data tab. The new format of the Duration tab messes up my formulas and it would take some time to rebuild my Master Tracker.

I did come up with a quick work around though in Excel. Try using the following formula to convert the Duration time value...(in my workbook, "F2" is the starting cell with the Duration in it, so change the cell reference to whatever yours is")
"=TIME(LEFT(F2,2),MID(F2,4,2),RIGHT(F2,2))"

NOTES: The Time formula in Excel changes the current text field to an Hour/Minute/Second format.

Hours = the 2 numbers on the far left side of the reference cell
Minutes = the 2 numbers that start in position 4 in the reference cell
Seconds = the 2 numbers on the far right side of the reference cell

After this, go into the number formatting and select "Custom" and scroll down to select "[h]:mm.ss"

If you play around with it, it should fix the number issue in the meantime.

Cheers! -Doug

HannahKP

Doug
Thanks heaps for posting this Doug! Will use this workaround in the meantime :-)