Calculating a Timestamp for the next day of the week

[Devin]

So I'm in need of calculating a Timestamp field that will give me the next Wednesday date.

Here is the calc that I started using
GetAsDate ( Date ) + Mod ( 4 - DayOfWeek ( GetAsDate ( Date )

It works, expect now I need worry about the time. If the time is after 12pm on a Wednesday
I need to return the next Wednesday and not the current Wednesday.

Examples
10/16/19 2:04:35 PM --> 10/23/19
10/16/19 8:23:55 AM --> 10/16/19
10/15/19 4:26:35 PM --> 10/16/19
10/18/19 6:14:17 PM --> 10/23/19

 

Edited October 17, 2019 by Devin

[comment]

Do I understand correctly:

• Your input is a Timestamp field, the result is a Date;
• The result should always be the date of the nearest future Wednesday - except when the input's date is a Wednesday AND the input's time is before 12:00 noon.

Your 2nd example does not fit these rules - should it be AM instead of PM?

 

[Devin]

Comment, 

Yes you are correct, it should have been AM for the 2nd example. 

After reviewing,  it will be best to get the result as TimeStamp. I can easily extract Date from a timestamp if needed later for something.  You are correct on your understanding of the result.  But know that we are wanting to return a Timestamp, we will want to return not only the correct date but change the Time to 1 PM.

Thanks

 

[comment]

There are several ways you could look at it. To continue the approach you have taken, consider:

Let ( [ 
startDate = GetAsDate ( Input ) ;
startDayOfWeek = DayofWeek ( startDate ) ;
nextWed = startDate + Mod ( 4 - startDayOfWeek ; 7 ) ; // this stays on startDate if startDate is a Wednesday
exception = startDayOfWeek = 4 and GetAsTime ( Input ) > Time (12 ; 00 ; 00 ) // input is Wednesday after noon
] ;
Timestamp ( nextWed + 7 * exception ; Time ( 13 ; 00 ; 00 ) )
)

 

[Devin]

This worked perfect..  The use of Mod is something I would never have thought of.

Thanks

[comment]

11 minutes ago, Devin said:

  The use of Mod is something I would never have thought of.

You have it in your opening post (although the formula there is incomplete).

Anyway, Div() and Mod() are indispensable in date calculations. 

 

[bcooney]

OK, I really want to understand Mod.

Mod ( 4 - startDayOfWeek ; 7 ) .  Given today is a Friday > Mod (-2 ; 7 ) = 5.  

What? 

[comment]

2 hours ago, bcooney said:

I really want to understand Mod.

I am not sure this is the best place to start, but let's try. And let's start with a Sunday:

Mod ( 4 - 1 ; 7 ) = 3

That's 3 days until Wednesday. Next, let's take a Monday:

Mod ( 4 - 2 ; 7 ) = 2

That's 2 days until Wednesday. Now, Tuesday is of course 1 day before Wednesday::

Mod ( 4 - 3 ; 7 ) = 1

and I think it's clear that 4 - 4 will result in 0 days to move from Wednesday to Wednesday.

So far, we have simply subtracted the day-of-week of today from 4 (Wednesday). Applying mod 7 to the result did not change it.

Now this is where it starts to get interesting. If today is a Thursday, then the difference is  -1 (as in you need to go 1 day back to get to a Wednesday). But Mod ( - 1 : 7 ) returns 6, telling us we need to go 6 days forward to get to the next Wednesday:

Mod ( 4 - 5 ; 7 ) = 6

Similarly (as you already calculated), it's 5 days from Friday to next Wednesday, and 4 days from a Saturday. So this calculation always tells us how many days to add to today in order to get to the next Wednesday.

---
A note is required here: in many programming languages, Mod ( - 1 ; 7 ) returns -1 instead of 6. And so did Filemaker before version 7. The v.7 team made a very smart and untypically bold decision to change the behavior in order to conform to the recommendation of Donald Knuth. 

Before this change, you always needed to add some multiple of 7 to the dividend in order to avoid a negative result - for example, Mod ( -1 + 7 ;  7 ) would return the wanted 6 instead of the useless -1.

And this is perhaps the most significant aspect of the Mod() function:

Mod ( 3 ; 7 ) = 3 ;
Mod ( 10 ; 7 ) = 3 ;
Mod ( 17 ; 7 ) = 3 ;
Mod ( 24 ; 7 ) = 3 ;
...

As you can see, adding a multiple of 7 does not change the result. IOW, the Mod() function is cyclical - and that's why it's so useful for date calculations: our calendar is cyclical too.

 

Edited October 18, 2019 by comment

[bcooney]

Thanks, Comment. It's that wrap around that was challenging. 

My mental model is dividing into equal groups and what mod provides is how many are left over. Weeks are in groups of 7 days. Hours in groups of 60 mins. The easy, Mod ( some number ; 2 ) in pairs.

[comment]

11 minutes ago, bcooney said:

My mental model is dividing into equal groups and what mod provides is how many are left over.

That's why Div() and Mod() go together like horse and carriage: Div() gives you the number of the group; Mod() gives you the position within the group.

 

Edited October 18, 2019 by comment

[bcooney]

So, you're saying that if we don't know our Modular divisor we'd use Div() to derive it. That is, we're not talking days in a week ( therefore, we know to use 7 for our divisor/grouping), we'd derive the grouping using Div(). What would be a good example showing that?

[comment]

Just now, bcooney said:

So, you're saying that if we don't know our Modular divisor we'd use Div() to derive it.

Oh no, that's not what I am saying. You need to know that there are 7 days in a week. Or 24 hours in a day. Or 400 years in a leap-year cycle.

What I am saying is that if you tell me that 1325936079 seconds have elapsed since the midnight of Jan 1, 1970 (this is what a Unix timestamp looks like), I can do:

Div ( 1325936079 ; 86400 ) = 15346;
Mod ( 1325936079 ; 86400 ) = 41679;

so I know it's 15346 full days and 41679 seconds. Next, I could do:

Div ( 41679 ; 3600 ) = 11;
Mod ( 41679 ; 3600 ) = 2079;

so I know it was 11 hours and 2079 seconds into the 15347th day, and so on. Of course, you could use the built-in functions to do the same thing - but that's exactly what they do: the same thing.

Another good example, I think, is base conversion: space is Char (32):

Div ( 32 ; 16 ) = 2;
Mod ( 32 ; 16 ) = 0;

and indeed the hexadecimal code for space is 20.

 

[LaRetta]

8 hours ago, comment said:

Anyway, Div() and Mod() are indispensable in date calculations. 

A great developer once told me, "Know thy Mod() function, when thou cometh to date calculations."  😀

Hi Barbara! No, I haven't perfected it yet either.  Michael and possibly Ray Cologon are the only two who've mastered it to that degree.  Nice to see you again!

[bcooney]

Great thread. Thanks.