Forum Discussion
Can i use FIND to search last occurence
So I have a string
Complete Status 123@2023-03-01T16:25:51.094Z Status 456@2023-03-01T16:22:46.227Z|
Status XYZ@2023-03-01T19:43:56.184Z Status 456@2023-02-28T18:24:23.7292|
Status XYZ@2023-02-28T13:45:26.107Z Status MNOP@2023-02-27T17:59:17.7312|
Status XABA@2023-02-27T17:26:38.781Z|
I need to find the date and time of the first occurrence of a given status
Right now I am able to get the first occurrence date and time using FIND by doing this
=IFERROR(DATEVALUE(MID(@s[[Status]: [Status]], FIND("@",@s[[Status]: [Status]], FIND(COLUMNA$1,@s[[Status]: [status]]))+1,
10))
+TIMEVALUE (MID(@s[[Status]: [Status]],
FIND("@",@s[[Status]: [Status]],FIND(COLUMNA$1,@s[[Status]: [Status]]))+12, 8))-5/24,"")
Please assist
With such a string in A1:
=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A1, "@", REPT(" ", 255)), 255)), "T", " "), "Z", ""), "|", "")-5/24
Format as date + time.
Thanks for your response
Can you please elaborate a bit if u don't mind
If A1 is the complete string ,where is the sub string that I am looking for?
Asking since I don't see the sub string that I am searching for anywhere in the formula
The formula extracts the part of the string after the last occurrence of @.
It then removes "Z" and "|" from that part and replaces "T" with a space.
That is the date and time you're looking for. Finally it subtracts 5/24 to convert from GMT to EST.
This 365 formula converts the string to elements of an array and works with those rather than continuing to process the string.
= LET( finalStatus, TEXTAFTER(A1,"Status ",-1), dateAndTime, TEXTSPLIT(finalStatus, {"@","T","Z"}), SUM(VALUE(CHOOSECOLS(dateAndTime,{2,3})), - 5/12) )
