Best Of
Re: February Question of the Month - Join the conversation and receive a badge
If I could make a badge for Smartsheet, I'd make a series of badges for each of the community ranks. (Observer, Seeker, Contributor, Counselor, Leader, and Ambassador) As people achieve higher and higher ranks, their badge portfolio would be updated to mark each new milestone.
It would also be neat to poll the community of people who've achieved certain ranks and potentially vote on designs for these badges. Or, we could just leave it up to the Smartsheet team that already designs badges - they always put out some bangers! (e.g. these new QoM badge designs!)
Will Duplex
Re: Calculating time
Smartsheet is not set up for calculation time durations the way it is dates. However, I think I figured out a way to do this even listing times as you have here.
First, you need to create two "helper" text columns that will help isolate the hour markers and convert them from text to recognizable values (can be hidden once set up). Additionally, it will calculate how far in the hour your minutes by adding it as a decimal.
In the "Start Time Hour Helper" column, I have the following column formula:
=IF(LEN([Start Time]@row) = 7, VALUE(LEFT([Start Time]@row, 1)) + (VALUE(MID([Start Time]@row, 3, 2)) / 60), IF(LEN([Start Time]@row) = 8, VALUE(LEFT([Start Time]@row, 2)) + (VALUE(MID([Start Time]@row, 4, 2)) / 60), "ENTER VALID TIME"))
In the "End Time Hour Helper" column, I have the following column formula:
=IF(LEN([End Time]@row) = 7, VALUE(LEFT([End Time]@row, 1)) + (VALUE(MID([End Time]@row, 3, 2)) / 60), IF(LEN([End Time]@row) = 8, VALUE(LEFT([End Time]@row, 2)) + (VALUE(MID([End Time]@row, 4, 2)) / 60), "ENTER VALID TIME"))
Now, in your "Training Duration (min)" column, you need to be able to show the difference in hours multiplied by 60 minutes, while also accounting for the chance that hours may cross over from AM to PM since this example does not use military time. For this column, I wrote the following formula:
=IF(OR(AND(CONTAINS("AM", [Start Time]@row), CONTAINS("AM", [End Time]@row)), AND(CONTAINS("PM", [Start Time]@row), CONTAINS("PM", [End Time]@row))), ([End Time Hour Helper]@row - [Start Time Hour Helper]@row) * 60, IF(VALUE(LEFT([End Time]@row, 2)) = 12, ([End Time Hour Helper]@row - [Start Time Hour Helper]@row) * 60, (([End Time Hour Helper]@row + 12) - [Start Time Hour Helper]@row) * 60))
Assumptions:
-Sessions are in the same day (none that cross the overnight threshold)
-You will write start and end times one of two ways (with either 7 or 8 characters, including spaces). To ensure accuracy, I would add all the hours in the day as drop down values in your Start and End Time columns formatted the same way, assuming sessions start at predictable intervals (i.e. every half hour, 15 minutes, hour, etc.)
Here's a screenshot of what the finished product of these formulas looks like on my screen:
In any case, this was a fun one to figure out. Hope this helps!:)
brianschmidt
Re: Automate sending a form
It sure would be nice to have a form designer when you create an Update Request Workflow - creating logic is nice if I am requesting an update and the user is going to complete the ticket/row and I want to have another field come up to be filled. What is the point of being able to create multiple forms if they are ONLY used to create a new row.
Even better would be a Dynamic form that pulled data - i.e. use an Auto-Number field as a dropdown at the top of a form to fill any current data and allow the user to update or complete them. The problems with having used Access for years is I get used to the functionality of Forms pulling data - obviously I am not going back to access but what can you do SmartSheet?
Michael Berman
Re: IF(AND(OR? Formula
I think I see what you are trying to do, give this a try:
=IF(AND([Suggested Replacement Equipment]@row = 1, [Room Type]@row = "Standard"), 95 * [Hours Needed]@row * 0.3, IF(AND([Suggested Replacement Equipment]@row = 1, [Room Type]@row = "Non-Standard"), 115 * [Hours Needed]@row * 0.3, ""))
Carson Penticuff
Re: CountIFs
Give this a try:
=COUNTIFS([Travel State]:[Travel State], OR(@cell = "CO", @cell = "TX", @cell = "WA", @cell = "OK", @cell = "NM", @cell = "NE", @cell = "MO", @cell = "MN", @cell = "ID", @cell = "IA"))
Carson Penticuff
Re: Forms and entries
Great question! This gets asked a lot. Unfortunately a user cannot return to a form. Not for editing their previous entry, nor can they save half way through and return to complete it.
There are Product Ideas regarding this that you can upvote to help gain attention by Smartsheet's development team.
All the best,
-Ray
Ray Lindstrom
Re: Countifs in multi contact column referencing another sheet - Help!!
This seems to be working perfectly, thank you! I shall double check a few totals just to be sure but fingers crossed! Thanks so much.
NHS Cornwall
Re: Countifs in multi contact column referencing another sheet - Help!!
Give this a try:
=COUNTIFS({Archive T&F Date}, <=TODAY(), {Archive T&F Date}, >=TODAY(-365), {Archive T&F Presenter}, HAS(@cell, "Jen McFarland"))
Carson Penticuff
Re: Why has my Core App Skills badge not shown up on my profile?
Glad you got all squared away. Yes, there can be a delay on the badge appearing.
And yes, I am certified in both Core Product and Project Management.
I love using Smartsheet too! The company I work for is also the first company I've ever used Smartsheet with too. I had never even heard of it before working here.
If you don't mind, kindly mark my answer as "Answer", so other can more easily find answers. Thanks!
BRgds,
-Ray
Ray Lindstrom
Re: Help with INDEX/COLLECT with a MAX/COLLECT
Ok. Try this:
=INDEX(COLLECT({IA Assigned}, {RTO Queue ID}, [RTO Queue ID]@row, {Fully Executed Date}, OR(@cell = "", @cell = MAX(COLLECT({Fully Executed Date}, {RTO Queue ID}, [RTO Queue ID]@row)))), 1)
Paul Newcome