Excel LET Formula for creating small values tables from a table or range
-
Comments posted to this topic are about the item Excel LET Formula for creating small values tables from a table or range
-
One thing I couldn't do easily was to deal with dates (that are date values in excel). These would show as "44649.94918" in the values table. When you would probably want a datestring "2022/03/29 22:46:49.220" although you could convert the numbers into dates in sql I wouldn't recommend it.
Clearly you can just use a formula in excel to get the date as text e.g. =TEXT(A1,"yyyymmdd hh:mm:ss.000") to reformat a cell that is a date value into a datestring. But if anyone can come up with a nice way of checking if it is a date and formatting it as a string in the formula then that would be excellent. I could not find an easy way of doing this.
-
Thank you for this. I had to work out what a table was and then it just worked. I thought I had skills in building sql statements using stacked concatenate functions, but this is far superior and much easier. I have no suggestions on how to identify dates, I do everything I can in SQL so if it's just a date with no time, I would dateadd the number of days.
- allinadazework wrote:
One thing I couldn't do easily was to deal with dates (that are date values in excel). These would show as "44649.94918" in the values table. When you would probably want a datestring "2022/03/29 22:46:49.220" although you could convert the numbers into dates in sql I wouldn't recommend it. Clearly you can just use a formula in excel to get the date as text e.g. =TEXT(A1,"yyyymmdd hh:mm:ss.000") to reformat a cell that is a date value into a datestring. But if anyone can come up with a nice way of checking if it is a date and formatting it as a string in the formula then that would be excellent. I could not find an easy way of doing this.
The number 44649.94918 is a Date Serial Number based on the number of decimal days since midnight (00:00:00.000) on 1 Jan 1900. The integer part to the left of the decimal point is the whole number of days and the part to the right of the decimal point is the fractional part of the day. For example, if that value were .5, that would mean half the day passed and is 12PM. .25 would be 6AM and .75 would be 6PM for the same reason.
Excel is still sporting 29 Feb 1900 as a valid date and it is not. Fortunately, they fixed the date serial numbers from 1 Mar 1900 and on.
If none of your dates from Excel include any date prior to 1 Mar 1900, then you're in luck because the DATETIME data-type (which a lot of people make the mistake of poo-pooing) has some incredible capacities that many (most?) don't know and are rather undocumented in the MS documentation. CONVERTing that number to a DATETIME will do the trick.
Be advised, however, that you've not received the entire length of the Date Serial Number that you've posted. 5 decimal places is not sufficient to get the 1/300th of a second accuracy for milliseconds but is generally sufficient for to-the-second accuracy.
Here's the code that will convert the Date Serial Number you provided to an actual DATETIME. Do notice the discrepancy at the millisecond level that occurs because of the limited precision to the right of the decimal place in what you provided..
SELECT CONVERT(DATETIME,44649.94918)
That returns the following:
To get accuracy to the 1/300 (3.3 ms) for DATETIME, you need 10 decimal places at the minimum. I use FLOAT but DECIMAL(15,10) should also do the trick in most cases... I say "most cases" because I personally don't use anything but float for such things and so have had no reason to test it like I have with FLOAT.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply