How do I extract dateonly from system generated date columns?
Hello, I would like to create conditional formatting based on a system-generated date being more than 7 days in the past (my test is if it's less than 6, then trigger the flag). I created a helper column to do the math, upon which the conditional formatting is supposed to be set (if the flag is activated, then turn specific columns in that row the selected background color). However, when I populate the formula, I receive an error message (first snapshot). I tested the DATEONLY formula in the source sheet and received a different error message (second snapshot). Your help is appreciated.
Best Answer
-
If you're looking at a System Generated column in the source sheet, you may need to use the DATEONLY as you had in your second image... however keep in mind that in order to use the exact formula you have pictured there you'd need to paste the formula into a Date Type of column (versus a flag). You could also just use =[System Column]@row in a Date Type of column to remove the timestamp.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Can you upload the screenshots again?
I hope that helps!
Be safe and have a fantastic weekend!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Here they are as an attachment.
-
You don't need to use DATEONLY, and your formula only had one too many parentheses.
Try this.
=IF(TODAY() - [Status Date]@row < 6, 1)
Did that work?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
It didn't work. I received an invalid operation error message instead.
-
Strange!
I'd be happy to take a quick look.
Can you maybe share the sheet(s)/copies of the sheet(s)? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Unfortunately I'm not able to share outside of my organization (permissions are set so our sheets are internal only). I'll keep following up with SmS directly. Thanks anyway for your help!
-
Ok.
You're more than welcome!
Feel free to update me on what the issue/solution was?
Can you also share a bigger screenshot of the one you shared last, so it includes all the referenced columns? I might be able to see the issue with that information.
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
How is the Status Date column being populated? It looks like this might be coming from a formula, which is pasting it as text in that cell (this would be why you're getting an error with the DATEONLY function - it cannot read any date but only sees text in the cell).
To convert the text to a date, you could use the DATE function. You'll need to grab different parts of the text to represent the day/month/year... see this post here by @Paul Newcome for an example.
Alternatively, could you change the Status Date column to be a date-type of column?
Let me know if this makes sense or if you need help building out the Date formula.
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Hi, Genevieve. In the initial sheet, it is part of a formula. I thought about that and went to the source sheet, only to still receive an error message (albeit a different one). It said invalid column type, and, when I tried to apply to the system-generated column in the source sheet (then I could use the summary section in the source sheet to populate the summary sheet and run the conditional formatting off of the flag or not flag). I'll review your post in more detail, and also check out the other post you mentioned, to see if either will help me. Thanks!
-
If you're looking at a System Generated column in the source sheet, you may need to use the DATEONLY as you had in your second image... however keep in mind that in order to use the exact formula you have pictured there you'd need to paste the formula into a Date Type of column (versus a flag). You could also just use =[System Column]@row in a Date Type of column to remove the timestamp.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
"If you're looking at a System Generated column in the source sheet, you may need to use the DATEONLY as you had in your second image... however keep in mind that in order to use the exact formula you have pictured there you'd need to paste the formula into a Date Type of column (versus a flag). You could also just use =[System Column]@row in a Date Type of column to remove the timestamp."
Darn! That didn't work, at least in my summary sheet. I'm going to try in my source sheet and see what happens
-
This wouldn't work in your Summary Sheet as it looks like the data is being pulled through as text, not as a date. The comment above is in reference to the original System Column in the source sheet, not the destination sheet where the date is from a formula (versus a system date). Does that make sense?
It would help to see a copy/paste of the formula you're using to bring across the date into this destination sheet, if possible.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Estelle Redding Try this in your flag column. The bold portion below extracts the date from the text string in your [Status Date] column and converts it into a useable date.
=IF(TODAY() - DATE(VALUE(20 + MID([Status Date]@row, 7, 2)), VALUE(LEFT(Status Date]@row, 2)), VALUE(MID([Status Date]@row, 4, 2))) < 6, 1)
-
Thanks, Paul, I am still a relative newbie and so don't understand some of the formulae you suggested.
Andree, no idea why, but now your original suggestions are working. I'm going to see if what you and Genevieve suggested will work.
Thanks, all, for your input! Here's hoping I eventually work it out!
-
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!