Formatting a date value using double quotes

xlhelppls

New Member
Joined
Jun 17, 2016
Messages
17
Hello - I am trying to figure out the following scenario:
in Col H I have a date field and it has to be in this format -- "YYYY-MM-DD" - yes, surrounded by double quotes.
If I try to format the cell as a custom using "YYYY-MM-DD" when a user types in 2021-04-01, then I get this as the results in the cell -- YYYY-MM-DD (assume it is because I have the dbl quotes around it).
If I remove the dbl quotes in the format cells, then I just get 2021-04-01 without the double quotes.

is there a way to 'force' dbl quotes when using format fields?
Or is there some other option - like conditional formatting - where if the user doesn't enter it in the format of "YYYY-MM-DD" that I can highlight in red and pop a msg for them to know how it should be formatted? I started to try and do that in conditional formatting but again I am getting hosed by the dbl quotes :(

thanks in advance!
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Teeroy

Well-known Member
Joined
Nov 9, 2012
Messages
2,244
Office Version
  1. 365
Platform
  1. Windows
I'm not sure why you need the quotation marks, but if it's purely visual you can use standard dates instead and display them with the double-quotes.

Use a custom format on the date cell: \"yyyy-mm-dd\"
 
Solution

xlhelppls

New Member
Joined
Jun 17, 2016
Messages
17
I'm not sure why you need the quotation marks, but if it's purely visual you can use standard dates instead and display them with the double-quotes.

Use a custom format on the date cell: \"yyyy-mm-dd\"
Thanks so much, Teeroy. That did it!
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,528
Messages
5,854,278
Members
431,633
Latest member
Smugpop

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top