MAX Function not returning time stamp

Stephanie Lapera

Source sheet contains a system generated [date submitted] column that shows date and time in the cell which is populated when someone fills out a form.

I am pulling the most recent submission to a destination sheet using the MAX function.

In the destination sheet I am trying to find the max date =MAX({Date Submitted}) but the cell is only showing the date not the time. I need it to pull the MAX date based on both date and time because there could be multiple submissions on the same day to the source sheet and I need it to pull the most recent one at all times.

Any help would be appreciated. Thank you in advance.

Paul Newcome

The + "" should be outside of the function.

=MAX({Date Submitted}) + ""

Paul Newcome

You would have to add some empty quotes to the end and put it in a text/number type column. The downside is that this is now a text value and won't work for future date based formulas.

=MAX(...........) + ""

Stephanie Lapera

This formula =MAX({Date Submitted} + "") is giving me Invalid Operation with both a Date Column and Text/Number Column

Paul Newcome

The + "" should be outside of the function.

=MAX({Date Submitted}) + ""

Stephanie Lapera

Thank you! That worked!

Stephanie Lapera

So now I am seeing the downside because I can't MATCH the date column on the destination sheet - Date Submitted Request - to the date column on the source sheet - Date Submitted - because one is text and one is a date.

=INDEX({Source Sheet 1}, MATCH([Date Submitted Request]@row, {Date Submitted}, 0))

This formula only works if both source and destination columns remain as dates. I will have multiple entries on the same date in the source sheet so it will only pull one and then stop. Is there a workaround to using the MAX function? or a better way to match if the Date Column is the only column that can be matched?

Paul Newcome

You could incorporate a helper column or two.

If you are using a system generated date/timestamp column on your source sheet, I would start by inserting a text/number column and using this formula to pull the timestamp into a number:

=IF(LEN((VALUE(MID(Timestamp@row, FIND(" ", Timestamp@row) + 1, FIND(":", Timestamp@row) - (FIND(" ", Timestamp@row) + 1))) + IF(AND(FIND("P", Timestamp@row)> 0, VALUE(MID(Timestamp@row, FIND(" ", Timestamp@row) + 1, FIND(":", Timestamp@row) - (FIND(" ", Timestamp@row) + 1))) <> 12), 12)) + MID(Timestamp@row, FIND(":", Timestamp@row) + 1, 2)) = 3, "0") + (VALUE(MID(Timestamp@row, FIND(" ", Timestamp@row) + 1, FIND(":", Timestamp@row) - (FIND(" ", Timestamp@row) + 1))) + IF(AND(FIND("P", Timestamp@row)> 0, VALUE(MID(Timestamp@row, FIND(" ", Timestamp@row) + 1, FIND(":", Timestamp@row) - (FIND(" ", Timestamp@row) + 1))) <> 12), 12)) + MID(Timestamp@row, FIND(":", Timestamp@row) + 1, 2)

Next we pull the date as yyyymmdd and attach the number generated by the formula above.

=VALUE(YEAR(DATEONLY(Timestamp@row)) + "" + IF(MONTH(DATEONLY(Timestamp@row))< 10, "0") + MONTH(DATEONLY(Timestamp@row)) + IF(DAY(DATEONLY(Timestamp@row))< 10, "0") + DAY(DATEONLY(Timestamp@row))) + above_formula

=VALUE(VALUE(YEAR(DATEONLY(Timestamp@row)) + "" + IF(MONTH(DATEONLY(Timestamp@row))< 10, "0") + MONTH(DATEONLY(Timestamp@row)) + IF(DAY(DATEONLY(Timestamp@row))< 10, "0") + DAY(DATEONLY(Timestamp@row))) + IF(LEN((VALUE(MID(Timestamp@row, FIND(" ", Timestamp@row) + 1, FIND(":", Timestamp@row) - (FIND(" ", Timestamp@row) + 1))) + IF(AND(FIND("P", Timestamp@row)> 0, VALUE(MID(Timestamp@row, FIND(" ", Timestamp@row) + 1, FIND(":", Timestamp@row) - (FIND(" ", Timestamp@row) + 1))) <> 12), 12)) + MID(Timestamp@row, FIND(":", Timestamp@row) + 1, 2)) = 3, "0") + (VALUE(MID(Timestamp@row, FIND(" ", Timestamp@row) + 1, FIND(":", Timestamp@row) - (FIND(" ", Timestamp@row) + 1))) + IF(AND(FIND("P", Timestamp@row)> 0, VALUE(MID(Timestamp@row, FIND(" ", Timestamp@row) + 1, FIND(":", Timestamp@row) - (FIND(" ", Timestamp@row) + 1))) <> 12), 12)) + MID(Timestamp@row, FIND(":", Timestamp@row) + 1, 2))

Now you should have your date/time converted to yyyymmddhhmm as a numerical value. This should allow you to run your MAX function and include the times.