Time Formula - Drop Down Selection

Rockee

Hello,

I've searched and it appears the time formula is an ongoing battle for enhancement requests. Looking for some help, I have two columns; 'Start Time:' and 'End Time'. There is a drop-down box on the web form and sheet that starts at 12:00 AM and ends at 11:55 PM, all increments of 5 minutes. For example, 12:00 AM, 12:05 AM, etc...

I've used the ginormous formula:

=IF([Start Time:]1 <> "", INT((((IF(LEFT([End Time:]1, FIND(":", [End Time:]1) - 1) = "12", IF(OR(FIND("a", [End Time:]1) > 0, FIND("p", [End Time:]1) > 0), 0, 12), VALUE(LEFT([End Time:]1, FIND(":", [End Time:]1) - 1))) + IF(FIND("p", [End Time:]1) > 0, 12)) * 60 + VALUE(MID([End Time:]1, FIND(":", [End Time:]1) + 1, 2))) - ((IF(LEFT([Start Time:]1, FIND(":", [Start Time:]1) - 1) = "12", IF(OR(FIND("a", [Start Time:]1) > 0, FIND("p", [Start Time:]1) > 0), 0, 12), VALUE(LEFT([Start Time:]1, FIND(":", [Start Time:]1) - 1))) + IF(FIND("p", [Start Time:]1) > 0, 12)) * 60 + VALUE(MID([Start Time:]1, FIND(":", [Start Time:]1) + 1, 2)))) / 60) + ":" + IF(((((IF(LEFT([End Time:]1, FIND(":", [End Time:]1) - 1) = "12", IF(OR(FIND("a", [End Time:]1) > 0, FIND("p", [End Time:]1) > 0), 0, 12), VALUE(LEFT([End Time:]1, FIND(":", [End Time:]1) - 1))) + IF(FIND("p", [End Time:]1) > 0, 12)) * 60 + VALUE(MID([End Time:]1, FIND(":", [End Time:]1) + 1, 2))) - ((IF(LEFT([Start Time:]1, FIND(":", [Start Time:]1) - 1) = "12", IF(OR(FIND("a", [Start Time:]1) > 0, FIND("p", [Start Time:]1) > 0), 0, 12), VALUE(LEFT([Start Time:]1, FIND(":", [Start Time:]1) - 1))) + IF(FIND("p", [Start Time:]1) > 0, 12)) * 60 + VALUE(MID([Start Time:]1, FIND(":", [Start Time:]1) + 1, 2)))) - INT((((IF(LEFT([End Time:]1, FIND(":", [End Time:]1) - 1) = "12", IF(OR(FIND("a", [End Time:]1) > 0, FIND("p", [End Time:]1) > 0), 0, 12), VALUE(LEFT([End Time:]1, FIND(":", [End Time:]1) - 1))) + IF(FIND("p", [End Time:]1) > 0, 12)) * 60 + VALUE(MID([End Time:]1, FIND(":", [End Time:]1) + 1, 2))) - ((IF(LEFT([Start Time:]1, FIND(":", [Start Time:]1) - 1) = "12", IF(OR(FIND("a", [Start Time:]1) > 0, FIND("p", [Start Time:]1) > 0), 0, 12), VALUE(LEFT([Start Time:]1, FIND(":", [Start Time:]1) - 1))) + IF(FIND("p", [Start Time:]1) > 0, 12)) * 60 + VALUE(MID([Start Time:]1, FIND(":", [Start Time:]1) + 1, 2)))) / 60) * 60) < 10, "0") + ((((IF(LEFT([End Time:]1, FIND(":", [End Time:]1) - 1) = "12", IF(OR(FIND("a", [End Time:]1) > 0, FIND("p", [End Time:]1) > 0), 0, 12), VALUE(LEFT([End Time:]1, FIND(":", [End Time:]1) - 1))) + IF(FIND("p", [End Time:]1) > 0, 12)) * 60 + VALUE(MID([End Time:]1, FIND(":", [End Time:]1) + 1, 2))) - ((IF(LEFT([Start Time:]1, FIND(":", [Start Time:]1) - 1) = "12", IF(OR(FIND("a", [Start Time:]1) > 0, FIND("p", [Start Time:]1) > 0), 0, 12), VALUE(LEFT([Start Time:]1, FIND(":", [Start Time:]1) - 1))) + IF(FIND("p", [Start Time:]1) > 0, 12)) * 60 + VALUE(MID([Start Time:]1, FIND(":", [Start Time:]1) + 1, 2)))) - INT((((IF(LEFT([End Time:]1, FIND(":", [End Time:]1) - 1) = "12", IF(OR(FIND("a", [End Time:]1) > 0, FIND("p", [End Time:]1) > 0), 0, 12), VALUE(LEFT([End Time:]1, FIND(":", [End Time:]1) - 1))) + IF(FIND("p", [End Time:]1) > 0, 12)) * 60 + VALUE(MID([End Time:]1, FIND(":", [End Time:]1) + 1, 2))) - ((IF(LEFT([Start Time:]1, FIND(":", [Start Time:]1) - 1) = "12", IF(OR(FIND("a", [Start Time:]1) > 0, FIND("p", [Start Time:]1) > 0), 0, 12), VALUE(LEFT([Start Time:]1, FIND(":", [Start Time:]1) - 1))) + IF(FIND("p", [Start Time:]1) > 0, 12)) * 60 + VALUE(MID([Start Time:]1, FIND(":", [Start Time:]1) + 1, 2)))) / 60) * 60), "")

This worked great UNTIL I changed it to AM / PM as my employees are struggling with military time. Any suggestions or have there been any improvements that I may have missed searching?

Secondly, is there a simple way to convert the increments when totaling the difference into decimal for the minutes?  Example: 7:05 PM - 10:00 PM converts to 2.92? 

Regards,

Rockee

L_123

It is much easier to convert to minutes then output the hours.

Something like

IF(Right(Col1,2)="PM",12*60,0)+value(left(col1,2))*60+value(left(right(col1,5),2)

would be the minutes for one number. (assuming double digit hours. I didn't want to take the time to use len to correctly parse it, and you've already show you know how to do that.) 

use the same formula to get the minutes for the other number and then subtract them. Then convert back into time.

L_123

Realized I would only have to Parse the hours, so I went ahead and did it.

=IF(RIGHT([Start Time]1, 2) = "PM", 60 * 12, 0) + (VALUE(LEFT([Start Time]1, VALUE(LEN([Start Time]1) - 6))) * 60) + VALUE(LEFT(RIGHT([Start Time]1, 5), 2))

Rockee

Thanks for your reply Luke. I've tried your formula in a "Calculation" column and it gives an error: #UNPARSEABLE - I'll keep messing with it and see if I can get it going. You'd think something that is so simple on other platforms (Google Sheets and Excel) would be a no-brainer with Smartsheet. 

L_123

What is the format of the time you are putting in?

 

10:15 PM is what I used for that formula I just realized you have a ":" at the end of yours. That's probably the issue

L_123

Give that a Try

 

=IF(RIGHT([End Time:]1, 2) = "PM", 60 * 12, 0) + (VALUE(LEFT([End Time:]1, VALUE(LEN([End Time:]1) - 6))) * 60) + VALUE(LEFT(RIGHT([End Time:]1, 5), 2)) - (IF(RIGHT([Start Time:]1, 2) = "PM", 60 * 12, 0) + (VALUE(LEFT([Start Time:]1, VALUE(LEN([Start Time:]1) - 6))) * 60) + VALUE(LEFT(RIGHT([Start Time:]1, 5), 2)))

L_123

=ROUND(MinDiff1 / 60) + "H" + (MinDiff1 / 60 - ROUND(MinDiff1 / 60)) * 60 + "M"

 

This formula takes a minute output in column "MinDiff" row 1 and converts it to hours and minutes.

I would use a hidden helper column post the other formula to it, reference it, then hide it to get your number. 

 

Lol got me interested. I had to build this once before, and had forgotten some of it.

 

Edited: Cleaned up the formula @1:53

Rockee

This formula seems to be off time wise when putting in minutes (7:25 AM - 9:00 PM).

=ROUND(MinDiff1 / 60) + "H" + (MinDiff1 / 60 - ROUND(MinDiff1 / 60)) * 60 + "M" 

I ended up just taking the duration; =IF(RIGHT([End Time:]1, 2) = "PM", 60 * 12, 0) + (VALUE(LEFT([End Time:]1, VALUE(LEN([End Time:]1) - 6))) * 60) + VALUE(LEFT(RIGHT([End Time:]1, 5), 2)) - (IF(RIGHT([Start Time:]1, 2) = "PM", 60 * 12, 0) + (VALUE(LEFT([Start Time:]1, VALUE(LEN([Start Time:]1) - 6))) * 60) + VALUE(LEFT(RIGHT([Start Time:]1, 5), 2))) and dividing by 60 which also gives me the minutes in decimals. My last issue is to figure out what to do if they end a job a midnight as none of the formulas like that and turn it into negative time.  

Thanks for all of your help. 

L_123

I forgot to subtract .5 from the rounds to make sure it rounds down. That should correct the issue i would think.

I Went back to an old sheet and reviewed it and edited it. It is a little deeper than this one as it adds days as well, which could be an option for your midnight problem. (honestly just rename your columns copy paste the formulas, then name your columns back) this way you could track days as well as time. There is a little glitch I haven't figured out where is there is less than a minute between the start and stop it goes to -23H but I don't think that's a huge deal. I just used conditional formatting to make it blank if that happens. There's not really any point in tracking anything less than 1 min in smartsheet.

 

Click the check boxes then save to refresh the row. i've got a personal copy of the sheet so don't worry about messing it up.

 

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

Rockee

Awesome Luke!

That gave me some ideas! I did some error checking and then JOINed the Date and Time, changed the column names to match yours and I'm getting an error - #INVALID DATA TYPE. See anything I may have missed (attached screen shot).

L_123

can you post the equation in a response? Also, check the column properties and make sure it is text not date.

Rockee

Had one column (Edited) that was set to Date, although that didn't change the output in the "Minutes" column.

 

Using your formula:

=(NETDAYS(Input1, Edited1) - 1) * (24 * 60) + IF(RIGHT(Edited1, 2) = "PM", 12 * 60, 0) + VALUE(IF(LEN(Edited1) = 17, RIGHT(LEFT(Edited1, 11), 2), RIGHT(LEFT(Edited1, 10), 1))) * 60 + VALUE(LEFT(RIGHT(Edited1, 5), 2)) - (IF(RIGHT(Input1, 2) = "PM", 12 * 60, 0) + VALUE(IF(LEN(Input1) = 17, RIGHT(LEFT(Input1, 11), 2), RIGHT(LEFT(Input1, 10), 1))) * 60 + VALUE(LEFT(RIGHT(Input1, 5), 2)))

 

Link: 

http://publish.smartsheet.com/d95c669fb5c442cda043febc544ae757

L_123

Ah I had a brainfart and was thinking you were on the ending calc. You need to use automod columns to get netdays to work. If you look at mine, one is created, and the other is modified. Otherwise the system doesn't recognize that the information is a date.

Rockee

Hmm, is there a workaround to get it to recognize the date/time? Your formula is exactly what I'm trying to achieve because I can convert the Duration to Hours and Minutes in Decimal format, plus I like the Date/Time format better as well. The caveat is all of this is coming from a web form and the data might be put in a day(s) later depending on how long they worked.

L_123

I would take away the date modified column and split it into a date and time column. You will have to modify the formula a little bit though. use a date column, and have them input the day and time separately in the web form.

Rockee

Thanks, that is how it is now, I just hid the date and time columns to show one column that uses the JOIN function.