MAX Function not returning time stamp

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.

Find more posts tagged with

Accepted answers

Paul Newcome

The + "" should be outside of the function.

=MAX({Date Submitted}) + ""

All comments

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.

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.