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
-
The + "" should be outside of the function.
=MAX({Date Submitted}) + ""
Answers
-
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
-
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?
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!