Time function

I need to record hours in HH:MM in one of the columns of my sheet is there a time format or formula I can use so that entry format is HH:MM . I will not be tracking time in and time out, just the total time worked, for instance, if John Doe worked 7hrs and 20mins, I want to be able to enter 07:20, is this possible?

Find more posts tagged with

Sheets

Formulas

Accepted answers

Paul Newcome

I would suggest inserting an additional helper column for each HH:MM column. The thread above has a solution for converting HH:MM into a useable number. Then you can sum these helper columns, and there is a solution in the above thread that details how to convert a number back into HH:MM format for display.

All comments

Paul Newcome

You would have to manually enter it in that format. There is no "time" type of column or column formatting.

If you wanted to enter your time spent as a number instead such as 7.5 and have it output in a different column as 07:30, then there are a number of formulas to accomplish that.

Regarding the second, take a look through this thread. It has quite a few time based solutions, and I am pretty sure there is a formula tucked away in one of the solutions that will convert 7.5 to 07:30. If you are unable to find something or need help modifying something, feel free to let me know.

https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time

Omotola Ashafa

Thank you @Paul Newcome, this is a great resource. The one thing I am struggling with is the formula to calculate the total hours worked weekly. I will be manually entering the daily hours worked in HH:MM and we need to Sum all those hours for a weekly total as shown below. Can you help with this?

Paul Newcome

Omotola Ashafa

Hi @Paul Newcome

I found multiple threads and was able to come up with the formula for converting useable numbers to HH:MM but I can't find the thread for converting HH:MM to useable number. I must be missing it, can you help please.

Paul Newcome

It is actually tucked into almost every single solution, but I am not sure if there is a "standalone" post that has it pulled out. If it is always a two digit hour then the only additional question would be are you using a 24 or 12 hour format?

Ginny Shoemaker

I'm trying to find a formula that will compare the timestamp to the Shift Start Time to automatically apply the RYG that I have been doing mannually. The Shift Start Time is actually a drop down and they must submit their Health Occupation Form before the beginning of their Shift from home.

Omotola Ashafa

@Paul Newcome It would always be in a 12 hour format but not always a two-digit hour. Actually, it would never be two digit hour since the highest value in hours:minutes that would be entered is 9:00 i.e for 9 hours.

Paul Newcome

@Ginny Shoemaker Please take a look through the thread linked above. There are multiple solutions provided for converting timestamps as well as time entries into useable numbers. You would then compare the two numbers in an IF statement to drive the RYG.

@Omotola Ashafa IF there is no AM/PM and you are only going to have a single digit hour, then your formula would look something like this...

=VALUE(LEFT([Time Column]@row)) + (VALUE(RIGHT([Time Column]@row, 2)) / 60)

Ginny Shoemaker

@Paul Newcome Do I need to get rid of the drop-down and actually have a row for each of the Shift Start Times? I'm not sure I'm 100% understanding the threads above, but I will give it a try.

Ginny Shoemaker

@Omotola Ashafa In reference to the formula you posted above...Do you mean by single digit hour to remove them from a drop down and have a column for each Shift Start Time?

Paul Newcome

@Ginny Shoemaker You don't necessarily need to get rid of the dropdown. The dropdown outputs a text value which is exactly what all of the formulas in the solutions are evaluating. If you only have those 5 selections for the dropdown, you could also use an IF statement to evaluate the dropdown selection and output a number accordingly.

=IF(LEFT([Shift Start Time]@row, 2) = "7:", 7, IF(LEFT([Shift Start Time]@row, 2) = "8:", 8, IF(LEFT([Shift Start Time]@row, 2) = "10", 10, IF(LEFT([Shift Start Time]@row, 2) = "3:", 15, IF(LEFT([Shift Start Time]@row, 2) = "11", 23.5)))))

Ginny Shoemaker

@Paul Newcome Thank you!

Paul Newcome

@Ginny Shoemaker Happy to help. 👍️

Ginny Shoemaker

@Paul Newcome I'm still not sure I completely understand the threads above regarding the time, but I'm going to try and set it up this afternoon.😉

Paul Newcome

@Ginny Shoemaker The linked thread above provides a solution for extracting the time from a timestamp column and converting it into a useable number. Once you have that set up in one column, you would then set up the nested IF in another looking at your dropdown. From there you would use a 3rd column to use an IF statement to drive the RYG.

If you find a solution that you think might work but need help tweaking it to fit your exact needs, please feel free to show me exactly what you are starting with, and I will be happy to help you tweak.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.