Formulas for Calculating Time
Comments

Thanks so much for the clarification on the formula. It makes so much sense. But somehow this is what I get as an answer? I might be doing something wrong.
This is the return value that I get when I use the following formula:
=(VALUE(LEFT([TimeOut]@row; 2) + (VALUE(RIGHT([TimeOut]@row; 2)) / 60))  (VALUE(LEFT([TimeIn]@row; 2) + (VALUE(RIGHT([TimeIn]@row; 2)) / 60))))
Not sure if I am missing something here. Or if I might have a bracket in the wrong place.
Any assistance will be appreciated.
Have a great day.
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Michelle BassonSmartsheet Overachiever Alumni  Solution Consultant  Lover of everything Smartsheet
https://www.linkedin.com/in/michellebasson/CERTIFIED SMARTSHEET PLATINUM PARTNER
www.projectplanservices.co.uk 
Your parenthesis are fairly off
=VALUE(LEFT([TimeOut]@row; 2)) + (VALUE(RIGHT([TimeOut]@row; 2)) / 60)  VALUE(LEFT([TimeIn]@row; 2)) + (VALUE(RIGHT([TimeIn]@row; 2)) / 60
*Didn't test this, I might have some errors as well.

This one seems to work. I've added that a hour be deducted for lunch.
Now I just need to get a way to get the #invalid value to return zero. instead of the error if no information is added in the columns. Because not everybody will work on a Saturday or Sunday, but I cannot sum the values for the month if there is an error.
Any thoughts?
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Michelle BassonSmartsheet Overachiever Alumni  Solution Consultant  Lover of everything Smartsheet
https://www.linkedin.com/in/michellebasson/CERTIFIED SMARTSHEET PLATINUM PARTNER
www.projectplanservices.co.uk 
@L@123 Thanks for stepping in.
@Michelle B You can use an IFERROR.
=IFERROR(original_formula, 0)
=IFERROR(VALUE(LEFT([TimeOut]@row; 2)) + (VALUE(RIGHT([TimeOut]@row; 2)) / 60)  VALUE(LEFT([TimeIn]@row; 2)) + (VALUE(RIGHT([TimeIn]@row; 2)) / 60, 0)

Haha, I was lazy and didn't put the ending parenthesis, so that wouldn't work as is @Paul Newcome
Bad habit of mine
Need 1 more close before you can put the return on the iferror.
=IFERROR(VALUE(LEFT([TimeOut]@row; 2)) + (VALUE(RIGHT([TimeOut]@row; 2)) / 60)  VALUE(LEFT([TimeIn]@row; 2)) + (VALUE(RIGHT([TimeIn]@row; 2)) / 60), 0)

@L@123 I didn't even catch that. There are too many different time formulas for me to be able to pick that up at a glance. I'm glad you caught it.
@Michelle B Take a look at @L@123's latest comment regarding the missing parenthesis. He is absolutely correct, and that formula should now work for you.

Thanks so much for these solutions, exactly what I was looking for!
I have run into an issue though that I was hoping you could help me with?
I used your formulas in the link below but if my result in [Finish Time] should be 12:00pm, it is showing as 0:00pm. I added formula to the Start Column to equal the Finish time so this throws everything off.
Can you let me know how to fix this please?
Formula: =MOD(INT(Finish@row), 12) + ":" + IF((Finish@row  INT(Finish@row)) * 60 < 10, "0") + (Finish@row  INT(Finish@row)) * 60 + IF(Finish@row >= 12, "pm", "am")
Thanks!

@Evelyn Morris Try this...
=IF(MOD(INT(Finish@row), 12) = 0, "12", MOD(INT(Finish@row), 12)) + ":" + IF((Finish@row  INT(Finish@row)) * 60 < 10, "0") + (Finish@row  INT(Finish@row)) * 60 + IF(Finish@row >= 12, "pm", "am")

Hello,
How do I get the leading zeros on the minutes column to show?
Here is the formula that is in use.
=VALUE(MID(Created@row, FIND(":", Created@row) + 1, 2))
Here is the data that's being displayed.

@Danielle Biddy Exactly what are your formulas in the Minutes column and in the Date/Time column?

Hi Paul! I am trying to duplicate a formula from Excel into smartsheet. I am not sure if smartsheet can do it, but I saw these threads and thought it couldn't hurt to ask. I have copied the excel formula below.
=IF(AT3="N/A",0,IF(AT3<=TIMEVALUE("12:30"),17,IF(AT3<=TIMEVALUE("13:00"),12,IF(AT3<=TIMEVALUE("14:00"),4,IF(AT3<=TIMEVALUE("15:00"),1,0.5)))))
I know smartsheet doesn't have the time value function, but I wanted to see if there was something comparable that could get me the same result.
I greatly appreciate the help and let me know if you have any questions!

@Megan Harry You would need to use one of the solutions in this thread to convert your times in the AT column into a number then use that in your comparison.

You can simply use an if statement when you concatenate if i'm understanding your formula correctly.
=if(len(minutes@row) = 1,0,"")+minutes@row

Question about your response my previous question. I have been looking through the solutions in this thread to convert my times into a number, have only been seeing examples in military time. Do I need to input times in military time and then convert to a number? Or is it possible to keep it in non military time?
Thank you!

@Megan Harry There should be a solution that does the 12 hour to 24 hour conversion for you. Take a look at the last comment on the 1st page. It is a formula that should be exactly what you need. Pulling a 12 hour from a timestamp column and converting it to a 24 hour. this is just for the hour portion. The minutes portion would remain the same where we use
VALUE(MID(Timestamp@row, FIND(":", Timestamp@row) + 1, 2)) / 60
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 61.3K Get Help
 321 Global Discussions
 197 Industry Talk
 418 Announcements
 4.2K Ideas & Feature Requests
 127 Brandfolder
 154 Just for fun
 124 Community Job Board
 441 Show & Tell
 26 Member Spotlight
 1 SmartStories
 278 Events
 34 Webinars
 7.3K Forum Archives