# Formulas for Calculating Time

• 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([Time-Out]@row; 2) + (VALUE(RIGHT([Time-Out]@row; 2)) / 60)) - (VALUE(LEFT([Time-In]@row; 2) + (VALUE(RIGHT([Time-In]@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.

Michelle Basson

Smartsheet Overachiever!

• Your parenthesis are fairly off

=VALUE(LEFT([Time-Out]@row; 2)) + (VALUE(RIGHT([Time-Out]@row; 2)) / 60) - VALUE(LEFT([Time-In]@row; 2)) + (VALUE(RIGHT([Time-In]@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?

Michelle Basson

Smartsheet Overachiever!

• @[email protected] Thanks for stepping in.

@Michelle B You can use an IFERROR.

=IFERROR(original_formula, 0)

=IFERROR(VALUE(LEFT([Time-Out]@row; 2)) + (VALUE(RIGHT([Time-Out]@row; 2)) / 60) - VALUE(LEFT([Time-In]@row; 2)) + (VALUE(RIGHT([Time-In]@row; 2)) / 60, 0)

thinkspi.com

• Haha, I was lazy and didn't put the ending parenthesis, so that wouldn't work as is @Paul Newcome

Need 1 more close before you can put the return on the iferror.

=IFERROR(VALUE(LEFT([Time-Out]@row; 2)) + (VALUE(RIGHT([Time-Out]@row; 2)) / 60) - VALUE(LEFT([Time-In]@row; 2)) + (VALUE(RIGHT([Time-In]@row; 2)) / 60), 0)

• @[email protected] 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 @[email protected]'s latest comment regarding the missing parenthesis. He is absolutely correct, and that formula should now work for you.

thinkspi.com

• 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([email protected]), 12) + ":" + IF(([email protected] - INT([email protected])) * 60 < 10, "0") + ([email protected] - INT([email protected])) * 60 + IF([email protected] >= 12, "pm", "am")

Thanks!

• @Evelyn Morris Try this...

=IF(MOD(INT([email protected]), 12) = 0, "12", MOD(INT([email protected]), 12)) + ":" + IF(([email protected] - INT([email protected])) * 60 < 10, "0") + ([email protected] - INT([email protected])) * 60 + IF([email protected] >= 12, "pm", "am")

thinkspi.com

• Hello,

How do I get the leading zeros on the minutes column to show?

Here is the formula that is in use.

=VALUE(MID([email protected], FIND(":", [email protected]) + 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?

thinkspi.com

• 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.

thinkspi.com

• You can simply use an if statement when you concatenate if i'm understanding your formula correctly.

=if(len([email protected]) = 1,0,"")[email protected]

• 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([email protected], FIND(":", [email protected]) + 1, 2)) / 60

thinkspi.com