Help with countif

[vfdd]

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

[KrishnaV]

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.

[v-bofeng-msft · Accepted solution]

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

[vfdd]

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