Min/Max Time Formula

I am need to calculate the min and max time for a column. These times are entered manually (from forms) in the HH:MM format because created by and modified by columns are not appropriate for the purpose here.

The times are entered into a form.

These times are converted in a new column using the following formula: =TIME([Time Received (FORM)]@row)

I am looking for the min and max times in this calculated column with the following formula:

=MIN([Time Received]:[Time Received])

I am getting "0" as the answer for both MIN and MAX when that is obviously not correct.

Any thoughts?

Find more posts tagged with

Formulas

Time Calculation

Accepted answers

All comments

Paul Newcome

There is a method for converting times into numbers (and then back from numbers into hh:mm format) in the thread below. You will need to convert them into numbers, find the min/max, then convert the min/max back into the appropriate format.

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

BSReid24

@Paul Newcome that is really good information, but I can't get that to work. I don't think I explained it the best: so I am wanting to put a formula into a sheet summary to calculate the "earliest time of day" for a time column.

I looked at what you had and tried the following:

=TIME((MIN(VALUE(LEFT([Time Received]:[Time Received], FIND(":", [Time Received]:[Time Received] - 1)) + (VALUE(RIGHT([Time Received]:[Time Received], 2)) / 60)))))

that gave me the following answer:

#invalid data type

Before I had the simple: =MIN([Time Received]:[Time Received]) and was getting 0 as my answer.

The [Time Received] column is in TIME format (default 24 hrs)

Any thoughts?

Paul Newcome

Right. You need a separate column to output the times as numbers on every row. You would then pull the min/max from this helper column and use a formula to convert it back into the proper format.

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.