cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

Mod() with date always returns 0

Hello,

 

I'm trying to build a simple app to assign chores to my children.  The assignments change on a daily basis.  I have the table set up in Excel and am using Mod() to assign the chores based on a rotating schedule of 4 people.  When I use it only on Excel, it works like a charm:

 

 

=IF(MOD(TODAY(), 4) = 2, "Blanca", IF(MOD(TODAY(), 4) = 3, "Gagy", IF(MOD(TODAY(),4)=0, "Derek", "Nataly")))

 

However, if I populate my column with this formula, PowerApps won't accept the data.  So, I have to leave that column blank and do the math inside PA.  I connect the data to my App, then I ask for the date--honestly it should always be Today(), but I'll work on that after I get Mod() working.  Once the date and the name are chosen with Dropdown boxes, I have them click on a button that performs the following:

 

 

Patch(clctDailyChores, First(Filter(clctDailyChores, Id = "1")), {Person:If(Mod(_MyDate, 4) = 2, "Blanca", If(Mod(_MyDate + 1, 4) = 3, "Gagy", If(Mod(_MyDate + 1,4)=0, "Derek", "Nataly")))}); 

 

Where

- clctDailyChores is a collection created on open from the Excel table

- Person is the column I am using to store the assigned person's name

- _MyDate is the variable created from the date dropdown box

 

The expected behavior (as it does in Excel) is that every new date on a rotation of 4 should populate this column with a new name. So today could be Derek, tomorrow Gagy, etc. until the rotation begins again.  Unfortunately, this does not happen in PA.  The result will always be Derek. I tested by creating a new label with the text field set to:

 

 

Mod(Today(),4)

 

The result is 0.  I change the number to 5, or anything, and the result is always 0.  Can somebody tell me if I am doing something wrong or if this is expected behavior?  If this is how PA reads dates, how can I create a workaround?

 

On a daily basis, the Chore should rotate.  Complete code for all chores is below the table.  Here is the Table:

IdTypeChoresPerson
1DailySnow's Food and Water 
2DailyWalk Snow - Evening (10 mins.) 
3DailyWalk Snow - Night (15 mins) 
4DailyDishes 
5DailyPick Up Living Room 
6DailyWipe Down Bathroom 
7DailyTake Out Trash 
Patch(clctDailyChores, First(Filter(clctDailyChores, Id = "1")), {Person:If(Mod(_MyDate, 4) = 2, "Blanca", If(Mod(_MyDate, 4) = 3, "Gagy", If(Mod(_MyDate,4)=0, "Derek", "Nataly")))});

Patch(clctDailyChores, First(Filter(clctDailyChores, Id = "2")), {Person:If(Mod(_MyDate, 4) = 1, "Blanca", If(Mod(_MyDate, 4) = 2, "Gagy", If(Mod(_MyDate,4)=3, "Derek", "Nataly")))});

Patch(clctDailyChores, First(Filter(clctDailyChores, Id = "3")), {Person:If(Mod(_MyDate, 4) = 0, "Blanca", If(Mod(_MyDate, 4) = 1, "Gagy", If(Mod(_MyDate,4)=2, "Derek", "Nataly")))});

Patch(clctDailyChores, First(Filter(clctDailyChores, Id = "4")), {Person:If(Mod(_MyDate, 4) = 3, "Blanca", If(Mod(_MyDate, 4) = 0, "Gagy", If(Mod(_MyDate,4)=1, "Derek", "Nataly")))});

Patch(clctDailyChores, First(Filter(clctDailyChores, Id = "5")), {Person:If(Mod(_MyDate, 4) = 2, "Blanca", If(Mod(_MyDate, 4) = 3, "Gagy", If(Mod(_MyDate,4)=0, "Derek", "Nataly")))});

Patch(clctDailyChores, First(Filter(clctDailyChores, Id = "6")), {Person:If(Mod(_MyDate, 4) = 1, "Blanca", If(Mod(_MyDate, 4) = 2, "Gagy", If(Mod(_MyDate,4)=3, "Derek", "Nataly")))});

Patch(clctDailyChores, First(Filter(clctDailyChores, Id = "7")), {Person:If(Mod(_MyDate, 4) = 0, "Blanca", If(Mod(_MyDate, 4) = 1, "Gagy", If(Mod(_MyDate,4)=2, "Derek", "Nataly")))});

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User
Super User

Re: Mod() with date always returns 0

@Anonymous

Hello there.  In PowerApps, if you convert TODAY to an integer it will be shown in milleseconds (e.g. 1572152400000) but in Excel TODAY is shown in days (e.g. 43765).  If you try to apply MOD 4 to TODAY within PowerApps it will always show 0 because TODAY always has a time of 12:00PM.

 

In PowerApps we can use this formula which includes the DATEDIFF function to find the number of days from the beginning of time...

 

Value(DateDiff(Date(1900,0,30),Today()))

 

...to show the same value as TODAY in Excel (please note I wrote this post on 10/27/2019)

 

43765

 

You can use my suggested forumla along with the MOD function to get your desired result.  I would recommend you store this number in a variable called ScheduleDay to make your code more readable.

 

Set(ScheduleDay,Mod(Value(DateDiff(Date(1900,0,30),Today())),4))

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

2 REPLIES 2
Highlighted
Super User
Super User

Re: Mod() with date always returns 0

@Anonymous

Hello there.  In PowerApps, if you convert TODAY to an integer it will be shown in milleseconds (e.g. 1572152400000) but in Excel TODAY is shown in days (e.g. 43765).  If you try to apply MOD 4 to TODAY within PowerApps it will always show 0 because TODAY always has a time of 12:00PM.

 

In PowerApps we can use this formula which includes the DATEDIFF function to find the number of days from the beginning of time...

 

Value(DateDiff(Date(1900,0,30),Today()))

 

...to show the same value as TODAY in Excel (please note I wrote this post on 10/27/2019)

 

43765

 

You can use my suggested forumla along with the MOD function to get your desired result.  I would recommend you store this number in a variable called ScheduleDay to make your code more readable.

 

Set(ScheduleDay,Mod(Value(DateDiff(Date(1900,0,30),Today())),4))

 

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

View solution in original post

Highlighted
Anonymous
Not applicable

Re: Mod() with date always returns 0

Thank you for the quick and easy solution.  It works like a charm.  Instead of Today(), I'm using a variable grabbed from the DatePicker Dropdown.

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (5,240)