Calculation for date field to always display friday date for the current week

[Miss A!]

Hi guys - Miss A! here, need help.....

 

 

How do i define a calculation for a date field to always display the date of the friday for the current or the previous week. The calculation i have, actually from previous week is working only for that day its created, for example today i had to change it again to:

 

 

Let(fri=Get(CurrentDate) - Mod(Get(CurrentDate) +5 ; 4); fri - 4) for the friday from last week 17.10.2014

 

Let(fri=Get(CurrentDate) - Mod(Get(CurrentDate) +5 ; 4); fri + 3) for the friday ending 24.10.2014

 

 

I want this value to change automatically as soon as new week begins, also at the moment, the calculation will display wrong date if i try to add new record to the database on the next day because the current date value keeps changing in the calculation.......appreciate possible solution please?

[Rick Whitelaw]

What about using your calc as an auto enter for your field instead of the field being the type Calculation?

[Miss A!]

Of course calculation is auto enter, but because the formulacalculation is making use of the get(currentdate) function the value is changing depending on what the current date is on the next day - i am thinking case should don though i do not know much of it yet.........will have to try something like that.

[Rick Whitelaw]

Did you check "do not replace . . ." Also the field can now be stored.

[David Jondreau]

The assumption I'm making is that Saturday and Sunday should result in the previous Friday and Monday - Thursday, should be the upcoming Friday.

 

Try this:

 

Let( [
dat = Get ( CurrentDate ) ;
day.of.week = DayOfWeek ( dat ) ;

adjust = Case (
    day.of.week = 1 ; -2 ;
    day.of.week = 7 ; -1 ;
    6 - day.of.week ) ;

friday = dat + adjust

];
friday
)

[Miss A!]

This is it guys - Hello!!!

 

Let([
 AddDays=
Case (
DayOfWeek(Get(CurrentDate)) = 1; -2;
DayofWeek(Get(CurrentDate)) = 2; -3;
DayofWeek(Get(CurrentDate)) = 3; -4;
DayofWeek(Get(CurrentDate)) = 4; -5;
DayofWeek(Get(CurrentDate)) = 5; -6;
DayofWeek(Get(CurrentDate)) = 6; -0;
DayofWeek(Get(CurrentDate)) = 7; -1;)
];
Date(Month(Get(CurrentDate));(Day(Get(CurrentDate) + AddDays)); Year(Get(CurrentDate)))
)

 

This is my calculation for the StartDate field which will always be a Friday previous as from Saturday and my EndDate field will be StartDate+7 to give a duration Startdate to EndDate(Friday to Friday).

 

 

Yes David, you got the idea i could start from(Saturday - Thursday should result in previous friday(for StartDate field) and the EndDate field should be the next friday always) - Thank you!

[comment]

In the interest of sanity,

anydate - Mod ( anydate - 5 ; 7 )

returns the date of the preceding Friday (or current Friday, if anydate is a Friday).

 

 

You could have derived this from the answers given to your previous question:

http://fmforums.com/forum/topic/93813-creating-find-set-based-on-date-ranges/

[Miss A!]

I am so off with the mod function, especially when it involves date calculations - i totally just do not get it i will use a made out solution from here but honestly do not understand what is going on.......so my case calculation is too much but i understand what is going on there, your calculation with Mod is very light and clean but i just do not get it, ohhh shame :(

[Miss A!]

Comment can you help me out here please, so i get to the data viewer with only this part of the calculation  (Mod ( Date(10;23;2014) - 5 ; 7 )) it evaluates to a number 6(how does it come to this?), could you please take me through that part of the calculation for the mod section?

[comment]

I suggest you make your self a test file with the following fields:

 

SerialID - Number, Auto-enter serial number

cDate - Calculation, Result is Date = Date ( 1 ; SerialID ; 2014 )

cMod - Calculation, Result is Number = Mod ( cDate ; 7 )

cMod1 - Calculation, Result is Number = Mod ( cDate - 1 ; 7 )

cMod2 - Calculation, Result is Number = Mod ( cDate - 2 ; 7 )

cMod3 - Calculation, Result is Number = Mod ( cDate - 3 ; 7 )

cMod4 - Calculation, Result is Number = Mod ( cDate - 4 ; 7 )

cMod5 - Calculation, Result is Number = Mod ( cDate - 5 ; 7 )

cMod6 - Calculation, Result is Number = Mod ( cDate - 6 ; 7 )

 

Create about 50 records and observe the results in Table view. See a pattern?

[Miss A!]

uhmmm.......I am seeing the pattern but not sure if i can explain it though - The number results refers to a particular day of the week, starting on a Sunday with 0 ending on a Saturday with 6. i am still stuck on how this result is evaluated, i can not get myself to think out of that box, still puzzled(For the First record, cDate is 01/01/2014, for the cmid field calculation, mod('01/01/2014';7) that i suppose is like Mod(1;7) returning 1 but the field value is 3, how do we come to getting this 3?).

[Miss A!]

Here is the test file, i am gazing at with no positivity what is going on......

ModTest.fmp12.zip

[LaRetta]

Hi Miss A,

 

Dates are numbers – the number of days since 1/1/0001 (which is actually Monday).

 

1/1/2014 is 735234. You can test this by creating another calculation with only cDate in it and make the result number. 1/1/2014 is Wednesday. The benefit of Mod() with dates over using DayOfWeek() is that you can calculate forward/backward across the 0 barrier.

 

DayOfWeek() is nothing but: Mod ( date ; 7 ) + 1

 

mod('01/01/2014';7) that i suppose is like Mod(1;7) returning 1 but the field value is 3, how do we come to getting this 3?).

 
So understanding this, now read FM Help about Mod().  I think it will make more sense.

 

Purpose 

Returns the remainder after number is divided by divisor.

Format 

Mod(number;divisor)

Parameters 

number - any numeric expression or field containing a numeric expression

divisor - numeric expression or field containing a numeric expression

Examples 

Mod(13;4) returns 1.

Mod(7;5) returns 2.

Mod(7;-5) returns -3.

Mod(-7;5) returns 3.

Mod(-7;-5) returns -2.

Mod(Participants;TeamSize) returns 4 if Participants contains 40 and TeamSize contains 9.

[Charity]

Okay Comment I did this file you suggest.  So if I divide 735234 by 7 I get 105,033.429.  If I understand then, the '429' is the remainder.  Why is Mod(735234;7) not a result of .429?  I am with Miss A here because I still do not see the 3 although I see that day of week is 4 and the result of mod is 3 but why doesn't the remainder give me .429 and instead gives me 3?  If I am wrong to step in to this conversation please tell me and I will go away but this is very important for me to understand.  Thank you.

[comment]

Why is Mod(735234;7) not a result of .429?

 

Because .429 is NOT the remainder of dividing 735234 by 7.

 

Let me take a smaller number for easier understanding, say 17 divided by 7.  If try to divide 17 apples among 7 children, you will start by giving each child 2 apples each (total of 14) and you will be left holding the remaining 3 apples. That's what remainder means.

 

 

Now, the important lesson from this file should be that Mod ( ), when applied to a consecutive series, creates a cyclical output. In the file, the result repeats itself in a cycle of 7 days (same as the days of a week repeat themselves), going from 0 to 1, 2 , ... 6 and resetting to 0 at the end of the cycle.

 

The other lesson is that the cycle can be shifted so that the 0 falls opposite any day of week.

 

 

With these two lessons in hand, when you write something like:

Case (
DayOfWeek(Get(CurrentDate)) = 1; -2;
DayofWeek(Get(CurrentDate)) = 2; -3;
DayofWeek(Get(CurrentDate)) = 3; -4;
DayofWeek(Get(CurrentDate)) = 4; -5;
DayofWeek(Get(CurrentDate)) = 5; -6;
DayofWeek(Get(CurrentDate)) = 6; -0;
DayofWeek(Get(CurrentDate)) = 7; -1;
...

you should immediately notice that you have two cycles going on here:

and that these cycles are not starting on the same day of week. The right-hand cycle is shifted 5 places relative to the left-hand one. Did you see the number 5 used anywhere in this thread?

[Miss A!]

ohhh, thank you LaRetta, you made it clear for me now......what i did not understand is the value in the number section of the function(number;divisor) when it comes to date, so the whole date value is converted into a number such as Mod(abs(Date(09;22;2014);7) breaks down to Mod(735498;7) which i then evaluate as follows, just so i am clear i proceed as follows:

 

Mod(735498;7)

=735498/7

=105071.142857 from this value i subtract the whole number section and multiply the decimal part by 7(0.142857 * 7)

=0.999999 ~ 1 Which on calendar is a Monday.

 

Before, i thought Mod is only acting on the day section of the date(ohhh dumbness) ana'cam the whole date is converted to a whole number value for the number of days(from first date 01/01/0001) up to that date Charity, i think we can help each other here now, i hope you are getting somewhere  too ) Also if you got all the time you can try subtracting 7 from the value 735498 all way down till you get the left over value that is less than 7(the divisor) which then is your reminder ))

 

With these two lessons in hand, when you write something like:

Case (
DayOfWeek(Get(CurrentDate)) = 1; -2;
DayofWeek(Get(CurrentDate)) = 2; -3;
DayofWeek(Get(CurrentDate)) = 3; -4;
DayofWeek(Get(CurrentDate)) = 4; -5;
DayofWeek(Get(CurrentDate)) = 5; -6;
DayofWeek(Get(CurrentDate)) = 6; -0;
DayofWeek(Get(CurrentDate)) = 7; -1;
...

you should immediately notice that you have two cycles going on here:

and that these cycles are not starting on the same day of week. The right-hand cycle is shifted 5 places relative to the left-hand one. Did you see the number 5 used anywhere in this thread?

Comment, sorry I do not understand this cycle and shifting you talk about.......I do not how much more you can elaborate but i do appreciate your good efforts.

[comment]

Comment, sorry I do not understand this cycle and shifting you talk about.......

 

Can you see it now?

 

 

[Charity]

Can you see it now?

 

I see it.  It took a week though. With dates we can do things like Date ( 10 ; ANY NUMBER ; 2014 ) and it works even if greater than 31 days for october.  And month will adjust same with no limit even if larger than 12.  The same works here and it is not limited but DayOfWeek() must be between 1 and 7 and it will not adjust meaning DayOfWeek(44) will not work so Mod() is better because it uses any numbers given it and if using Mod(any number; 7) then it will act like DayOfWeek except better.  Well at least this is what I am getting so far.

 

I am considering changing my major to something with math.  I think I like it once I understand it.  Thank you again for taking the time to make the screen shot.  It shows the pattern and I plan to keep it.