-
End of Month Formula
I am using this formula to look at the created date of a submittal and then generate the end of the month date based on when it was created: =DATE(YEAR(Created@row ), MONTH(Created@row ) + 1, 1) - 1 But if something is created on the last day of the month, it puts the end of the month date for the next month. So, for…
-
I'm trying to create a tracking system using two sheets.
One sheet is the Gift Card Quantities has the number of existing gift cards in each dollar amount and the other is a tracker that allows the user to enter the gift card used, dollar amount, quantity of gift cards. What I want to do is if a $50 dollar gift card is used, then that number is subtracted from the existing…
-
How do I find the closest matching row that is above my searching row?
I am trying to find the closest matching row that is above my searching row. If Smartsheet does not allow searching backwards (above the current row) is there a way to search downwards, where the closest matching criteria to the searching row is displayed like the formula below is attempting to do? The following provides…
-
Need help with sheet reference formula to grab assigned user for a specific day.
I am working on some cross sheet data and sharing and would like to list in a cell of a sheet the user [Technician] assigned to a space [Bldg-Room] on Thursday from another sheet. There is a column for each day of the week on the sheet being referenced. [Thursday] I can use this formula within the current sheet but I seem…
-
How to Add and Equate for an Empty Field?
We have an intake sheet that we just updated to include a new field that will only have data input on a rare occasion. I need to figure out how to update my formula to take into account that input as we are measuring as a pass/fail system but I need it not to affect when it's empty. Here's the current formula:…
-
number of weeks between two dates
Please can you help find a formula to work out the number of weeks between two dates I want to know if there is a 5 week gap or not between date now request and proposed works columns the 5 week mark always starts on a monday e.g. if date requset was 30th July 25 then the five week mark would start on 4th august 25 - if…
-
RAID Formula for Dashboard
Does anyone have a RAID log formula they can share to calculate the overall and individual project health of a project based on the amount and priority level of Risks and Issues? Currently, I have formulas that evaluate the status of issues and risks in our RAID log which return color codes (Red, Yellow, Green) in a…
-
When cell populates, enter date
I have an automation set up for my Paid column. When a box is checked, then the automation runs and enters Paid into the Paid column. I would like to record the date the Paid column is populated in the Approved Date column. Is that possible?
-
ISCRITICAL for Parent Tasks
Currently, Critical Path does not propagate from a Child to the Parent Row. Create a functionality or a new formula for ISCRITICAL to show on Parent Tasks, maybe even up through a specified number of levels in a hierarchy.
-
Hours and Minutes calculation
Hi, I have a Start Time column and an End Time column and I would like it to calculate the hours and minutes of duration into another column. I scoured Smartsheets and found information on this, see link below, but when I try the formula, which should give me 0 hour(s) and 45 min, I get 0 hour(s) and 4500 min. Can someone…