# Formulas for Calculating Time

Options

• Overachievers Alumni
edited 04/21/21
Options

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.

If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Michelle Basson

Smartsheet Overachiever Alumni | Solution Consultant | Lover of everything Smartsheet

CERTIFIED SMARTSHEET PLATINUM PARTNER
www.projectplanservices.co.uk

• ✭✭✭✭✭✭
Options

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

• Overachievers Alumni
Options

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 Basson

Smartsheet Overachiever Alumni | Solution Consultant | Lover of everything Smartsheet

CERTIFIED SMARTSHEET PLATINUM PARTNER
www.projectplanservices.co.uk

• ✭✭✭✭✭✭
Options

@L@123 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)

• ✭✭✭✭✭✭
Options

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)

• ✭✭✭✭✭✭
Options

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

• edited 04/26/21
Options

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!

• ✭✭✭✭✭✭
Options

@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")

• Options

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.

• ✭✭✭✭✭✭
Options

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

• Options

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!

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
Options

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

• Options

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!

• ✭✭✭✭✭✭
Options

@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