Math Stuff and Dates

Here’s what I’m trying to accomplish and have tried a few different approaches none of which have worked perfectly and wondering if there is a more obvious solution that I have overlooked.

I am creating a list that shows the user what documents were processed on the two preceding days (so it’s always showing yesterday’s actions, and the actions of the day before yesterday) I have an approach that works fine when the list is viewed Tuesday-Friday. The issue is that on Saturday-Monday, it needs to continue to show the documents processed on Thursday (documents generally aren’t processed on Friday). So while my approach compares today’s date with the processing date and shows any document that is less than or equal to 2 (days ago). But on Sunday or Monday, that shows nothing. Basically, I need an approach that says “if today is Tuesday-Friday” show me stuff from the prior 2 days" but if It’s Saturday-Monday, show me Thursday’s stuff (and Friday’s if there are any). "

In a similar fashion I am trying to show upcoming meetings. During the first part of the week, it needs to show meetings that are happening that day and for the rest of the week. But on Thursday-Sunday there are no meetings, so at that point it needs to then show upcoming meetings for the following week, starting on Monday.

Any clues on how to do this?

Just a few clues if you want to exclude Saturdays and Sundays.
I would try to do this:

  • Build an array that contains the 4 dates prior to the current one (today-1, today-2, etc.); element 0 will be yesterday’s date.
  • Build an array that contains the date of last Saturday and last Sunday (use relative references in date columns (this sunday, last saturday)).
  • Remove the elements of the second array from the first (other-> array-> remove elements).
    At this point:
  • In the first array, element 0 is that relating to the last working day (single value → get first → from the new array), element 1 is that relating to the penultimate working day (single value → get from start row 1 → from the new array).

P.S. To build the array do not use the Make Array function because you would drag the time as well, instead use a template column to assemble the elements of the dates and then split into an array.

Now I can’t help you any better (here it’s time to go to bed).

For your first issue, Is Friday the only day that does not have documents processed? I’m trying to understand what prior days you expect to see when today is either Saturday, Sunday, or Monday.

If you have some screenshots of what you have and what you are expecting, that would be great. I have some ideas, but I think I need a better idea of what you are trying to achieve.

1 Like

This will likely be a problem. I think you should decide if you’re using Friday or not before building the flow, or else it can get more complex than you expect.

I assume you will use Friday, so the days will look like this.

Today Start day to show End day to show
Monday Thursday Friday
Tuesday Friday Monday
Wednesday Monday Tuesday
Thursday Tuesday Wednesday
Friday Wednesday Thursday
Saturday Thursday Friday
Sunday Thursday Friday

What I would do, in your user profiles table, is:

  • Create a math column to return the weekday of the current day (0: Sunday all the way to 7: Saturday).

  • Create an if-then-else column to return the “days from today” value so that when I take today minus that value, it will return the start day for the period to return.

Today Weekday If-then-else value
Monday 2 4
Tuesday 3 4
Wednesday 4 2
Thursday 5 2
Friday 6 2
Saturday 7 2
Sunday 1 3
  • Then, create a start date column, take today minus the value above.

  • Create an end date column, take the start date above plus 1.

Finally, filter your list by date is on or after start date and date is on or before end date.

1 Like

Sorry for the @Jeff_Hager. Now I realize that was not totally clear. So documents are processed Monday-Thursday. On Saturday, Sunday And Monday I want it to show what was processed on Thursday, the last prior day of any processing. Then on Tuesday it will show Monday. Is that clearer? I could try to mock up a screen shot if not.

Thanks @ThinhDinh. Let me try this. I tried something similar using math rather than the if then and the results weee not as expected. Let me try this and test it across the weekend here to see if it works. I’ll be back.

1 Like

Thank you this is a creative approach. Let me play around with that.

1 Like

So I tried this and I might be doing something wrong but I’m getting the same result that prompted me to ask this question: The math column sometimes results in a negative number. But before explaining that I want to clarify one thing: I’m using this formula in a math column to get the day number: Weekday(today)

I understand your response to expect 0 for Sunday, but for me, that equation returns 1, not 0 for Sunday.

Using the approach above, the equation looks like this on Sunday:

Today - IfThenValue OR 1-3 which equals -2. I think the result would be similar on Monday returning either -3 or -2, depending on whether the correct value for Monday is 1 or 2

I could flip the equation to be IfThenValue - Today. But that then gives a negative number beginning on Wed (2-4= -2).

What am I doing wrong?

Sorry, it should have said 1 for Sunday.

No, you don’t use the “weekday” value inside your equation to calculate the start date. You use this instead.

So Start Date = Today - ITE Value

By Today I mean the “Now” value.

The weekday was just a value to pull out the ITE value, which is used in the equation. It has no role afterwards.

1 Like

Ah! Ok. Thanks.

1 Like

Let us know if you have any other questions!

Finally implemented this and got it working. Great solution. As I was working on this, I changed my goals sightly, deciding that for Tues-Friday, rather than showing yesterday’s actions and the actions of the day before, I would just for now show the day before. (So show one prior day, not two). For Friday though Monday, we decided it needed to show Thursday’s actions through whatever day. So on Sunday for example, it needs to show Thursday, Friday and Saturday.

So I modified the approach slightly from what @ThinhDinh suggested, creating an If-Then for the end date as well, and subtracting that value from the start date. I adjusted the values for both if-thens until.I got the result needed.

For anyone else who might want to implement something similar, here’s a screenshot of a version showing how it works for various dates (of course in practice you would use “now” for today’s date, not the various dates in the first column).

1 Like

Both of those could be simplified slightly by moving the common cases into the else condition. For example, for the first one you should be able to use:

  • If 1, then 3
  • If 2, then 4
  • If 7, then 2
  • Else 1
1 Like