Calculating Particular Days of the Month

English
#1

Here’s an interesting one, I think.

What I want is list of dates for the first and third Friday (or in deed, any day of the week) of each month throughout the year. I’d be happy to manually put in the first entry for the year (i.e. 1/1/27), but the following 23 need to be calculated. I could probably do this if I were writing a program, but I just can’t figure it out with functions. I’d be happy for multiple (maybe hidden) columns. I don’t mind if it’s complicated - as long as it works.

Just to complicate matters, I might need to adjust individual dates, by overwriting the function with a comment, such as ‘Off this week’. but have the correct values continue.

I’ve had several goes at trying to work this out, but seem unable to do so. Let me know if I’m asking too much of LO Calc (or in deed, you). If there is no reasonable way, I will just have to manually enter the year’s dates, which would probably take less time than me writing this comment out :smile:.

Don’t work too hard on this, it’s not that important. I just thought it might be interesting to know if there’s a simple(ish) way.

Matt

#2

Hi @MattC, post an example file, it’s easier to suggest something.

#3

ask136454.ods (33.4 KB)

#4

With the same idea as @Villeroy.
Finds the smallest date greater than or equal to the date in cell A1, where the day of the week is Friday.

=A1+6-MOD(A1;7)

For other days of the week - an exercise for the reader. :slight_smile:

#5

Thanks Villeroy, sokol92. I’ll have to examine this, as I’m a little slow to recognise what’s going on as schiavinatto might testify. But thanks.

schiavinatto, sorry, I didn’t have an example file, but Villeroy seems to have answered my problem. Thanks though.

Thanks all for your trouble.

1 Like
#6

=INT((ROW(A1)-1)/12) repeats 12 times 0, then 12 times 1, 12 times 2 and so on as far as you drag down. Plus the year number in $H$1, you get 12 times 2025, 2026 and so on.
=MOD(ROW(A1)-1;12)+1 repeats the sequence of month numbers 1 to 12 as far as you drag down.
This way you get 2 sequences of year numbers and their month numbers.
In column C =DATE(A2;B2;1) gets the 1st day of the respective month.
-WEEKDAY(CURRENT();2) subtracts the weekday number from that day. According to WEEKDAY, Monday=1, Sunday=7 if the second argument is 2. Subtracting the weekday number from the first of month, you get the Sunday before that first day and +5 must be the first Friday within that month. First Friday +14 gives the third Friday.

#7

Single-cell-formulas can do.
See
disask_136454_selectDaysByWeekdayRept.ods (12.6 KB)
I came back to the topic and noticed that I had attached an example with lots of errors.
I made a (hopefully) better example and included some explanations.
disask_136454_selectDaysByWeekdayOccurrence_E.ods (17.0 KB)
Please tell me if you find errors again.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.