Extract Date from Modified Date

M. David

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.

Paul Newcome

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.

Paul Newcome

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.

M. David

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.

Paul Newcome

Yes. My apologies for not specifying. I usually use it within other functions, so I didn't even think to mention that.

Carmen Wong

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?

Paul Newcome

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

Brandon Herrington

I'm also getting the #invalid column value...

Paul Newcome

@Brandon Herrington Are you putting the formula in a column that has been formatted for dates?

anilkrag1984

Hi Paul

By using DATEONLY() formula if a date is in AM it will result the date one day prior, Can you please advise?

DATEONLY().PNG

Paul Newcome

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.

Cinda.q

@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

Paul Newcome

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

Ken Hoffman

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.

Paul Newcome

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

Ken Hoffman

Thank you Paul!

Paul Newcome

Happy to help. 👍️