I'm having no luck being able to do sumifs or countifs on a cell range that is linked to an MS SQL server 2008R2 view when using the date column as a criteria.
If it is done on a native table link they works fine but it appears that the query delivers date as text, so..
- I've tried formatting the output of the query using convert but it is still a text output
- tried using datevalue function within the sumifs in excel but doesn't seem to work on ranges
- same goes for text function
Short of running macros to copy and convert (which sort of defeats the purpose of a live link), can anyone provide a workaround on either the server or excel side?
Cheers
Interestingly enough, Excel is unable to recognize SQL's data type
dateas such. If you want Excel to recognize it as a date then you will have to convert it todatetime. So, if you convert the date field into datetime with a cast in the query then Excel will recognize and treat it as such.This is due to the fact that Excel – unlike SQL since Version 2008 – is unable to differentiate between date and time. If you choose to enter a date into a cell and format it as such then it is still a
datetimeto Excel and if you reformat that cell to a date with time you’ll see that Excel automatically assumes a time of 00:00:00 (midnight). The same applies when you are entering merely a time. In this case Excel will automatically assume 00.01.1900 as the underlying “date”. The reason is that alldatetimes are stored in Excel as a number. Anything before the decimal point is the difference between 00.01.1900 and the date you wish to store, while anything after the colon represents the time of the day. As all numbers in any Excel cell may have a number before and after the colon there is onlydatetime. The number 42.000 represents the 42.000 day after 00.01.1900 which equates to December 27, 2014 and a time of midnight while the number 42.000,75 means that 75% of the day is over: 0,75 * 24 hours = 6PM = 18:00:00 (military time) on December 27, 2014.