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

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (28,155)