Extract Date from Modified Date
I am trying to use the Modified Date column in formulas. For some reason, it doesn't appear that this was created as a "Date" column type. Because of this, it cannot be used in any formulas that would allow a date. Is this assessment correct? Also, is there a way of pulling the date out? I've pulled out the Month, Day, and Year individually into three separate columns, but then when I concatenate them using "+'s", it still doesn't work because it is not a date format. Can somebody please tell me how to get the date out of this column?
Thanks.
Best Answer
-
Check out the DATEONLY function. It is specifically designed for this.
=DATEONLY([Modified Date Column]@row)
You can use it to pull the date into a separate column or you can use it within other functions.
Edited to include link to DATEONLY documentation.
Answers
-
Check out the DATEONLY function. It is specifically designed for this.
=DATEONLY([Modified Date Column]@row)
You can use it to pull the date into a separate column or you can use it within other functions.
Edited to include link to DATEONLY documentation.
-
That worked great. The only thing I would say is make sure the column that contains the formula is also a "Date" type. Thanks for the help.
-
Yes. My apologies for not specifying. I usually use it within other functions, so I didn't even think to mention that.
-
Getting an #invalid Column Value when I do =DATEONLY([Modified Date Column]@row) --- for some reason all my modified dates changed today- something to do with the outage?
-
@Carmen Wong Make sure the column you are putting the formula in is formatted as a date type column.
If you added a formula to all of the rows today, then the Modified column should have updated.
-
I'm also getting the #invalid column value...
-
@Brandon Herrington Are you putting the formula in a column that has been formatted for dates?
-
Hi Paul
By using DATEONLY() formula if a date is in AM it will result the date one day prior, Can you please advise?
-
It looks like you may have a time zone issue. If you do a search here in the community on time zones, you should be able to find a few different solutions.
-
@Paul Newcome Is this issue due to a time zone issue?
The Modified Date column is formatted via the formula "=DateOnly(Modified@row)"
Thank you,
Cinda Quass
-
@Cinda.q It looks like it very well may be. To test, you would want to insert a handful of test rows and modify them one at a time to see which hour the switch occurs. From there you would need to use one of the timezone solutions where you basically pull the hour into an IF statement to say that IF the hours is greater than # AND "PM" is present, subtract 1.
-
Hi @Paul Newcome,
I am having a similar issue with DateOnly, but I'm getting invalid Data Type. The RD Date Column is a date column.
-
@Ken Hoffman It looks like your Requested Date column is not a system generated column (either modified or created) in which case the DATEONLY function will not work.
You will need to use a DATE function and a combination of VALUE and MID functions to parse out the date portion into a usable date.
=DATE(VALUE(MID([Requested Date]@row, FIND(",", [Requested Date]@row) - 4, 4)), VALUE(LEFT([Requested Date]@row, FIND("/", [Requested Date]@row) - 1)), VALUE(MID([Requested Date]@row, FIND("/", [Requested Date]@row) + 1, FIND("/", [Requested Date]@row, FIND("/", [Requested Date]@row) + 1) - (FIND("/", [Requested Date]@row) + 1))))
-
Thank you Paul!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!