Mod() with date always returns 0

[Anonymous]

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:

Id	Type	Chores	Person
1	Daily	Snow's Food and Water
2	Daily	Walk Snow - Evening (10 mins.)
3	Daily	Walk Snow - Night (15 mins)
4	Daily	Dishes
5	Daily	Pick Up Living Room
6	Daily	Wipe Down Bathroom
7	Daily	Take 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")))});

[mdevaney · Accepted solution]

@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."

[Anonymous]

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.