How do I extract dateonly from system generated date columns?

Estelle Redding · 09/25/20

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.

Genevieve P. · 09/28/20

Hi @Estelle Redding

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.

Andrée Starå · 09/25/20

Hi @Estelle Redding

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!

Estelle Redding · 09/25/20

dateonly-system date columns.docx

Here they are as an attachment.

Andrée Starå · 09/25/20

@Estelle Redding

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?

Estelle Redding · 09/26/20

It didn't work. I received an invalid operation error message instead.

Andrée Starå · 09/27/20

@Estelle Redding

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)

Estelle Redding · 09/28/20

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!

Andrée Starå · 09/28/20

@Estelle Redding

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.

Genevieve P. · 09/28/20

Hi @Estelle Redding

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

Estelle Redding · 09/28/20

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!

Genevieve P. · 09/28/20

Hi @Estelle Redding

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.

Estelle Redding · 09/28/20

"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

Genevieve P. · 09/28/20

https://community.smartsheet.com/discussion/comment/264000#Comment_264000

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.

Paul Newcome · 09/28/20

@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)

Estelle Redding · 09/28/20

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!

Andrée Starå · 09/28/20

@Estelle Redding

Strange, but good! ;)

Let me know if I can help!

How do I extract dateonly from system generated date columns?

Best Answer

Answers

Help Article Resources

Categories