How to get first Friday date of next month when a month is chosen?

[apoorvasd]

Hello Everyone,

I would like to create a date field which should contain the list of date of first Friday of a month. And when I choose a particular month, then that list box has to correspond to the date of first Friday of next month.

Say, for example in the below screenshot, I have two fields named, Week and Date. Date column here has the dates of just Fridays in that particular month. Now I would want to create a new field which has the list of 1st Friday dates of every month in it. So when I select Jul in Month field, this new field should show 11/Aug/2017 as its corresponding value for Jul. Similarly when I choose Aug in month field, this field should display 08/Aug/2017 as its corresponding first Friday date of next month and so on. How do I achieve this?

Any help on this?

Thank you.

[jpenuliar]

Hi Apoorva,

Example date is 07/11/2017 (Tuesday),  to calculate the date for friday('10/11/2017') is :

=Date('07/11/2017'  + 3)

10/11/2017 - 07/11/2017 = 3 Days

here is a lookup for all the days in a week

Weekday()	Days to Friday
0 for Monday	4
1 for Tuesday	3
2 for Wednesday	2
3 for Thursday	1
4 for Friday	0
5 for Saturday	6
6 for Sunday	5

Using the above lookup table, we can write a formula like below to return "Days to Friday".

= Pick(Match(Num(Weekday('07/11/2017')),0,1,2,3,4,5,6),4,3,2,1,0,6,5)

the formula wil return 3 for the date "07/11/2017"

we can then use the Pick(Match()) formula here:

=Date('07/11/2017'  + Pick(Match(Num(Weekday('07/11/2017')),0,1,2,3,4,5,6),4,3,2,1,0,6,5))

This will return 10/11/2017 (Friday) for the Date 07/11/2017 (Tuesday).

[apoorvasd]

Hi Jonathan,

Thanks for your response. I would like to create a date field which has dates of only first Friday of every month. But here in your suggestion you have provided a date (07/11/2017) by default. I would not like to explicitly mention each and every date but a field field which contains all such dates. Is it possible?

Thank you.

[jpenuliar]

Hi,

You can use the same expression in script like below:

temp1:
Load * Inline
[
Date
07/11/2017
15/11/2017
22/11/2017
];

temp2:
load *,
Date([Date]  + Pick(Match(Num(Weekday([Date])),0,1,2,3,4,5,6),4,3,2,1,0,6,5))
Resident temp1;