Percentage Question

Caroline Dunnell

Hi, I am sure this is simple, but everything I have tried isn't working.

I am working on a time allocation sheet and I need to work out how much of the day is allocated to a certain project:

"Daily Hours" Column - will always be 9:00 (hh:mm)

"Time Allocation" Column - our PM's will add into a column how much time allocated to that job - so for example - 2 hours - 2:00 (hh:mm)

I need it to work out a % of the day to come up, but everything I have tried isn't working and says "invalid Operations" - I know this should be 22.22% but I can't get it working.

Any help will be much appreciated, thanks

Paul Newcome

You will first need to convert the time into a usable number then divide it by 24.

There are a number of solutions for converting hh:mm into a usable number in this thread below:

Formulas for Calculating Time

Paul Newcome

Jun 8, 2020

I have decided to create a post that consolidates all of the different time based solutions that I have put together.

Some of these solutions can be made more efficient or condensed into fewer but larger formulas. I am still going through these working on making them more efficient and tweaking them, but all of them have been used as working solutions.

I thought it would be helpful to have them all in one place instead of spread out.

I welcome all feedback, suggestions, and additions.

I'll start by posting the published sheets to the solutions. If it is a solution to another community post, it will have a hyperlink to the original post which may help with context. Some are 12 hour clocks and others are 24 hour clocks. Some involve dates and others do not.

I will also try to post various tips and tricks as I think of them as well such as:

It is easier to convert the hours for 12 hour times to 24 hour times before attempting to do further calculations:

=VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) + IF(CONTAINS("p", [Time Column]@row), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) <> 12, 12), IF(VALUE(LEFT([Time Column]@row, FIND(":", [Time Column]@row) - 1)) = 12, -12))

Calculating Time Worked for Employees

Can you calculate time in Smartsheet?

Conversion of timezones (Solved Formula Included)

Date/Time/Year

Flagging a Date and Time Overlap

How do I create time of day columns?

Need to create a "shift" column from a time column

---

View Post