cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Extract timestamp from text field

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.

12 REPLIES 12
eka24
Super User III
Super User III

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
Not applicable

@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
Super User III
Super User III

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
Not applicable

@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
Not applicable

@eka24 any help on this?

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
Not applicable

@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

 



 

Hi @Anonymous :

I've made a test but it worked well:

1.JPG

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
Not applicable

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

Jayant2_0-1612430045340.png

 

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

PowerPlatform 768x460.png

Microsoft Learn

Check out our new Discover Your Career Path blog post series and get all the details.

Users online (2,294)