Min/Max Time Formula

BSReid24

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?

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.