-
Using SumIfs with a date qualification
Hi Everyone. I would appreciate any feedback on what I'm doing wrong. Inf the formula below When I hardcode my dates below, I get the correct answer back. =SUMIFS({Tech Enablement-NCAL - Allocation}, {Tech Enablement-NCAL - Timeline Range 1}, HAS(@cell, $Resource@row), {Tech Enablement-NCAL - StartDate}, <=DATE(2026, 4,…
-
Google Map + Longitude & Latitude
I have a column in which I am trying to join a URL and two columns, one for Long, one for Lat. My goal is to write a formula to join them (i've tried, join, i've tried +)…. so that I can click on the link and be taking to the google map for the site. The issue is, the first coordinate is followed by a comma and the second…
-
Same Formula (=WEEKNUMBER(Date@row)) Different Result
Hello, Smartsheet Gurus! I am stumped. I am using the same =WEEKNUMBER(Date@row) formula in two different sheets. One sheet is pulling information from the other using the project number, employee name, and week number. I could not figure out why the data is wasn't pulling over because I used the same setup over and over…
-
Help with INDEX/COLLECT with a MAX/COLLECT
I am attempting to create a few reference columns using an INDEX/COLLECT function and incorporating a MAX/COLLECT because there is a 1-Many relationship on the RTO Queue ID in the 2nd sheet so I would like to use the row with the MAX Fully Executed Date. Below is the formula I am using: =INDEX(COLLECT({IA Assigned}, {RTO…
-
Subtracting Months from a date, Error #Invalid Value
I have three columns. One with a date 7/1/2027 in date format. One with a number representing the number of months I wish to subtract from the date column. Third column is a date column with the following equation: =DATE(YEAR([Column4]@row) - INT((MONTH([Column4]@row) - VALUE(Factor1)) / 12), MOD(MONTH([Column4]@row) -…
-
Allow Gantt view to use Date columns with Column Formulas for Start/End
I'm kind of flabbergasted that this is not currently possible in a Sheet, and I'm struggling to explain to my users why. It is already possible to populate a Date column with a Column Formula and then convert it to a Cell Formula, and then use that column as the Start/End for Gantt view / Project Settings, so the issue is…
-
Index, Match formula returning some, not all values
Formula was flowing correctly, but some change seems to have partially broken the pull but I can't figure out what/how. "Project" column dropdown is linked to a Master sheet. Budget Owner column contains the below formula which should look for the cell value in the Project/Cost Center/IO column within the P+CC+IO column of…
-
Duration based on time - formula question
I was able to create a Smartsheet column with time included with the date (Thank you @Paul Newcome & @Akblevins ). I want to add another column that just shows the time - I used the AI Smartsheet feature to create a formula but receive an error message. Any ideas on how to correct? TIA!
-
Help with a formula or formuals
I have 3 columns: Vic By plate number and model, Date Driven(Date Field), and mileage. I am trying to fina a formula or formuals to allow me to see the difference in milage since the last time the plate number has driven. Bonus: In 1 month. Trying to find an easy way to see priority of which vehicle needs to be driven next…
-
Tracking a 1-day project based on time not date
I am helping manage a 1-day Disaster Recovery exercise and we want to track the tasks by start & end based on time of day. Would also like to auto adjust the times with dependencies - Does anyone know if a template is available of has anyone ever done this before?