How do I create time of day columns?

2»

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @James Elliott Glad you were able to get it figured out.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • mbsamuel6
    mbsamuel6 ✭✭✭✭

    @Paul Newcome

    Hi,


    I'm trying to implement the formulas you have posted here https://app.smartsheet.com/b/publish?EQBCT=e1b1d4c042e84f8b82574cbf378bd767, but I'm getting a few errors.


    For some reason, the formulas are not reading when it comes to "Duration, Years, Months, and Days".


    As seen below in the screenshot its saying "invalid data type". Any reason why? Is it my columns? I have them all set to "Text".


    All I did was copy and paste what you had in the link above and placed it in my smartsheet.


    Thank you!



  • mbsamuel6
    mbsamuel6 ✭✭✭✭

    @Paul Newcome


    Hi,


    I have a similar issue and for some reason its not working on my end.

    All I did was copy from what you have posted here: https://app.smartsheet.com/b/publish?EQBCT=e1b1d4c042e84f8b82574cbf378bd767


    and pasted it my SMARTSHEET. When I do that I am getting "invalid operation" errors as seen below. Any idea why?


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    @mbsamuel6 Double check that your date columns are in fact set as date type columns.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • mbsamuel6
    mbsamuel6 ✭✭✭✭

    Face palm. That did it. Sorry for the double post. I tried to delete it but it wouldn't let me.


    Thank you. :)

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    No worries and happy to help. 👍️


    The way I worked this one was like this...

    I started at the end of the solution. Not working. Error.

    So I look at the formula to see what cell(s) it is referencing. Do any of those have an error? Yup. The one right before it in the chain.

    We take a look at that one. None of the cells it is referencing have an error, so we know the originating error is coming from somewhere in this particular formula.

    So we look at each of the sections of this formula to try to figure out exactly where the problem may be.

    We know that both the End and Start Time Calc formulas are outputting numbers, so we know that isn't the issue.

    The only other place that could cause an issue in this particular formula would be where we subtract the date from the date. We know that if they are both dates then it SHOULD be working, so we start with the simplest check of... Are they in Date type columns?

    If they were date type columns, the next step would be some testing to find out exactly how those dates are being stored on the back-end, but we didn't need to go that far because the column type was the issue and is now fixed allowing everything to work properly.


    Short version... Start at the end of the problem. Trace it back to the beginning of the problem. Start troubleshooting with the easiest things to fix.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • Hello Paul, I read through the entire post on this issue. I copy/pasted all your formulas and the date columns are a date type. I get BLOCKED & UNPARSEABLE. if you have any ideas it would be greatly appreciated.


  • Please disregard...your formulas worked perfectly :) Now I just have to figure out how to modify your formulas to show Total Hours only (not days).

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!