Time Formula - Drop Down Selection

2»

Comments

  • Luke,

    What type of workaround would you suggest? I believe this is week 2 for us using SmartSheet and adapting to the function or lack of functions. 

  • L_123
    L_123 ✭✭✭✭✭✭

    https://app.smartsheet.com/b/publish?EQBCT=dcf71ecb7f184467ab8f5debb68a04da

     

    Didn't realize this was only your second week. This is probably one of the harder problems in smartsheet that I have come across (we are at around 6 months right now)

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 01/26/18

    I changed it a little bit after I found another little glitch if the time was 12:00.I recommend you simply save a copy of the sheet so you have a copy of it for yourself. just keep it as a template or something. I was the one who made it and it would take me quite a bit to rewrite all that. for someone reading through this trying to figure out how to do the same thing the formulas and explanation are below.

    There are 5 columns. 

    (Input) (DateEnd) (TimeEnd) (Manual End Min) (Manual End Time Down)

    Their properties are as below:

    Input - Auto Created Time Column

    DateEnd - Date Column

    TimeEnd- Text Column

    Manual End Min - Text Column

    Manual End Time Down - Text Column

    The date/time is auto-created in the auto created column. The DateEnd and TimeEnd is user input. the Manual End Min column contains a formula that converts the amount of time between the auto-created column and the date/time user input columns. The Formula in the minutes column is:

    =(NETDAYS(Input@row, DateEnd@row) - 1) * (24 * 60) + IF(RIGHT(TimeEnd@row, 2) = "PM", 12 * 60, 0) + VALUE(LEFT(TimeEnd@row, FIND(":", TimeEnd@row) - 1)) * 60 + VALUE(RIGHT(LEFT(TimeEnd@row, FIND(":", TimeEnd@row) + 2), 2)) - (IF(RIGHT(Input@row, 2) = "PM", 720, 0) + VALUE(RIGHT(LEFT(Input@row, FIND(":", Input@row) - 1), LEN(Input@row) - 15)) * 60 + VALUE(LEFT(RIGHT(Input@row, 5), 2)))

    This is then converted into a day hour minute format in the Manual End Time Down column. It outputs the result as 1D1H1M. The formula is below.

    =ROUND((([Manual End Minutes]@row / 60) / 24) - 0.5) + "D" + (ROUND(([Manual End Minutes]@row / 60) - 0.5) - ROUND((([Manual End Minutes]@row / 60) / 24) - 0.5) * 24) + "H" + (([Manual End Minutes]@row - (ROUND((([Manual End Minutes]@row / 60) / 24) - 0.5) * 60 * 24)) / 60 - ROUND((([Manual End Minutes]@row - (ROUND((([Manual End Minutes]@row / 60) / 24) - 0.5) * 60 * 24)) / 60) - 0.5)) * 60 + "M"

     

  • Rockee
    Rockee
    edited 01/26/18

    Fantastic Luke, I will work on this today. 

     

    Edit: How do you save a smartsheet copy? I see the option to export to Excel but that would change the formulas?

  • Alrighty, after throwing my computer, I feel a little better. However, I am unable to get your formula to work.  

     

    I see that one was a simple typo Minutes was used instead of Min. 

     

    Link: https://app.smartsheet.com/b/publish?EQBCT=40fa50833b74401aab5f0d7b99269ac5

    I have a bunch of columns but the ones for the formula match up. 

  • L_123
    L_123 ✭✭✭✭✭✭

    I'm not sure i'm looking at it right, but I think you are referencing "Input" rather than "Start Date and Time" in your formula. The -3 and -17 are correct if you take that into consideration.

  • L_123
    L_123 ✭✭✭✭✭✭

    Right click the tab and click save as should let you save a copy.

  • Correct - Was referring to it but when changing it to my "Start Date and Time" it doesn't parse correctly, I'm guessing because of what you said earlier about using NETDAYS and Auto Generation of Date and Time. I'm unable to use that as the user may create the form 12 hours after the job has finished. 

  • L_123
    L_123 ✭✭✭✭✭✭
    edited 01/26/18

    So you will have to split the start date and time into two separate columns as well. If you look at my sheet I have the end time and date separated, and I don't parse them together. I simply have the date in a date column and the time in a text column. 

    https://app.smartsheet.com/b/publish?EQBCT=dcf71ecb7f184467ab8f5debb68a04da

    (I went ahead and changed it. Nothing on the right side of the black line calls anything on the left and vice versa)

  • L_123
    L_123 ✭✭✭✭✭✭

    I'm working on the 60 min thing at the moment. I'll let youknow what I end up with.

  • L_123
    L_123 ✭✭✭✭✭✭

    fixed the issue it was with the rounding.

  • Thanks for all your help Luke; it's working now. :D 

  • L_123
    L_123 ✭✭✭✭✭✭

    Not a problem! Good luck. That was a lot more painful to build than I remembered. Luckily I have a quite a few sheets that I think can use the same functionality so I needed to build it too :D

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!