-
Time Tracking - Formula needed to total time spent
Has anyone used SS to track actual time spent? I am using the "Time Track for Client Projects" template modified for simple tracking of time spent for individual tasks by client or department. I cant seem to get the formula needed to add the columns for Start time & Stop Time to calculate and provide a Duration for actual…
-
for two criteria query should I use LOOKUP or INDEX MATCH
I need to populate the low range based on a match to a specific specialty and tier. The specialty is just a drop down but the Tier was based on the following IF formula: =IF([Points Total]11 <= 15, 3, IF([Points Total]11 >= 16, 2, IF([Points Total]11 <= 45, 2, IF([Points Total]11 >= 46, 1)))) * Question 1: Should "…
-
Status auto change
Hello, I was wondering is it possible to make the status RYG color changes when it is closer to the due date? For example: it shows red when it is due two days before the date and so on... Is there a formula to right and what is the formula? Thanks a lot
-
Checkbox checked, cancel RYG
Hello, I have created 3 columns for the RYG, if the project is in the early stages the color green will show, if it is due within 4 days, yellow will show, and when it is due in 2 days red will show. Now I have a checkbox for the employees to click if the work is complete and I want the RYG colors disappear when they click…
-
Can a Date autoupdate to the current date
Is there a way to set a date to dynamically update to TODAY? For example, I have a sheet with start date finish date and duration. I want to track how many days into the project without having to update the finish date continually.
-
Formula puts data left-justified instead of right-justified
I have the following simple formula: =IF([Grasp of Material]3 = "Excellent", "100%", IF([Grasp of Material]3 = "Good", "80%", IF([Grasp of Material]3 = "Fair", "60%", IF([Grasp of Material]3 = "Poor", "40%", IF([Grasp of Material]3 = "N/A", "N/A"))))) The formula puts the 100%, 80%, etc. as left-justified. When I try…
-
Form Submission Organization in Sheet
Is there a way to automatically move a new form submission to a nest under a particular row? For instance, I have 4 parent rows that nest several rows underneath and would like any form submissions to automatically be assigned under one of those 4 parent rows based on information that's submitted (if this, then nest under…
-
Creating IF AND THEN statement
Hello I need help with creating if and then statement to perform the following. If Recruited From = Referral And Store Start / Transfer Date = Date Then Store Start / Transfer Date +90 days Here's the code I have and it's giving unparsable. =IF (AND([Recruited From]147 = "Referral", [Store Start / Transfer Date]147= DATE,…
-
COUNT where cells <>"N/A"
Hi, I'm struggling to combine a SUM and a COUNT together (keeps returning an error). =SUM([CELL 1]22:[CELL 4]22) / COUNTIF([CELL 1]22:[CELL 4]22, <>"N/A") This returns a #DIVIDE BY ZERO. CELL 1 to CELL 4 could contain the numbers 1 to 5 or N/A. The end result is to provide a percentage out of 100% for the total cells with…
-
Formatting with alerts
I need to do the combination of formatting with and alert, but seem to have hit a wall. If column "delivery type"= selection "o" and column "1st delivery" has a date that is past due then turn "1st delivery" to red and send an alert to the contact in columns "PM" and a particular individual. Any help would be appreciated.