Question description:
This user has given permission to use the problem statement for this
blog.
Formatting dates will not stick if we refresh the data
Solved by T. S. in 30 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
04/03/2017 - 08:20
Hello
Excelchat Expert
04/03/2017 - 08:20
Is it working now after import?
User
04/03/2017 - 08:21
I still did not get the file
User
04/03/2017 - 08:21
The Window got closed
Excelchat Expert
04/03/2017 - 08:21
OK i am checking
Excelchat Expert
04/03/2017 - 08:21
I sent you on your email
Excelchat Expert
04/03/2017 - 08:22
Please send me your email id
User
04/03/2017 - 08:25
swapna174@gmail.com
User
04/03/2017 - 08:28
Did u send
Excelchat Expert
04/03/2017 - 08:28
Yes i sent again
User
04/03/2017 - 08:29
I am check it now
Excelchat Expert
04/03/2017 - 08:29
ok
Excelchat Expert
04/03/2017 - 08:31
https://docs.google.com/spreadsheets/d/1MDXJM-IkrNrMZeV5cFvKG1v2UYwvj_uSFDOaCbg1pXg/edit#gid=2002594769
User
04/03/2017 - 08:33
it is protected view and cannot open
User
04/03/2017 - 08:33
I see some error in there
Excelchat Expert
04/03/2017 - 08:34
OK i am fixing that
User
04/03/2017 - 08:35
so u want me to write Formula there
User
04/03/2017 - 08:35
=DATEVALUE(LEFT(Sheet2!$E2,LEN(Sheet2!$E2-FIND("/",Sheet2!$E2)))&"/"&"20"&RIGHT(Sheet2!$E2,LEN(Sheet2!$E2)-FIND("/",Sheet2!$E2)-3))
Excelchat Expert
04/03/2017 - 08:35
Yes this is another sheet
User
04/03/2017 - 08:35
So u need to write the formaula and Use it
User
04/03/2017 - 08:35
Is that what uare telling me
Excelchat Expert
04/03/2017 - 08:36
Yes
Excelchat Expert
04/03/2017 - 08:37
[Uploaded an Excel file]
Excelchat Expert
04/03/2017 - 08:37
Please download this file
Excelchat Expert
04/03/2017 - 08:37
You can work directly from here
Excelchat Expert
04/03/2017 - 08:37
No error here
User
04/03/2017 - 08:37
ok
User
04/03/2017 - 08:38
SO let me add teh formula and See
Excelchat Expert
04/03/2017 - 08:38
Please import your data on Sheet2 and you can see on output sheet
Excelchat Expert
04/03/2017 - 08:38
Use this sheet directly
Excelchat Expert
04/03/2017 - 08:39
[Uploaded an Excel file]
User
04/03/2017 - 08:39
ok
Excelchat Expert
04/03/2017 - 08:39
Download this one
Excelchat Expert
04/03/2017 - 08:39
and use this
User
04/03/2017 - 08:39
I dont wnat to create a new sheet Again
User
04/03/2017 - 08:40
Because my user for sure would not like this
Excelchat Expert
04/03/2017 - 08:43
Are you there?
User
04/03/2017 - 08:44
yes
Excelchat Expert
04/03/2017 - 08:44
Can you import data now?
User
04/03/2017 - 08:45
no
Excelchat Expert
04/03/2017 - 08:45
What is the problem now?
User
04/03/2017 - 08:45
Filter are all gone
User
04/03/2017 - 08:45
And refresh button is gryed out
Excelchat Expert
04/03/2017 - 08:46
Fro which sheet you trying?
Excelchat Expert
04/03/2017 - 08:46
Sheet2 or output
Excelchat Expert
04/03/2017 - 08:47
And the best is tell your developer to fix date format on sql db
User
04/03/2017 - 08:47
For Sheet2
User
04/03/2017 - 08:48
what format should it be
Excelchat Expert
04/03/2017 - 08:48
mm/dd/yyyy
User
04/03/2017 - 08:48
mm/dd /yy
User
04/03/2017 - 08:48
Yes It is that format only
User
04/03/2017 - 08:48
When i run the query I see o/p as mm/dd/yyyy
Excelchat Expert
04/03/2017 - 08:48
No your current format is mm/dd/yy that not correct format
Excelchat Expert
04/03/2017 - 08:49
In your query it's the format but when you input data on sql the format is mm/dd/yy
