-
Military Time to Standard Time
There are several articles about how to convert standard time to Military Time but I cannot find anything that tells me how to convert Military Time into Standard Time. Ex: If I enter 16:13 in Column A, I want Column B to display 4:16 PM. Can anyone help me with that? Thanks!
-
COLLECT with an OR criteria
I have a COLLECT statement with multiple criterias, one being and involving an OR. In the below formula I have a Discipline column where I want to bring back Reading or Literature for a particular year. =JOIN(COLLECT({SA Grid: ELA}, {SA Grid: Go/NoGo}, ="[X]", {SA Grid: Year}, =Year@row, {SA Grid: Discipline},…
-
Percentage Formula based on Stage
Hello, I have a process that is 11 stages long (stage 1, stage 2, ...) and I want to write a formula for percent complete based on the current stage. Each stage will be equal in percent value. There will be many clients items going through this process, 1 line per client. There will be a column with the clients name, then…
-
Need a fix to formula to verify with system date and return a value
With the current formula i'm able to verify system date with the value started with two characters but not more than 2 Characters. Can some one please help to fix this. =IFERROR(IF(VALUE(MID(SUBSTITUTE(Source@row, " ", ""), 3, 2)) > VALUE(RIGHT(YEAR(TODAY()), 2)), "N/A", IF(AND(VALUE(MID(SUBSTITUTE(Source@row, " ", ""), 3,…
-
index/match formula that returns image instead of file name
has anyone been able to come up with a formula to return the actual image instead of the file name? i tried index/match but wasn't successful. found a few previous posts re: vlookup and data mesh that shared similar struggle. 2018 https://community.smartsheet.com/discussion/29921/vlookup-of-image 2020…
-
Another formula question
Hi, I was trying to do a count for how many fire drills in each month. I have already done helpers for monthly and have a separate column for fire drills. This is the formula I have, can anyone help?
-
If/Or with an Index Match
My formula is able to locate from a reference sheet the return value of the HC Request Status1 as a match, however I need to add that when a [Job Class] = "Associate" the HC Request Status = Approved. I am not sure how to correct my formula as I am getting an error 🤨 "unparseable"
-
SUMIF question
Hello all! I am trying to update a SUMIF formula to sum a column's data for multiple names. It appears each time it will only include 1 name (POC) which is alphabetically first for the formula. =SUMIF({Sort QPP - Q1 2023v2 (POC)}, [Column2]7, {Sort QPP - Q1 2023v2 (Feb Month)}) Thank you.
-
Averageif for specific Project Phase Duration
I need to show the average of an {Initiation Duration} by each month. So average for Jan, Feb, March, etc. Columns Initiation Start (Date Column) Initiation End (Date Column) Initiation Duration (text Column w/ formula) I have a formula on my source sheet or "portfolio" for Initiation Duration which is:…
-
Calculating time passed from one column to another
I would like to calculate time passed from a Start Column '00:00' to an End Column '17:00' for example This 'Overtime Hours' should be automatic, right now I have to fill it myself.