-
Need help with an Index/Match using TEXT for Dates
Can someone help me understand how I can correct this formula? It's returning Unparsable. =INDEX({Revenue & Opex | ARRONI Row}, MATCH(TEXT(DATE(YEAR({Revenue & Opex Go Live Date}), MONTH({Revenue & Opex Go Live Date}) + 11, 1), "MM/DD/YYYY"), {Revenue & Opex Date Header}, 0)) The formula is indexing a full row. Matching a…
-
Extracting a Date from String of Text
I need help extracting a date from a string of text. I tested each component (year, month, day) separate and get the right result. However, when I put the extraction formulas together in a DATE formula (in a Date column type) I get INVALID DATA TYPE. Sample text string: 2025-10-20T23:26:16+03:00 Formulas that work…
-
1 or more months automatic fill into different columns
I want to put a date in columns that are monthly increments off an entered date field. We want to have reminders when a site needs to be reviewed for warranty items. This is based off 'Planted Date'. I created 12 columns for each month we need to visit the site. I was able to enter the following formula and it works till…
-
Automating Quarterly/Annual Reminders
I need to setup automations based on a contract date that will notify me on a monthly/quarterly/annual basis so I can process paperwork at the appropriate time. Ideally, I'd like to set it up to send an email 90 days before that date. I have columns for the initial contract date and the frequency (one for the word…
-
Finding Next Closest Date of Children
Ok hive mind - I haven't been able to talk this formula out with someone yet, so I am looking for a little help. I have the formula that will fill out the children of my FINAL DATE column, but to fill out the parent row, I want my formula to look at the children of the NON REFUNDABLE DEPOSIT column, and tell me what is the…
-
Sending an alert if any one of multiple columns changes after a deadline date
It doesn't appear that Smartsheet has an ISCHANGED function so I am struggling with an alert request. The request is for an alert if any one of 18 different columns has a value change after a deadline date. I guess I could create 18 Record Date automations on 7 different sheets to accomplish this, but that seems like a…
-
Return a value when one date is less than another
I'm trying to project the amount of non-bill hours we plan to spend on a project between the non-bill approval date and the contract end date. I'm getting an invalid operation error when using this formula: =IF([Column2]1 <= [Contract End Date]@row , [Approved Hours per week]@row , "") Here's my column setup: I've…
-
Formula to add Months to Date
This is the simplest formula I can find to add Months to Date. This considers sum of months exceeding 12, converting it to January and adding 1 to the year. Where: Term column is the number of months to add, Date is the starting date. =DATE( YEAR([Date]@row) + INT((MONTH([Date]@row) + [Term]@row - 1) / 12),…
-
Calculating Quarters with a Start and a Finish date
I searched the community and did not see a formula that calculated Quarter based on a Start and a Finish date while also accounting for cases where the year for the Start and Finish date differ. The formula below handles that. A few notes … It assumes that Finish is always >= Start. The entire formula is wrapped with…
-
Pivot App doesn't allow date columns?
I have a source sheet that has the following columns: Project Code , Milestone and Milestone Date . I want to use the Pivot app so that it gives me a row for each project code, columns of Milestones and the corresponding dates for those milestones. Am I correct in understanding that the Pivot app cannot output dates? I…