Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I'm stuck with a problem in LibreOffice Calc or Writer:

I try to get a table of dates for every Monday, Wednesday and Friday per month, something that I can load every new month wich sets the dates automatically so I just have to print the page on paper. :)

In Writer the fieldcommand for date can't obviously do the job (it only can be set to a fix date, the today's date or +offset as far as I can see).

In Calc I tried it with the autofill but that obviously works for the first 3 days only.

I might add that I'm lost with Excel/Calc formulas...

Any help?

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted

If cell C1 contains a date, then this formula will give you the date of the first Monday in the month:

=MOD(DAY(C1)-WEEKDAY(C1,2)+1,7)

So for example, if the date in C1 is 19th December 2012, then the formula will produce 3, because the 3rd is the first Monday in December 2012.

To create the full date of the first Monday, you can use:

=DATE(YEAR(C1),MONTH(C1),MOD(DAY(C1)-WEEKDAY(C1,2)+1,7))

This will result in 05/12/2012 (depending on your date format) - the first Monday

Lets say this is in cell A4. To populate the rest of the days, you need to add +2 for Wednesday, then +2 for Friday, then +3 for the following Monday. It is easiest to do this manually:

A5 would be =A4+2 A6 would be =A5+2 A7 would be =A6+3

Then you can repeat this down the spreadsheet for the remainder of the month.

share|improve this answer
    
Ah, thank you! That looks pretty good to me, that way I can change just the date of the sheet to get the whole month changed. Great! I played with "=DATE(YEAR(TODAY());12;3)" and added in the next cells for all three days a +7, worked too but your way is far more convinient, thanks! :) –  Phin Dec 19 '12 at 2:39
    
Hm, after I made a new sheet I get an error 501 now. I just translated it to german: =DATUM(JAHR(C1),MONAT(C1),mod(TAG(C1)-WOCHENTAG(C1,2)+1,7)) but Calc changes the cell-ID of the weekday to C10... why?: "=DATUM(JAHR(C1),MONAT(C1),mod(TAG(C1)-WOCHENTAG(C10,2)+1,7))" –  Phin Dec 25 '12 at 3:42
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.