Help with Dates

Hi,

I've got this formula below and I'm very confused why it spits out 1 rather than 0 as I believe 12/29/2024 is less than 1/5/25. Is it because it's parsing it as DDMMYYYY rather than MMDDYYYY? I feel like that's probably not the case since 12/29/2024 would spit out an error if the format was supposed to be DDMMYYYY.

Also I can't set the columns to be dates as I need a helper row to account for certain items so the column properties have to be text/numbers.

Any help would be greatly appreciated. Thanks!

However, when I use the 2025 dates comparison, it returns what I would have expected:

Find more posts tagged with

Accepted answers

All comments

Adam Murphy

Is it just a typo in your first formula?

vt4ethan

https://community.smartsheet.com/discussion/comment/453125#Comment_453125

The first equation returns a 1. Which is not what I would have expected it to return. I would have expected it to return a 0.

Nic Larsen

If your columns with the dates are not date type columns then it is not recognizing them as dates and will give you false results.

vt4ethan

https://community.smartsheet.com/discussion/comment/453132#Comment_453132

So a follow-up question for you then @Nic Larsen. So the only reason why I'm using these dates is because it's a helper row because based on previous digging, there's currently no formula or expression/function that allows me to use the name of a column in my formula. My goal formula is to leverage two dates in two different columns to return a $ value, how should I then go about doing it? If I turn the whole column into a date column, then it won't return a $ value.

Nic Larsen

I can certainly try and assist but I think we'll need more info on what you're trying to do. Screenshots perhaps, hide or change any confidential info. Also every reference in a formula is using a Column Name so I am not sure what exactly you are saying or what's preventing you from doing so.

vt4ethan

@Nic Larsen. Ok sounds good

So I'll describe it like an XY graph but with rows and columns. So the row is the project and relevant project information. The Y axis (columns) are the weekly dates/benchmarks. Where they meet up will be the $$ values that are calculated for that specific week and for that specific column.

Based on this help forum below, it's determined that I can't utilize a reference to a column name i.e. Column name is "1/25/2025". There's no way for me to reference the name of the column which is 1/25/2025 in my formula within that column. I can refer to the totality of that row and the information within that column but not the column name.

https://community.smartsheet.com/discussion/77124/how-to-reference-column-name-in-formula

I don't want to transpose the column name data into a column because that would just make the sheet incredibly bulky and hard to parse because that would mean I would have 52 rows for just one project.

Adam Murphy

I agree with Nic, needs to be a date column for those dates to be recognized. In your example screenshots, I think you are using the actual dates (which are being recognized as text) in place of the Column names (what should be between the [ ]'s), which is what you would want to use (not sure why you think you cannot do that).

vt4ethan

Oh weird my comment disappeared.

So I want to reference the column name as a value in the formula... so for example, to return the name of the column if a matching value is found. That's why I have to use a helper column. And I believe if I turn the whole column into dates, I can't return a $$ in my function within that column.

vt4ethan

The structure of the sheet is to have the projects in each row and the dates in each column. And the cell where it matches (x and y) would return the $$ of the project. So because I can't return the name of the column, I have to have a helper row.

Nic Larsen

Okay yeah that you cannot use a Column name as a value. But if you're projects are listed in Column 1, and Column 2 and 3 are date columns, then run your formula in column 4. You may need to restructure to make it work. Hard to know though without some visual.

vt4ethan

My columns are weekly so I'd have 52 rows for each project in the sheet. It makes it a bit untenable if I have 52 rows for each week per project, especially since Smartsheet has row limitations which I've definitely hit with the data shuttle to bring the data in lol

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.