-
Formula for Duration using Start & End Date & Time?
Hello, I would like a formula to calculate Duration (in hours) using the Start Date, End Date, Start Time, and End Time. I would like to keep the Time in 12-Hour format if possible. Any formula suggestions? I tried using a formula without factoring in the Dates, but the Duration for those highlighted in yellow ended up…
-
Sum If & contains
Hi, Hoping for some help with a Sum IF formula. I would like to sum if from 'Sheet 2' Value based on Sheet 1 project code. I have tried the below formula utilising 'Contains' however it is returning 0 rather than sum of project 304. Sheet 1 Sheet 2 Any assistance would be appreciated
-
Help with formula again
Hello, Sorry that I have been away but the last discussion I had regarding a formula revison still does not work. I've simplified my field names below and provided an example of what I would like displayed for the compliance status. Scenario: If all 3 columns have check marks and the flag is blank, then compliant. If 0 or…
-
Calculate the number of days in the different application statuses
I want to measure the number of days an application stays in the different application statuses. For this purpose, I have set up automated workflows to record a date in the corresponding date columns for each status change. When a date field is blank, it either indicates that the application skipped that status, or it is…
-
Getting the difference between a past date and today's date
I want to use the sheet summary to report how long a project has been put on hold. So I have the date that the project got put on hold, which is Aug 9, 2023. I want to automate the calaculation of how many days it has been at any given time. I've tried multiple formulas and none of them are working. Even typing in =TODAY()…
-
Remove duplicates based on Modified Date
Hi team, any suggestion is appreciated! I have 4 sheets: Sheets A-C: Individual project trackers Sheet D: Main project sheet What I need: Setting up a workflow that copies row updates in Sheets A-C over to the Main Sheet (Sheet D). This will ensure the Main Sheet includes all project statuses so I can report on them.…
-
Formulas to Calculate Dates/Duration
I manage an intake process and want to calculate the duration that an item is in each stage (there are 3 stages) I created 3 helper columns - Stage 1 Date, Stage 2 Date, and Stage 3 Date. I also created automation so that when an item reaches Stage 1, the date is recorded —- same with Stage 2 and 3. I need help on what…
-
Ability to Find & Replace text in Formulas
Hi Team, I am aware that this functionality isn't currently available. But, as someone who works on rows & rows of data every day, replacing values in a formula using the Find & Replace feature is extremely helpful and time-saving. As I type this, I have the daunting task of going back to my sheet on SmartSheets and…
-
Index and Match between sheets providing Invalid value
Hi All, I have a master data sheet collating uniform info + delivery addresses. All fields work but for some reason half of delivery details come as #INVALID VALUE - Not sure what is the issue or how to resolve. =INDEX({Uniform site master Range 1}, MATCH([Site SIN number]@row, {Site Details Range 2}, 0))
-
IF Formula - Checked Community and Can't Find a Solve
It will not return the N/A. I removed the quotes around N/A, added them in and it won't give me the result either way. I removed the Dropdown function on the GF Testing Required and made it to a Text/Number format but it will not return the "No" in the Form- Approved column. The formula works in Excel with the N/A in…