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.
Hi Ristee,
Please read this
https://www.myonlinetraininghub.com/excel-forum/forum-rules-and-guides/read-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.myonlinetraininghub.com/excel-date-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
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.
No worries, you can always create a dummy file with just a few examples of the data.
Thanks for letting us know you got it figured out.
Regards
Phil
