how can I input a logical date in the following format "YYYY-QQ" (ie, 2021-Q2)...
how can I input a logical date in the following format "YYYY-QQ" (ie, 2021-Q2) so that it can be sorted and also be referenced in a formula ?
Answers
-
Hi Jeff,
How are you wanting to use this in a formula?
A value like this would be read as text since Date-Type columns won't be able to read this format as a date. If you were manually inputting this data into your sheet, you could use a LEFT function in a formula to reference the year by pulling the four characters on the LEFT of this cell. This depends on what you're looking to do, though, which is why I asked about what type of formula you're wanting to use this value in.
Alternatively, you could have two helper columns: one that has the date in a Date type of column, and one that notes what Quarter it is. Then if you wanted it to be displayed together you can combine the YEAR from the date column and the text from the Quarter column into one Text/Number type of column.
To do this, you would use the YEAR function to pull the year, then add (+) the - and the Quarter:
=YEAR([Date Column]@row) + " - " + [Quarter Column]@row
This would allow you to use the Date column in other formulas.
Let me know if this makes sense or not! If not, it would be helpful to know a bit more about your process (how the data is being entered) and how what different formulas you want to reference this cell in.
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 141 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!