Assuming the whole format is YYYYMMDDHHMM and that the first such value is in cell A1, you can use the following formula in B1: =DATEVALUE(MID(A1,7,2) & '/' & MID(A1,5,2) & '/' & MID(A1,1,4)) + TIMEVALUE(MID(A1,10,2) & ':' & MID(A1,12,2)) This creates a date serial number based on DD/MM/YYYY using the MID function to gather each part, and the DATEVALUE function to return the serial number. It then adds to this the time serial number which uses the MID function in a similar way to gather the hours and minutes. You can then format the cell as a Date format, Time format, or a custom format of say dd/mm/yyyy hh:mm to see the end result.
Just right click the cell, choose Format Cells then configure similar to the screenshot below: Here's a screenshot of the end result.
Convert Date Nepali To English
In an Excel sheet I will receive the data, which in turn I need to upload it to SQL server and then implement the logic. I have received a date field -Due Date as number ex:.40317.
In Excel if you right click it and then format it to a date. It will show the correct date as. So after uploading the file as it is. I have used, SELECT (dateAdd(day,Due Date,'1900-01-01')) FROM table1. Assuming that Excel counts the days from. So I am adding the number -Due Date Which should give me the proper formatted date. But this returns me a value of 2010-05-21 00:00:00.000.
Can you please help me whether by assumption that Excel counts from is wrong, or the procedure that I have used is wrong to give such a value. I asked a a while ago - it wasn't talking about Excel but VB6, hence I'm not suggesting this question is a duplicate. However, the answer I think is the same. Date 0 in SQL is. Date 0 in VB6 is, 2 days earlier - that explains the difference. So I believe Excel to be the same.
In Excel, I entered 0 in a cell and formatted the cell as a date - which displayed (weirdly IMO) 0/1/1900. I've had a quick look to find another reference that answers this definitively, but haven't managed to find one. Edit: Pulling in links that were given to me in my question (that do relate to Excel). You need to subtract two days from 'excel date number' to convert it into 'MS SQL date'.
Select dateadd(day,-2, 36526) Explanation: Excel uses 0-jan-1900 as its begin date while SQL server uses 1-jan-1900, so to convert excel date to MS SQL date you can do something like this select dateadd(day,-1, 36526) But wait, earlier version of excel has bug and calculating 1900 as a leap year but in fact it was not a leap year. Current version still calculate the same (I assume due to backward compatibility. So we need to subtract 1 more day to fix this problem. So our final query to convert excel date number to SQL Server would be select dateadd(day,-2, 36526).