Modified Date in Formulas

I am trying to use the auto-populated system column "Modified" in formulas. Specifically, I am trying to create a "Modified Helper" column that takes the "Modified" column date alongside the time, checks if it is after midnight, checks which shift it was logged under, and log the auto-populated "Modified" date to reflect the previous days date if it was logged after midnight for 2nd shift in the "Modified Helper" column. I know i can use the formula:
=DATEONLY(
IF(AND([Shift]@row = "2nd", HOUR([Modified Helper]@row ) < 6),
[Modified Helper]@row - 1,
[Modified Helper]@row ))
to do this, but (I assume) since the "Modified" column is a system column, it will not let me use it in formulas. I continue to get an #INVALID COLUMN VALUE even with "=modified@row". Is there any way to automatically copy the "Modified" column to a new column so that I can use it in formulas besides making the person filling out the form type the date every time?

Find more posts tagged with

Sheets

Formulas

Accepted answers

ker9

@J.White - is your Adjusted Date column formatted as a Date?

All comments

Paul Newcome

There are a couple of things at play here. The most hindrance is actually going to come from the fact that there is no HOUR function in Smartsheet. Try this formula instead:

=DATEONLY(Modified@row - IF(AND(Shift@row = 2, TIME(RIGHT(Modified@row, LEN(Modified@row) - FIND(" ", Modified@row))) < TIME("6:00AM")), 1, 0))

J.White

The problem is that I cannot use a formula at all to reference the "Modified Date" system column, assuming since it is a system column.

J.White

I have even tried using the basic "=modified@row" formula to copy the "Modified Date" input to another column, and it gives the #INVALID COLUMN VALUE error.

ker9

Perhaps it is the use of the column name and it should be: [Modified date]@row

Paul Newcome

I used this in a sheet and referenced the system generated column to test it. You may want to double check your column types. Are you able to provide some screenshots?

J.White

Here is a screenshot of my problem. Each formula is in quotes above itself being run.

ker9

@J.White - is your Adjusted Date column formatted as a Date?

J.White

No, it was not. This fixed my problem, thank you all for the help!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.