Time Formula - Drop Down Selection

Rockee

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

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

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

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?

Rockee

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

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

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

Rockee

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

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

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

L_123

fixed the issue it was with the rounding.

Rockee

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

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