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

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

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

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

Top Solution Authors
Top Kudoed Authors
Users online (72,601)