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"

Options
2456

Comments

  • Francine Sneddon
    Francine Sneddon ✭✭
    edited 04/06/15
    Options

    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. 

     

     

    · Share on FacebookShare on Twitter
  • Michael Stadulis
    Michael Stadulis ✭
    Options

    I was told this was almost completed months ago.  

    · Share on FacebookShare on Twitter
  • Kara Lumley
    Kara Lumley Employee
    edited 01/11/16
    Options

    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 Smile

    · Share on FacebookShare on Twitter
  • Rob Jacobs
    Rob Jacobs ✭
    Options

    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? 

    · Share on FacebookShare on Twitter
  • Loann McGee
    Loann McGee ✭✭✭✭✭✭
    edited 04/21/15
    Options

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

    · Share on FacebookShare on Twitter
  • EMR
    EMR ✭
    Options

    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.

    · Share on FacebookShare on Twitter
  • Neil Staite
    Neil Staite ✭
    edited 06/11/15
    Options

    Bumping this... are we close?

    · Share on FacebookShare on Twitter
  • Knut Anders Gjersøe
    Knut Anders Gjersøe ✭
    edited 06/14/15
    Options

    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.

     

    · Share on FacebookShare on Twitter
  • Travis
    Travis Employee
    Options

    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

     

    · Share on FacebookShare on Twitter
  • Apryl Alexander-Savino
    Apryl Alexander-Savino ✭
    edited 07/21/15
    Options

    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. 

    · Share on FacebookShare on Twitter
  • Loann McGee
    Loann McGee ✭✭✭✭✭✭
    edited 03/04/16
    Options

    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

    Time Column.PNG

    Time Schedule.JPG

    · Share on FacebookShare on Twitter
  • OMH
    OMH ✭
    Options

    Dear All , 

     

    is that feature active the time format ? 

    · Share on FacebookShare on Twitter
  • Travis
    Travis Employee
    edited 08/14/15
    Options

    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

    · Share on FacebookShare on Twitter
  • Reiner Sackmann
    Reiner Sackmann ✭✭
    Options

    Any news on implementation of time calculation???

     

    regards

    Reiner

    · Share on FacebookShare on Twitter
  • Travis
    Travis Employee
    Options

    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

    · Share on FacebookShare on Twitter
This discussion has been closed.

Categories