cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

Help with countif

Now that I've sorted my previous issue !  I'm having difficulty with CountiF.  

 

I have a collection called fileinfo (pulled from a sharepoint list) which has a calculated field (at the sharepoint end)  called finishdate  (which appears to come through as a text field)

 

I want to be able to count the following scenarios

 

Records where finishdate is between Today()+ 30

Records where finishdate is between 30 to 60 days from today

Records where finishdate is between 60 to 90 days from today.

 

As well as figuring out the correct syntax , I'm wondering the best way to approach it..    With regard to the discrepancy between the Today() function which returns a date/time value and the finishdate  which is a text field, I can create a label which converts Today() to string?

 

Any suggestions?

 

thank you

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support
Community Support

Hi @vfdd :

Do you want to obtain the number of records that satisfy these three situations separately?

If so,I suggest you try this solution:

The key is to use the DateValue function to convert the text data into a date format, and then use the DateDiff function to calculate the date difference for comparison.

Case1:Records where finishdate is between Today()+ 30

 

CountIf(fileinfo,DateDiff(Today(),DateValue(finishdate),Days)<30)

 

Case2:Records where finishdate is between 30 to 60 days from today

 

CountIf(fileinfo,DateDiff(Today(),DateValue(finishdate),Days)<60 && DateDiff(Today(),DateValue(finishdate),Days)>30)

 

Case3:Records where finishdate is between Today()+ 30

 

CountIf(fileinfo,DateDiff(Today(),DateValue(finishdate),Days)>60 && DateDiff(Today(),DateValue(finishdate),Days)<90)

 

 Best Regards,

Bof

View solution in original post

3 REPLIES 3
Super User III
Super User III

Hi @vfdd ,

 

Try the below:

If(DateValue(Label43.Text)>=Today() And DateValue(Label43.Text)< DateAdd(Today(),30,Days), "Yes","No")

In the above label43 is the calculated date coming from your SharePoint list. In the same way, enhance the remaining two dates.


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Regards,
Krishna
If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members find it more.


I hope this resolved your issue if you see any challenge/need further help please let me know I am always happy to do it for my community.

Proud to be a Super User!

Regards,
KrishnaV
Business Applications MVP
Twitter | Linkedin | YouTube | GitHub
If this post helps you give a 👍 and if it solved your issue consider Accept it as the solution to help the other members / new members of the community.
Community Support
Community Support

Hi @vfdd :

Do you want to obtain the number of records that satisfy these three situations separately?

If so,I suggest you try this solution:

The key is to use the DateValue function to convert the text data into a date format, and then use the DateDiff function to calculate the date difference for comparison.

Case1:Records where finishdate is between Today()+ 30

 

CountIf(fileinfo,DateDiff(Today(),DateValue(finishdate),Days)<30)

 

Case2:Records where finishdate is between 30 to 60 days from today

 

CountIf(fileinfo,DateDiff(Today(),DateValue(finishdate),Days)<60 && DateDiff(Today(),DateValue(finishdate),Days)>30)

 

Case3:Records where finishdate is between Today()+ 30

 

CountIf(fileinfo,DateDiff(Today(),DateValue(finishdate),Days)>60 && DateDiff(Today(),DateValue(finishdate),Days)<90)

 

 Best Regards,

Bof

View solution in original post

Thank you so much @v-bofeng-msft  that is exactly what I needed !!   I spent nearly a full day yesterday trying various combinations and here you give it to me in 5 mins LOL

Helpful resources

Announcements
News & Announcements

Community Blog

Stay up tp date on the latest blogs and activities in the community News & Announcements.

Power Apps Community Call

Power Apps Community Call- January

Mark your calendars and join us for the next Power Apps Community Call on January 20th, 8a PST

PP Bootcamp Carousel

Global Power Platform Bootcamp

Dive into the Power Platform stack with hands-on sessions and labs, virtually delivered to you by experts and community leaders.

secondImage

Power Platform Community Conference On Demand

Watch Nick Doelman's session from the 2020 Power Platform Community Conference on demand!

Top Solution Authors
Top Kudoed Authors
Users online (10,745)