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

CountIf Dates from Excel

Hello,

 

I'm really struggling with dates from Excel. Seems simple enough I have a drop down with Calendar.MonthsLong() I want to use that to count dates form an Excel table. I have tried many things. I have set the column in Excel to Date format and Text format in combination with the formulae below.

 

CountIf(Data,Text(Date,"MMMM")=ddFilter4.Selected.Value)

CountIf(Data,Text(DateValue(Date),"MMMM")=ddFilter4.Selected.Value)

CountIf(Data,Text(Date(Date,Date,Date),"MMMM")=ddFilter4.Selected.Value)

 

Probably a few others too. Can anybody give me an idea what I'm doing wrong?

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @StephenGW ,

Interesting exercise (I do not use Excel for many reasons), but did an import into my trusty test app. I got this working

CountIf(
   AddColumns(
      Data,
      "DateTxt",
      Text(
         'Audit Date',
         "[$-en]mmmm"
      )
   ),
   DateTxt = ddFilter4.Selected.Value
)

The main issue (which I should have picked up) is that MMMM (or more correctly MM) is Minutes - Months are mmmm / mmm / mm

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

8 REPLIES 8
WarrenBelz
Super User III
Super User III

Hi @StephenGW ,

The first one should do it, although I assume you are not actually using the title Date for the column (it is a Reserved Word in PowerApps). Having said that Excel and the way it handles dates stored is problematic. Please confirm the column heading first here. I am also assuming you see the correct full month name in the drop-down. You could also try

CountRows(
   Filter(
      Data,
      Text(Date,"MMMM")=
      ddFilter4.Selected.Value
   )
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

@WarrenBelz 

 

Yes I am using the header of Date. I wonder if that is my problem? I will try changing the header and see if I can get it to work. I do see the full month name in the drop down. I feel like I should have started this with a SP list instead of excel.

Hi @StephenGW ,

Firstly, you might watch this video from Shane Young @Shanescows and I fully endorse your thoughts on going to SharePoint (all of these issues should go away). I also have a blog on data structure that may interest you.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

 

@WarrenBelz 

 

I tried to go to SP but I really struggled with images and I don't want to use Flow. I'm trying to make everything in the app with no Flows. So I went back to my old app with Excel, Yikes! This app pretty much works as needed but I can't get the count based on month to work. I renamed the Date column to Audit Date. It contains all dates from April specifically 4/16/21. I then set my ddFilter4 which contains the Calendar.MonthsLong() and put this in my text label CountIf(Data,Text('Audit Date',"MMMM")=ddFilter4.Selected.Value) which returns 0. If I change it to CountIf(Data,Text('Audit Date',"MMMM")="April") it still returns 0. If I use a Data Table to view the Audit Date column it shows it as text? If I use Text(DateValue(ThisItem.'Audit Date'),"[$-en-US]MMMM") it changes it to values like MMMM16185492500000 The dates in the Excel sheet are formatted as Numbers. I have been reading a lto of your stuff and watching a lot of Shane's videos but I can't figure this out.

 

Any ideas on how I can force these dates?

 

Thanks,

Stephen

Hi @StephenGW ,

Interesting exercise (I do not use Excel for many reasons), but did an import into my trusty test app. I got this working

CountIf(
   AddColumns(
      Data,
      "DateTxt",
      Text(
         'Audit Date',
         "[$-en]mmmm"
      )
   ),
   DateTxt = ddFilter4.Selected.Value
)

The main issue (which I should have picked up) is that MMMM (or more correctly MM) is Minutes - Months are mmmm / mmm / mm

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

View solution in original post

@WarrenBelz 

 

Thank you so much! I think the MMMM vs mmmm was my problem. For anyone else looking into this here is how I got it to work. I formatted the dates in Excel as Text. Then I used this to count them against my dropdown.

 

CountIf(Data,Text(DateValue('Audit Date'),"[$-en-US]mmmm")=ddFilter4.Selected.Value)

 

Stephen

@WarrenBelz 

 

Got a question I'm wondering if you can answer. I'm trying to Filter my gallery by month using the same data set. When I use this CountIf(Data,Text(DateValue('Audit Date'),"[$-en-US]mmmm")=ddFilter4.Selected.Value) it works to count but this Filter(Data,Text(DateValue('Audit Date'),"[$-en-US]mmmm")=ddFilter4.Selected.Value) gives an error of "expected text value" can you explain this to me?

 

Edit: Please disregard. My mistake, I was trying to filter the label not the Gallery. Switched it up and it works now.

 

Stephen

Hi @StephenGW ,

You might also consider doing this - I use the syntax any time I modify a field value to suit a Filter or Sort as it simply works all the time.

Filter(
   AddColumns(
      Data,
      "Audit"
      Text(DateValue('Audit Date'),"[$-en-US]mmmm")
   ),	  
   Audit=ddFilter4.Selected.Value
)

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

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

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

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

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.

Top Solution Authors
Top Kudoed Authors
Users online (2,279)