Formatting a date value using double quotes

xlhelppls

New Member
Joined
Jun 17, 2016
Messages
14
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!
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

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
14
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,127,751
Messages
5,626,654
Members
416,198
Latest member
Enigma909

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