asked on

Need to format a column within my Excel formula to be a date

I keep getting a number instead of date within my Excel formula. How can I format the field within my formula to be the same date format as the data in the TAG_DATE column? Please see the attached screenshot.
Capture.PNG

Norie

Replace H2 in the formula with this.

TEXT(H2, "dd-mmm-yy")

maximus1974

ASKER

Thank you but I am receiving the following error attached.
Capture.PNG

Norie

What I posted should directly replace H2 in the formula, i.e there should be no quotes around it.

="UPDATE STOCK SET TAGGED_BY'"&G2&"', TAG_DATE='"&TEXT(H2,"DD-MMM-YY")&"' WHERE STM_AUTO_KEY='"&B2&"';"

maximus1974

ASKER

My apologies for my ignorance but I am obviously not understanding. I have removed quotes and replaced H2 with what you posted and still the same error.
Capture.PNG

Norie

Are you using a non-English version of Excel?

Gustav Brock

Try with a semicolon - and the ISO format for the date format, as you will need this in the finished SQL:

="UPDATE STOCK SET TAGGED_BY'"&G2&"', TAG_DATE='"&TEXT(H2;"YYYY-MM-DD")&"' WHERE STM_AUTO_KEY='"&B2&"';"

Open in new window

Result:

UPDATE STOCK SET TAGGED_BY='2019-10-15' WHERE STM_AUTO_KEY = '5519696';

Open in new window

maximus1974

ASKER

Same error using the semicolon. I am using an english version of Excel 365. I have attached the Excel file.
tagzz.xlsx

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Norie

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

maximus1974

ASKER

Thank you very much to both.

Gustav Brock

You are welcome!