Forum Discussion
Time to Duration conversion throws an error.
I'm trying to convert time of day to duration in order to perform summary calculations. However, when I try to convert time to duration in Query Editor the following error occurs: DataFormat....
I was unable to find how to attach an Excel file.
The dates are all formatted as dd/mm/yyyy.
I have sorted them by Start Date.
Does the one below work?
| ProjectID | TaskDescription | Start date | Start time | End date | End time | Duration | Tags |
| 018/CAD | Library Management | 26/08/2014 | 15:48:29 | 26/08/2014 | 16:45:56 | 00:57:27 | Cont. Impr. - Templates / Families |
| 018/CAD | Revit Training Sessions preparation | 11/09/2014 | 09:09:25 | 11/09/2014 | 11:34:31 | 02:25:06 | Cont. Impr. - Training / Induction |
| 018/CAD | CPD Feedback form fill out | 11/09/2014 | 17:11:57 | 11/09/2014 | 17:29:06 | 00:17:09 | Cont. Impr. - Training / Induction |
| 018/CAD | Railings CPD Preparation | 12/09/2014 | 16:00:08 | 12/09/2014 | 17:23:20 | 01:23:12 | Cont. Impr. - Training / Induction |
| 018/CAD | Training area setup | 15/09/2014 | 17:00:33 | 15/09/2014 | 17:31:48 | 00:31:15 | Cont. Impr. - Training / Induction |
| 018/CAD | Railings CPD Preparation | 18/09/2014 | 13:45:51 | 18/09/2014 | 17:32:08 | 03:46:17 | Cont. Impr. - Training / Induction |
| 018/CAD | CT Template | 25/09/2014 | 08:56:46 | 25/09/2014 | 10:08:53 | 01:12:07 | Cont. Impr. - Standards / Guides |
| 018/CAD | BIM Conference report | 13/10/2014 | 12:55:12 | 13/10/2014 | 13:06:33 | 00:11:21 | Cont. Impr. - Conferences & Workshops |
| 018/CAD | Revit training sessions outline and preparation | 27/10/2014 | 15:20:50 | 27/10/2014 | 15:27:54 | 00:07:04 | Cont. Impr. - Training / Induction |
| 018/CAD | CPD on Views preparation | 13/04/2015 | 13:25:14 | 13/04/2015 | 15:26:36 | 02:01:22 | Cont. Impr. - Training / Induction |
| 018/CAD | Task review and Management | 29/04/2015 | 17:29:40 | 29/04/2015 | 17:30:38 | 00:00:58 | Office procedures |
| 018/CAD | HTG Packaging and uploading to the Hub | 01/05/2015 | 11:07:41 | 01/05/2015 | 12:03:30 | 00:55:49 | Cont. Impr. - Standards / Guides |
| 018/CAD | BEP Template update | 12/05/2015 | 11:25:35 | 12/05/2015 | 11:32:17 | 00:06:42 | Cont. Impr. - Templates / Families |
| 018/CAD | Task review and Management | 10/07/2015 | 13:43:26 | 10/07/2015 | 13:44:00 | 00:00:34 | Office |
| 018/CAD | Task review and Management | 14/07/2015 | 09:38:39 | 14/07/2015 | 09:43:16 | 00:04:37 | Office |
| 018/CAD | CPD Curtain Walls | 17/07/2015 | 11:51:35 | 17/07/2015 | 12:31:56 | 00:40:21 | Cont. Impr. - Training / Induction |
| 018/CAD | CPD - The Series | 07/04/2016 | 10:51:05 | 07/04/2016 | 10:59:45 | 00:08:40 | Cont. Impr. - Training / Induction |
| 018/CAD | Audits Dashboard | 08/04/2016 | 16:41:13 | 08/04/2016 | 17:39:12 | 00:57:59 | Cont. Impr. - R&D |
| 018/CAD | Audits Dashboard | 08/04/2016 | 16:09:32 | 08/04/2016 | 16:17:24 | 00:07:52 | Cont. Impr. - R&D |
Anonymous I've made a PBIX file but I can't get it to you yet. I need to find a way to upload it, thanks.
EDIT: I was able to upload it to the Power BI Community: http://community.powerbi.com/oxcrx34285/attachments/oxcrx34285/DesignMakeover052016/5/2/sample_ds.pbix
I had to reformat some of the start date and time data in an excel file, but you should be able to figure out how I got the calculated columns. Let me know if this is what you were looking for or at least if I was on the right track, thanks.