**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