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.

Best Answer

Answers

  • Paul Newcome
    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(...........) + ""

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    The + "" should be outside of the function.

    =MAX({Date Submitted}) + ""

  • Thank you! That worked!

  • 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
    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.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!