Apache OpenOffice Community Forum

Hey guys, I need assistance in building a calendar from scratch using formulas. So far I've figured out how to use the Validity function to create a drop down menu and state the month (as a number, cant seem to figure out how to set January to =1, literally tried to figure this out for hours etc ), and the Year. Then using the date formula i created the initial date of the month. How can I improve it from here? I'd like to have it automatically (using formulas of course) set holidays and etc.

Thanks.

Setting January to the first month is automatic in any of the date functions.
Out of curiosity, why are you trying to do a calendar when there are calendar templates already available?

I suggest looking at the various date functions in the help file, as I suspect you'll find a few of them handy.

Just needed some guidance on an assignment. I'd like to do well, my calendar works, its just that I'd like it to be better.

Also about the date function, I set it as =Date(A2,A1,1) referencing my drop down year and month menus. A1 is the month. If I use that it gives me a value error, but it you set A1 to 1 it works fine.

I've found the Easter fuction, which is nice. I can't figure out how to input weekdays into my basic calendar.

I actually meant looking in the Index of the Help file for date and time functions, not the DATE function alone.
I think you'll find the DATEVALUE function handy, as my experiments have shown good results
You may/will also need to work with the CONCATENATE and/or TEXT functions, inside the DATEVALUE function.

As to getting the dates under the correct Weekday names, the WEEKDAY function, I think, would be helpful

Hi, and welcome to the forum.

mydadismyhero wrote:cant seem to figure out how to set January to =1

To convert "January" to 1, "February" to 2, etc. use [Tutorial] VLOOKUP questions and answers (or MATCH).
To convert the other way (1 → January, 2 → February, …) use either VLOOKUP or CHOOSE.

mydadismyhero wrote:its just that I'd like it to be better

Building a calendar is a great way to learn about Calc. But you might want to look at some of the calendar templates to get some ideas for how to improve what you've created. Or look at one I developed about ten years ago. I've learned quite a lot since then and perhaps wouldn't do it the same way now. If this solved your problem please go to your first post use the Edit button and add [Solved] to the start of the title. You can select the green checkmark icon at the same time.

[Tutorial] Ten concepts that every Calc user should know

Here's another example. Cell B4 (invisible unless value higlighting is used) determines the first day of the week.

mydadismyhero wrote:... cant seem to figure out how to set January to =1, ...

If A1 contains a month name,

=MONTH(DATEVALUE(A1&" 1, 1900"))

will convert it to the proper month number: Jan=1, Feb=2, ...

This will only work if the locale accepts input dates in the form Jan 1, 1900 but since you're using English month names, this shouldn't be a problem.

Thanks everyone! Helped a tonne. I've gotten a lot further with the calendar using a combination of all this advice.

Currently trying to use get WEEKDAY to display the names of the week rather than numbers. I'm guessing the best way to do this is to use VLOOKUP.

Also, is there is a function that highlight a cell (or make it stand out) if the cell is a certain value (date, this is for holidays)? I'd like public holidays such as Christmas and etc. to stand out on the Calendar.

Once again, thanks for the help.

For the second part of your latest post, look at Conditional Formatting
I have something running through my head for the first part, but I'm drawing a blank as to the actual function name

mydadismyhero wrote:Currently trying to use get WEEKDAY to display the names of the week rather than numbers. I'm guessing the best way to do this is to use VLOOKUP.

That's not necessary. Just format a date with format code NN or NNN to display the day of the week.