Export [time] to excel use to be as a decimal i.e. 30min = 0.5 now it is exporting as a value i.e. 0:30 which you can't use unless you create a formula to convert. help

Hi James100,

Thanks for posting in the Community!

Are you exporting the Timesheets Report in QuickBooks Online? If so, currently, the time format shown on the report is by design. You'll have to manually edit the time format after exporting the excel file. Here's how you can format the time into decimal.

1. Open the excel file.
2. Select all the time you want to convert.
3. Right-click on it and select Format Cell.
4. Click on Custom from the left panel.
5. Enter the type format you want like h.mm.
6. Ensure that the new custom type is selected and then click Enter.

If you're exporting time from QuickBooks Time, you can choose the option to show HH:MM format. Here's how:

1. Log in to QuickBooks Time.
2. Click on the Time Off tab.
3. Click on Manual Time Card.
4. Click on Options and click Display time in HH:MM.

Then, export the report in excel once more. If the issue remains, we'd recommend checking in with our Customer Care Team to further check the report you're exporting.

Message us back if you need more help.We'll be here to assist you.

Thanks Sheila,

Confirming I am using Quickbooks online and am running the standard report [Time Activities by Employee Detail] report.

The version of Excel I am using is Microsoft Excel for Mac V16.54

The issue is not the actual report or how it is displayed as that is fine and as you say by design. The issue arises when you select [Export to Excel]. Up until at least May of this year (could be later) QBO by design use to export the time data as a numeric value i.e. 0:30min was exported as [0.5]. This meant the value could then be used to calculate in Excel, for example: when you are summating a number of timesheet values or using the values in a Pivot table. 

Regardless of changing the format, Excel currently only sees it as a non-numeric value and can only 'count' the records not use the actual time.

Interestingly and somewhat related the same issue happens (this issue has been around a lot longer) when you export the date value. Regardless of formatting, you have to actually convert the imported date value by using =datevalue as a formula before excel can recognise it as a date so you can then sort/filter etc esp when using Pivot tables.

As the point of excel is to drive further analysis of the quickbooks data, it would be helpful if both the time and date values could be exported as numerical values. I know it was certainly possible for the time value as I use to extract all my time reports this way.

thanks

Let's make sure you'll be able to submit your request, James100.

For now, you'll have to convert the imported date value by using a formula before Excel recognises the date. I'll take note of this and let our engineers know about the importance of simplifying things when exporting a report. In the meantime, I also suggest checking out if a request has already been submitted. If so, you can cast your votes instead. Please know the more votes we get, the bigger chances of getting this implemented. 

If not, you can submit a request to let them know how this is useful for your business. For more information, in case you'll want to send more than one report by email at the same time, add the reports to the group. Go through this article for the steps: Memorise Reports In QuickBooks Online. 

Please touch base with us if you have any other concerns about exporting reports. The Community is always here to guide and further assist you.