Extract timestamp from text field

[Anonymous]

Hi All,

I am trying to achieve a small requirement, where in I have a text column which stores the comments of users. 

The comment column contains : Timestamp User name and comment and there can be multiple comments in that text field added by different users.

So, i want to extract the latest comment timestamp and check whether that timestamp is older than a week.

How can i achieve this condition.

My data source is SharePoint.

Thank you in advance!

Cheers.

[eka24]

Can you give a sample of how the structure of comments in the list looks like?

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[Anonymous]

@eka24 this is how the structure looks like.

RV - 13/01/2021 10:26 - Comments.

JM - 14/01/2021 15:43 - 

Sometimes it can be a case that the Name - Date - is captured but comments are empty so we would want to capture the timestamp where comments are provided.

[eka24]

On the First part of Extraction Onvisible:

ClearCollect(Coll3,Sort(AddColumns(ProductsNew,"Extracted",Mid(dateColumn,5,17)),Extracted,Descending))

On the Second part which is a Conditions:

If(DateDiff(Today(),DateValue(First(Coll3).Extracted),Days)>7,true,false)

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

[Anonymous]

@eka24 i am able to extract the date part from the Comment History column but the only problem that is see is if we are going with position of text , the name might length might increase and extracted data might not be correct.
is it possible we can extract data between 2 special characters which are - , as the format of the text has Name - Date - Comments, so if we can capture the date between Name and comments that should work even if the Name length increases.

Also, there are multiple comments in Comments History column, how do we pick out last comment date?

[Anonymous]

@eka24 any help on this?

[v-bofeng-msft]

Hi @Anonymous :

Firstly,You can use this formula to get the last comment:

Last(Split(
    "RV - 13/01/2021 10:26 - Comments.

JM - 14/01/2021 15:43 - ",
    Char(13)
)).Result

 I split the string by char(13), and the last record value obtained is the latest comment.

Secondly,Use match to find the date:

Match(Last(Split(
    "RV - 13/01/2021 10:26 - Comments.

JM - 14/01/2021 15:43 - ",
    Char(13)
)).Result,"\d\d\/\d\d\/\d\d\d\d\ \d\d\:\d\d").FullMatch

Thirdly,convert date string to time format

DateValue(Match(Last(Split(
    "RV - 13/01/2021 10:26 - Comments.

JM - 14/01/2021 15:43 - ",
    Char(13)
)).Result,"\d\d\/\d\d\/\d\d\d\d").FullMatch,"bg-BG")

Finally,compare date

DateValue(Match(Last(Split(
    "RV - 13/01/2021 10:26 - Comments.

JM - 14/01/2021 15:43 - ",
    Char(13)
)).Result,"\d\d\/\d\d\/\d\d\d\d").FullMatch,"bg-BG")<DateAdd(Today(),7)

Best Regards,

Bof

[Anonymous]

@v-bofeng-msft I tried the function but it seems, that below piece of code does not pick out the last comment date but picks out the first comment date. I want to pick out the last comment date provided in the comment history column. Any modification required to this piece of code?

Last(Split(
    "RV - 13/01/2021 10:26 - Comments.

JM - 14/01/2021 15:43 - ",
    Char(13)
)).Result

[v-bofeng-msft]

Hi @Anonymous :

I've made a test but it worked well:

14/01/2021 is the last day

If you want to get 13/01/2021,please try:

First(Split(
    "RV - 13/01/2021 10:26 - Comments.

JM - 14/01/2021 15:43 - ",
    Char(13)
)).Result

Best Regards,

Bof

[Anonymous]

@v-bofeng-msft i made the test on the column in my SharePoint list and below is the output.As you see the last date and time stamp is different as to what you see in the left column. 
The first column includes your first code and the second column includes the second code to retrieve the date. How do i solve this in order to achieve the last recorded date.