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([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.
-
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?
-
@[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
Bad habit of mine
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
