Need a Column Property type that is "clock" or "time of day"

Francine Sneddon

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. 

 

 

Michael Stadulis

I was told this was almost completed months ago.  

Kara Lumley

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 

Rob Jacobs

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? 

Loann McGee

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. ;-)

EMR

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.

Neil Staite

Bumping this... are we close?

Knut Anders Gjersøe

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.

 

Travis

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

 

Apryl Alexander-Savino

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. 

Loann McGee

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

OMH

Dear All , 

 

is that feature active the time format ? 

Travis

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

Reiner Sackmann

Any news on implementation of time calculation???

 

regards

Reiner

Travis

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