+ Add Question

Yearly repeat

P

I'm a motorcycle rallyist. Rallies are repeated on the same weekend every year. How can I set à repeat date for say the last Saturday in April every year?

All responses

David Trey staff
Replied on Apr 20, 2014 - 22:06 UTC

Hello,

Unfortunately, such date recurrence is not available right now. You can set a task for a specific date every year, for example "every 27 April", but you can't set it to every last Saturday of one specific month every year.


Best regards,
David

P
pogotronics
Replied on Apr 20, 2014 - 22:27 UTC

Hi David.

Thanks for responding so quickly. I have a couple of ideas for a workaround
which I am testing. I'll post them if they work out.

P
pogotronics
Replied on Apr 29, 2014 - 08:17 UTC

The problem has been solved on the Excel forum by providing a formula that uses Excel date functions to calculate the nth xday of any month in any year. Finding the last xday is solved by finding the first xday in the following month and taking the date one week before.

I've set my repeats to be every 52 weeks which will be right for up to 5 or 6 years depending on where the date is in the 28 year repeat cycle, then jump forward a week.

Here's the formula:

=DATE(B2,A2,1+7*n)-WEEKDAY(DATE(B2,A2,8-xday))

where year is in B2 and month (as a number 1 to 12) is in A2, and where xday is a number representing the day of the week (1 = Sun through to 7 = Sat)

Putting n in A2, xday in B2 and moving the rest of the formula across gives:

=DATE(D2,C2,1+7*A2)-WEEKDAY(DATE(D2,C2,8-B2))

I use the formula to find the jump year, then set a task for the previous year to change the date.

Pogo.

Clyde Romo staff
Replied on Apr 29, 2014 - 09:59 UTC

Hi Pogo,

Thank you for taking the time in sharing your solution. :-)

Regards,

Clyde

GO
Glenn Olson
Replied on Jul 12, 2014 - 16:29 UTC

How/where is that formula used? I have a lot of this type of entry and have been considering a switch to another app that will do this type of recurrence.

Thank you,
Glenn

Sebasti√£o Fortunato Araujo premium
Replied on Jul 13, 2014 - 13:47 UTC

I wanna know too..