#INVALID Value when converting Hours into Number

Sepi Deh

Hello,

Good day to you.I have used the below formula for our attendance sheet and it works well!!

I just had one concern, when converting the hours into no. we get #INVALID Value for some of the rows higher than 10. Please see the attached image.

Would you please assist us to overcome this issue.

Many thanks.

=INT((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) / 60) + ":" + IF(((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) - INT((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) / 60) * 60) < 10, "0") + ((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) - INT((((IF(LEFT(EndTime1, FIND(":", EndTime1) - 1) = "12", IF(OR(FIND("a", EndTime1) > 0, FIND("p", EndTime1) > 0), 0, 12), VALUE(LEFT(EndTime1, FIND(":", EndTime1) - 1))) + IF(FIND("p", EndTime1) > 0, 12)) * 60 + VALUE(MID(EndTime1, FIND(":", EndTime1) + 1, 2))) - ((IF(LEFT(StartTime1, FIND(":", StartTime1) - 1) = "12", IF(OR(FIND("a", StartTime1) > 0, FIND("p", StartTime1) > 0), 0, 12), VALUE(LEFT(StartTime1, FIND(":", StartTime1) - 1))) + IF(FIND("p", StartTime1) > 0, 12)) * 60 + VALUE(MID(StartTime1, FIND(":", StartTime1) + 1, 2)))) / 60) * 60)

J. Craig Williams

If anyone tackles this one, when the OP says "above formula", she is referring to this thread:

https://community.smartsheet.com/discussion/using-start-and-end-time-columns-calculate-hours-worked-timesheet-page

You'll notice there that Travis embedded his Sheet, not an image or gif. His Sheet is also showing the #INVALID VALUE, which leads me to suspect something has changed on the back-end as this would have been a topic of conversation if that were the case in March 2016 or any of the succeeding times the thread was updated. 

The #INVALID VALUE may be a new Smartsheet bug and I suggest reporting it as such.

Lastly, you'll notice in that thread (read the whole thread) that I had a lot to say about why this was not the best solution. I won't touch it (this solution) again, at least not on the Community in September. See my comment there about "beautiful and horrible". This update is an example of the "horrible" part.

Craig

 

Paul Newcome

1. The formula you are using is based off of a 12 hour clock. There is a lot built into it that is completely unnecessary for you since you are using a 24 hour clock.

 

2. It looks like there error is coming into play whenever you are converting anything that is not between 0 and 10.99 hours when converted to the decimal.

 

If you are willing to use some helper columns (which can be hidden after populating), we can break this down into a handful of formulas that will give you the end result you are looking for in a much less breakable format.

 

I will use 10 columns for this example. 6 of which are helper columns. I am keeping the names simple for this example, but of course you can change them to whatever you would like/need. Here is the breakdown:

 

1: "Start" - The column you use for your starting time

 

2: "Finish" - The column you use for your ending time

 

3: "Total" - The 7th column in your screenshot will contain the following formula to give you an hh:mm total time:

=JOIN(Hour@row:Minute@row, ":")

 

4: "TotalO" - The 8th column in your screenshot will contain the following formula to give you the same total in the same format:

=IFERROR(Hour@row + "." + MID(Minute@row / 60, FIND(".", Minute@row / 60) + 1, 2), Hour@row)

 

5: "StartH" - The following formula will pull out the hours from the Start column:

=IF(FIND("0", LEFT(Start@row, 2)) = 1, VALUE(SUBSTITUTE(LEFT(Start@row, 2), "0", "")), VALUE(LEFT(Start@row, 2)))

 

6: "StartM" - The following formula will pull out the minutes from the Start column:

=VALUE(RIGHT(Start@row, 2))

 

7: "FinishH" - Same as the StartH column except for the finish time:

=IF(FIND("0", LEFT(Finish@row, 2)) = 1, VALUE(SUBSTITUTE(LEFT(Finish@row, 2), "0", "")), VALUE(LEFT(Finish@row, 2)))

 

8: "FinishM" - Same as the StartM column except for the finish time:

=VALUE(RIGHT(Finish@row, 2))

 

9: "Hour" - Will be the total number of hours:

=IF(FinishH@row - StartH@row < 0, 24 + (FinishH@row - StartH@row), FinishH@row - StartH@row)

 

10: "Minute" - Will be the total number of minutes:

=IF(LEN(IF(FinishM@row < StartM@row, 60 + FinishM@row - StartM@row, FinishM@row - StartM@row)) = 1, "0" + IF(FinishM@row < StartM@row, 60 + FinishM@row - StartM@row, FinishM@row - StartM@row), IF(FinishM@row < StartM@row, 60 + FinishM@row - StartM@row, FinishM@row - StartM@row))

 

I suggest renaming the current two columns used and creating the new columns to match the names exactly in the formulas to make sure all of the formulas get entered correctly. You can then rename the columns to whatever you want, and the formulas will automatically update. This set of formulas can be used with or without the : in the time fields.

Sepi Deh

Hello Paul,

Thanking you for sending us the above formulas.

We have followed the same steps but we've encountered some errors. I have highlighted them for you.

For Time-Out Hour we get the value as 1. We have manually changed it to 18 but still the Total Hours is wrong.

Kindly assist.

Paul Newcome

For the Sunday error... I do need to tweak the formula a bit, but I do know what is wrong. I will get that to you A.S.A.P.

 

Thursday is correct though. 0802 - 1602 is 8 hours.

Paul Newcome

Put this in the Hour column:

 

=IF(FinishH@row - StartH@row < 0, 24 + (FinishH@row - StartH@row), IF(StartM@row > FinishM@row, (FinishH@row - StartH@row) - 1, FinishH@row - StartH@row))

 

To take care of the error when no hours were worked, Change the formula in the Total column to:

 

=IFERROR(JOIN(Hour@row:Minute@row, ":"), "0:00")

 

That should fix the issue for you. I used some of your times as examples and for testing as well. See below.

Sepi Deh

Hello Paul,

Thanks, problem is resolved now

in order to calculate daily wage of an employee, we have multiplied the no of Hours by hourly wage.

we have also converted min by multiplying into hourly wage divided by 60.

But when the min is zero we get an error.

is it because we are multiplying zero with a no.?

Please check attached image.

Paul Newcome

You should be using the column titled Minutes. In the screenshot you provided, there is 1 minute which should be hourly wage divided by 60. Either way... Multiplying by 0 should return 0 and not an error.

 

I would recommend simply multiplying the hourly wage by the value in the corresponding row of the "total o" column. For example, if someone worked from 0800 - 0830, that is 30 minutes or half an hour. Their hourly wage is $20.00. The "total o" column should display "0.5". Those two columns multiplied together will give you the same result without having the extra 2 columns to break down hourly wage and minute wage.

 

To maintain your layout though, you would multiply the PayDayHour column by the Hour column and the PayDayMinute column by the minute column.

 

The screenshot you provided should not be throwing an error unless there is an issue with a formula itself. If you would like, post the formulas you have in the 2 cells that are giving you the error message, and I will take a look at them.

Sepi Deh

I have tried multiplying "total o" with "Hour Wages" and I get invalid operation.

Please see the screenshot.

Paul Newcome

Ah. Easy fix. Because of the way Smartsheet works in the background, any number that is derived from a formula needs to first be wrapped in a VALUE statement before it can be used as an actual number in calculations. Weird I know, but this should do the trick...

 

=VALUE([total o]@row) * [Hour Wage]@row

Sepi Deh

Thanks.

Works well

 

Paul Newcome

Excellent. Happy to help.