Excel changes time-format to datetime with MSQuery

Question

I make with Excel a MSQuery to obtain some fields from a table in MySql what is working fine but there is one column which has in MySQL a time-format and Excel delievers me this in datetime-format. I can format the cell in Excel so that it shows me the time in format hh:mm that is not the problem but I had to use these fields from the database to do some calculations in Excel which leads to my problem: I can't make a sum over these fields when the sum is greater then 24 hours, because then I get only the value of the hours and not from the days multiplied with 24 (e.g. instead of 25:15 I get only 1:15). Normally I use for this format [hh]:mm so there will be displayed all values (even for more then 24 hours) but this doesn't work here, because I get a very high value (for example: 1017144:15). The reason for this is, that Excel adds for every field to the time the acrtual date of today (e.g. 12:00 will be 12.01.2016 12:00) and with this for every time in the sum the value for the date will be add additionally. I tried it with the following statement in the MSQuery:

SELECT
   DATE_FORMAT(entry.timeBegin, '%h:%i') AS 'Beginn',
   entry.timeEnd AS 'Ende',
   TIME(entry.pause) AS 'Pause'
FROM timetable.entry entry<br>

All 3 columns have in the database in MySQL the format hh:mm. Ende and Pause behaves like described above and Beginn is formatted in the right way (just without formatting the field by Excel) but the content of the field has type string and with this, Excel always use 0 (zero) for these fields when calculating with them, so I get allways 0 as sum. I know that I can build the sum in MySQL, but this is no solution for me because the user could set some filters in Excel and this is to complicated to build in the query, espacially this is only one part of a more difficult construct.

Can you provide a few sample values you get in each one of those columns? Have you tried using Power Query instead of MSQuery? — Chicago Excel User, Jan 12 '16 at 14:51

Sascha · Answer 1 · 2016-01-14T10:50:38.857

Ok, here are a few examples. 'Beginn' allways delivers 0:00 (as described above) and 'Ende' and also 'Pause' are allways equal together. So we have to consider Ende or Pause with hh:mm and [hh]:mm format:

0:00 leads to 0:00 / 1017144:00
1:30 leads to 1:30 / 1017145:30
20:00 + 3:00 leads to 23:00 / 2034311:00
21:30 + 3:00 leads to 0:30 / 2034312:30

I didn't try PowerQuery because I don't know it till now and I don't know if it is inside of Excel or MS. I don't want to install an additional tool on the PC for every user. If I had only to take it into my Excel-sheet so it could be a solution if it is functions right.

Supplement: I looked after PowerQuery and see that this will bring me problems, because the most users uses MS Office 2010 and the requirement with Excel 2010 is Microsoft Office 2010 Professional Plus mit Software Assurance and the feature Software Assurance is not present in our company.

Added: For more clearness I add a screenshot from Excel:
screenshot of Excel

I get column A from SQL displayed in format h:mm.
Column B is the same just in number-format.
D2 and E2 are the Sum from A1:A10 in format h:mm respectively [h]:mm:ss.
D3 and E3 are the Sum from A1:A23 in format h:mm respectively [h]:mm:ss.
As you can see SQL delievers the Time 5:30:00 via MSQuery and Excel shows me 14.01.2016 05:30:00 which I can format in the right way (column A). Building the sum is possible (look D2) as long as it is smaller than 24 hours else I get only the hours (< 24) which are greater than a entire day. The reason for this you can see in D3 and E3.
What can I do to get the right values? I suppose the easiest way would be at the point of getting the data from MSQuery.

Excel changes time-format to datetime with MSQuery

1 Answers1