Log In
Register
Home
June 22, 2020
Offline
Hello,
I want to make a data model for files that have stopwatch inside(made with VBA).
The format in the table where the stopwatch is giving me results is : [h]:mm:ss and it needs to be like this in order to sum the results.
When I use power query to make Data Model, I don't know if there is a possibility to make the column in this format([h]:mm:ss) so the results are correct.
If I don't select anything, when the power query is done and I want to make the pivot table it doesn't show me the correct results because of the format.
Thank you.
October 5, 2010
Offline
Hi Ristee,
Please read this
https://www.myonlinetraininghu.....this-first
Always supply a workbook so we can refer to the data you are talking about.
Excel stores dates and times as serial numbers
https://www.myonlinetraininghu.....e-and-time
So if your VBA is correctly generating times, these will be stored as a serial number, not as text of the format h:mm:ss. These times can then be used in PQ and/or pivot tables.
You can use PQ to reformat time stored as text but it's best to correctly generate and use time serial numbers.
If you supply your workbook I can see what's happening.
regards
Phil
June 22, 2020
Offline
Hello Philip,
I am sorry that I didn't attach the file, but because it's from the company that I work in it is forbidden.
Anyway, I managed it.
I formatted the column as general in my original workbook, generated the power query for a Data Model, and after I made the pivot table where I needed, I changed the format into h:mm:ss and it gave the numbers correctly as I needed.
Thank you and sorry again for not respected the rules. I will keep in mind in the future.
Regards,
Riste.
1 Guest(s)
