Welcome to the Smartsheet Forum Archives
The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.
Need a Column Property type that is "clock" or "time of day"
Comments
-
I work in IT as well and have a need for tracking start/end times for tasks. For major IT implementations, the time duration data is critical for coordination of sequential and dependent activities.
-
I was told this was almost completed months ago.
-
Hi all - I will be posting some Time Formulas below (big thanks to super smartsheeter Ajay).
Hint: Copy these formulas into a text editor. Search & replace column names in the example with your own column names. Then paste that formula in your sheet. If you do this manually and make a mistake it won't work and will be very difficult find the error.
Formula 1:
Given a time, determine how many minutes it is from midnight. Will take into account 24-hour military time as well as 12-hour am/pm. It will assume if the letter "p" is in the time it is PM. This handles 12:00a , 12:00, and 12:00p properly.
((IF(LEFT(StartTime2, FIND(":", StartTime2) - 1) = "12", IF(OR(FIND("a", StartTime2) > 0, FIND("p", StartTime2) > 0), 0, 12), VALUE(LEFT(StartTime2, FIND(":", StartTime2) - 1))) + IF(FIND("p", StartTime2) > 0, 12)) * 60 + VALUE(MID(StartTime2, FIND(":", StartTime2) + 1, 2)))"
Use Case: Useful to comparing two times (in the same day) to determine which is later or the difference between them.
Formula 2: Given two times, a StartTime and EndTime, calculate the difference in hours (format 1 3/4 hours -> 1.75) Handles 12:00a , 12:00, and 12:00p properly.
(((IF(LEFT(EndTime4, FIND(":", EndTime4) - 1) = "12", IF(OR(FIND("a", EndTime4) > 0, FIND("p", EndTime4) > 0), 0, 12), VALUE(LEFT(EndTime4, FIND(":", EndTime4) - 1))) + IF(FIND("p", EndTime4) > 0, 12)) * 60 + VALUE(MID(EndTime4, FIND(":", EndTime4) + 1, 2))) - ((IF(LEFT(StartTime4, FIND(":", StartTime4) - 1) = "12", IF(OR(FIND("a", StartTime4) > 0, FIND("p", StartTime4) > 0), 0, 12), VALUE(LEFT(StartTime4, FIND(":", StartTime4) - 1))) + IF(FIND("p", StartTime4) > 0, 12)) * 60 + VALUE(MID(StartTime4, FIND(":", StartTime4) + 1, 2)))) / 60
Use Case: Useful for time tracking
Formula 3: Given two times, a StartTime and EndTime, calculate the difference in hours (format 1 3/4 hours -> 1:45) - get ready for this one!
Handles 12:00a , 12:00, and 12:00p properly.
INT((((IF(LEFT(EndTime4, FIND(":", EndTime4) - 1) = "12", IF(OR(FIND("a", EndTime4) > 0, FIND("p", EndTime4) > 0), 0, 12), VALUE(LEFT(EndTime4, FIND(":", EndTime4) - 1))) + IF(FIND("p", EndTime4) > 0, 12)) * 60 + VALUE(MID(EndTime4, FIND(":", EndTime4) + 1, 2))) - ((IF(LEFT(StartTime4, FIND(":", StartTime4) - 1) = "12", IF(OR(FIND("a", StartTime4) > 0, FIND("p", StartTime4) > 0), 0, 12), VALUE(LEFT(StartTime4, FIND(":", StartTime4) - 1))) + IF(FIND("p", StartTime4) > 0, 12)) * 60 + VALUE(MID(StartTime4, FIND(":", StartTime4) + 1, 2)))) / 60) + ":" + IF(((((IF(LEFT(EndTime4, FIND(":", EndTime4) - 1) = "12", IF(OR(FIND("a", EndTime4) > 0, FIND("p", EndTime4) > 0), 0, 12), VALUE(LEFT(EndTime4, FIND(":", EndTime4) - 1))) + IF(FIND("p", EndTime4) > 0, 12)) * 60 + VALUE(MID(EndTime4, FIND(":", EndTime4) + 1, 2))) - ((IF(LEFT(StartTime4, FIND(":", StartTime4) - 1) = "12", IF(OR(FIND("a", StartTime4) > 0, FIND("p", StartTime4) > 0), 0, 12), VALUE(LEFT(StartTime4, FIND(":", StartTime4) - 1))) + IF(FIND("p", StartTime4) > 0, 12)) * 60 + VALUE(MID(StartTime4, FIND(":", StartTime4) + 1, 2)))) - INT((((IF(LEFT(EndTime4, FIND(":", EndTime4) - 1) = "12", IF(OR(FIND("a", EndTime4) > 0, FIND("p", EndTime4) > 0), 0, 12), VALUE(LEFT(EndTime4, FIND(":", EndTime4) - 1))) + IF(FIND("p", EndTime4) > 0, 12)) * 60 + VALUE(MID(EndTime4, FIND(":", EndTime4) + 1, 2))) - ((IF(LEFT(StartTime4, FIND(":", StartTime4) - 1) = "12", IF(OR(FIND("a", StartTime4) > 0, FIND("p", StartTime4) > 0), 0, 12), VALUE(LEFT(StartTime4, FIND(":", StartTime4) - 1))) + IF(FIND("p", StartTime4) > 0, 12)) * 60 + VALUE(MID(StartTime4, FIND(":", StartTime4) + 1, 2)))) / 60) * 60) < 10, "0") + ((((IF(LEFT(EndTime4, FIND(":", EndTime4) - 1) = "12", IF(OR(FIND("a", EndTime4) > 0, FIND("p", EndTime4) > 0), 0, 12), VALUE(LEFT(EndTime4, FIND(":", EndTime4) - 1))) + IF(FIND("p", EndTime4) > 0, 12)) * 60 + VALUE(MID(EndTime4, FIND(":", EndTime4) + 1, 2))) - ((IF(LEFT(StartTime4, FIND(":", StartTime4) - 1) = "12", IF(OR(FIND("a", StartTime4) > 0, FIND("p", StartTime4) > 0), 0, 12), VALUE(LEFT(StartTime4, FIND(":", StartTime4) - 1))) + IF(FIND("p", StartTime4) > 0, 12)) * 60 + VALUE(MID(StartTime4, FIND(":", StartTime4) + 1, 2)))) - INT((((IF(LEFT(EndTime4, FIND(":", EndTime4) - 1) = "12", IF(OR(FIND("a", EndTime4) > 0, FIND("p", EndTime4) > 0), 0, 12), VALUE(LEFT(EndTime4, FIND(":", EndTime4) - 1))) + IF(FIND("p", EndTime4) > 0, 12)) * 60 + VALUE(MID(EndTime4, FIND(":", EndTime4) + 1, 2))) - ((IF(LEFT(StartTime4, FIND(":", StartTime4) - 1) = "12", IF(OR(FIND("a", StartTime4) > 0, FIND("p", StartTime4) > 0), 0, 12), VALUE(LEFT(StartTime4, FIND(":", StartTime4) - 1))) + IF(FIND("p", StartTime4) > 0, 12)) * 60 + VALUE(MID(StartTime4, FIND(":", StartTime4) + 1, 2)))) / 60) * 60)
Use Case: Useful for time tracking in a case where HH:MM format is preferred
Hope this helps
-
I really disappointed that we won't be able to display time of day with the feature update. AS Monika says above, duration is great but we start and finish times to schedule work during days. We've been holding out on signing up because, without this feature, we can't use your otherwise terrific tool. Is it really that difficult to add what would simply be another field for time of day in addition to date?
-
Robin - if our only choice is to use two columns, we would do so. We're just trying to keep the number of columns we use to a minimum as we already have close to 70 or more columns in our master sheet. ;-)
-
I'd like to see this enhancement as well. We use task sheets to track content and sometimes it helps to know when it should go live.
My work around is a column where I manually enter the time of day.
-
Bumping this... are we close?
-
Time columns showing start and end time is much wanted. This and then setting a rule for formatting say text goes red when a time is passed and tickbox is not clicked solves our need. You then need to combine date and time into the rule. Then we can sort by day, start time and end time and calculate full duration in days, hours & minutes.
-
Hi everyone! I am happy to announce the ability to set partial day duration (hours, minutes, and seconds) is here! Check out my announcement for more information and to ask questions: https://community.smartsheet.com/announcement/new-smartsheet-critical-path-and-duration-minutes-hours-and-weeks
-
Ok - I have played around with the new hours/minutes features and they are completely useless in my use case, so I'm with the group here.
I want to use SmartSheet for event specifications and need to be able to sort by start/end times. When this is a text field, it sorts on the first digit rather than recognizing AM/PM or that 10:00 comes after 8:00. Doesn't work in Military time either because 1200 always comes before 600 (1 v 6).
Here are my two use cases that would make you a solid platform for event planners:
1. Having TIME (AM/PM or even military time) that you can sort. Being able to add/subtract time in hours or minutes would be an added bonus, but if I could atleast add my event start times be able to sort in chronological order, I would be ecstatic.
2. I want to be able to take a list of events (think of them as tasks), have a "Asset" column (in my case, a room name) and have the itemized events show up on a schedule/timeline so I can see when an Asset (Room) is available or double booked. So a start time/end time that could be used in a Gantt chart to create an asset allocation grid. For planners, this means that my room is occupied from Start time to End time - not that I have a person at 150% utilization but don't know when.
-
Here's a very small snapshot of how our team uses the text/number column for time. This is to keep track of when we'll be in the studio to record an interview that will air at a later date.
As you can see, when there are two events, or more, on the same date there's not a functionality to determine in which order they should appear.I'm unable to do military time as we work on a daytime schedule and it would cause confusion to do otherwise. ;-)
Hopefully the time stamp column will allow an am/pm designation, as well.
Blessings,
Loann
-
Dear All ,
is that feature active the time format ?
-
OMH - check out this announcement regarding our last release which included partial day durations: https://community.smartsheet.com/announcement/new-smartsheet-critical-path-and-duration-minutes-hours-and-weeks
-
Any news on implementation of time calculation???
regards
Reiner
-
Reiner, time formulas are currently being looked into by our product team but I dont have any information on which this might be released. Keep checking our product roadmap for information on what we are working on: www.smartsheet.com/product-roadmap
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.2K Get Help
- 419 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 142 Just for fun
- 58 Community Job Board
- 462 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 299 Events
- 38 Webinars
- 7.3K Forum Archives
