[Solved] Calendar for Staff Vacations

[Cat101]

I've downloaded several calendar templates, but I'm now trying to take it to the next level...

The template I'm modifying does the basic stuff with changing dates with years & notes holidays & events, but I now want to also indicate requested vacations & approved vacations on a second calendar which only has dates closed in red. This is a one page calendar, with the ability to see who's specifically gone on the next page.. Im toying with managers being able to toggle between seeing the overall out or just for their department.. but I've got to get past this hurdle.

I've created an area for either managers or staff to enter start date for vacation & end date. With that information, I created a row of which specific dates that time frame that will encompass. ie They enter start 1/3/22 return 1/10/22, so to the right is 1/3/22; 1/4/22;1/5/22 etc to 1/9/22 (Im not worried about weekends or holidays at this point).

This area is random, depends on when vacation requests are received. I'm working with conditional formatting & limited to 3 conditions. I apparently can't make an array of these date, cause they are formulas.

Im just trying to highlight the dates that staff is out of the office on the calendar.. I've tried more formulas then I care to remember, VLOOKUP, HLOOPUP, INDEX, etc. But due to the randomness of where the date may be, I've at a loss... I can indicate (highlight) start but that's been about it & that all depends on the day.. I've used INSERT > Names & tried to grab the area for MATCHing, but no go.

(If I've missed one of the millions of calendar templates both here & on the net.. opsy sorry, I haven't found one yet)

ANY SUGGESTIONS

(1-7-22 I've attached the file - I also have sheet7 where I copied & pasted special - text & numbers - just to try another way)

[Edit2]You gave me a great idea, rather then do the calendar template - do a vertical calendar for vacations. Not quite as compressed to a single page, but I'm obsessed with finding a good way of representing this.. with COVID scheduling & monitoring absences/staffing is a big challenge today..thanks for the idea [/Edit]

[Lupp]

Werlcome to the forum!

A few suggestions:

-1- Create an anonymized version of your spreadsheets document (also not containing any confidential information) and attach it to your question by editing.

-2- "Im toying with managers ..." Is your "managers" software or persons? You might start "toying with a Global English". Most contributors here aren't familiar with US slang, e.g.

-3- (Will be shown also by your sheet, of course.) If talking of "dates" entered into a spreadsheet or returned in a cell based on a formula always state explicitly if they are text(strings) or numbers.

<edit1: same day, about 11:45 UTC>
I found the time to make an example showing how I would approach the job.
In no case I would waste time searching for templates. All the (few) calendar templates I saw the last 20+ years were of the my-idea-of-a-prettyprint-calendar-type, and therefore not flexible enough to adapt as special purposes require, and very difficult to use with the help of formulas. (Often due to the one-row-per-week-for-a-month-design which only should be pondered for a wall calendar with pictures.)
Always: First implement the needed functionality completely!
In special cases: Add dedicated sheets for prettyprint then if absolutely necessary.

aoo106899expectableAbsences.ods

(15.04 KiB) Downloaded 211 times

</edit1>

[Cat101]

Thanks for the input, I'll remember the audience..
Let me sanitize what I've done & load..

[Cat101]

I'm back to this project & working to complete..
I have this formula in Conditional Formatting,
MATCH(A9;Holiday;0)
but can't remember How I created this "Holiday" array..
Once I complete, I'll post my finished work..
any help is appreciated..

[robleyd]

Probably you used a Named Range - Insert | Names | Define.

[Cat101]

YEP.... BINGO

Now the answer is forever here the next time I forget

[Cat101]

Is there another way to get to Styles other then [F11] and then right click.. I've updated to Open Office 4.1.11 and now I can't modify, delete any of the styles I had been testing.. Would an upgrade cause this?

I've rebooted my PC and [F11] still ignores my request to Modify.. Even my conditional formatting changes are acting funny. I've tried copying the entire file, thinking that might change something.. But it didn't..

I haven't tried copying sheet by sheet into a new file, which is probably next.

Any thoughts? Is there a known bug?

[Hagar Delest]

You can't delete a style that is applied somewhere.
No idea why you couldn't modify your styles...

[Villeroy]

Im just trying to highlight the dates that staff is out of the office on the calendar.. I've tried more formulas then I care to remember, VLOOKUP, HLOOPUP, INDEX, etc. But due to the randomness of where the date may be, I've at a loss...

This is simply because you (and the vast majority of today's spreadsheet users) insist in using the wrong tool. A spreadsheet is not a database, no matter how many sophistication, blood, sweat and Basic code you invest. Your example document is too complicated to become usable at any time in the future. A spreadsheet is an arithmetic calculator. It was never intended to be a database surrogate. Even though very simple and small pseudo-databases on sheets are possible, you will never get an acceptable user interface. Constantly dragging around formulas and taking care of correct references to growing list ranges with no separation of data, processing and representation is a nightmare.
------
I have one such database on a single sheet. It is one flat list with auto-filter buttons and calculated hyperlinks to web-sites growing by one or max. two copy&paste entries per week. It is nothing particularly important. All the real payment data come from a real database. Nothing would be lost when the spreadsheet is corrputed. Just a convenient way to keep track of some detail and generate hyperlinks.

[Cat101]

hummm... Villeroy, I've done other things in openoffice database, usually in accounting or marketing, but never considered it for this.. of course, it's changed from when I first posted, but underlying focus is the same.. but hadn't considered a database for this.. I may step back over the next two days to consider this & what it might mean & how to structure it..

SMH..I get into a box when designing & seldom walk out of that box til done.. Thanks for the insight....

[Villeroy]

Designing a databas is development work, but at least you have a tool kit that deals with record sets.
There are dozends of ready made calendar apps.
And there are spreadsheets made of paper.

[Villeroy]

Have a look at this database. It has a table of persons, a table of vacations and a filter table storing filter criteria.
In the form you edit all vacations ordered by date (latest on top) and you can filter them by person and/or by time interval.
One report shows the filtered vacations in printable form. Another report shows all vacations. A third one sums the vacation days per calendar year and person.

Edit: Fixed a minor issue after 2 people downloaded the first version.

The minor issue was that I allowed missing until-dates for 1-day vacations but forgot about this extra. The queries did not handle records with missing until-dates. I modified the queries, so the day counter counts these vacations as 1 day and the filtering queries set missing until-dates equal to the from-date.

[Villeroy]

With a better database engine, we can dump person names into a spreadsheet calendar.
1) Download https://www.mediafire.com/file/hdw55c0l ... s.zip/file
2) Extract the zip to a trusted directory. See Tools>Security>[Macro Security...] "Trusted Sources"
3) Open the database document. A macro will connect the odb to the external database and register itself as data source "Vacations". Save and close the database document.
4) Open the spreadsheet. Its calendar sheet shows the entered vacations. Every time you open this document, a tiny macro updates the database import on the holydays sheet.

The reason why I switched to an external database: The old HSQL database engine that is shipped with the office suite can not concatenate names sharing the same vacation day. HSQL2 has an aggregation function GROUP_CONCAT which does that job.

[Cat101]

Okay, This is my formula
=COUNTIFS($A$2:$P$75;A2;$A$2:$P$75;"PM")

If I just do COUNTIF on each item then they work,
COUNTIF($A$2:$P$75;A2) << works
COUNTIF ($A$2:$P$75;"PM") << works

But when I put them together I just get 0

If I understand COUNTIFS.. it should only count if both elements are true??
Im trying to count the number of (whole)Day/AM/PM they requested off..

Any suggestions??

I'd really like to add a third conditions - approved, not approved..

But let's see about this first :/

(Sorry Villeroy, I just noticed your post above.. I tried loading it & hardware tech has this PC locked down on receiving macros mayb later)

[eeigor]

Change COUNTIFS() with SUMPRODUCT()
=SUMPRODUCT($A$2:$P$75=A2;$A$2:$P$75="PM")

=COUNTIFS($A$2:$P$75;A2;$A$2:$P$75;"PM")
But when I put them together I just get 0

Because these two conditions must be met simultaneously, and cell A2 must contain "PM" for the conditions to be met.

[Cat101]

i GET ALL ZEROS WITH "SUMPRODUCT" AS WELL...

But just noticed you stated "Because these two conditions must be met simultaneously, and cell A2 must contain "PM" for the conditions to be met." Im not sure why that is said. Im searching for two separate conditions to be met - A2 AND PM..
A2 happens to be a name.. and PM is a shift..

But you did give me an idea,,
Thanks for the suggestions

[Cat101]

Nope, I just tried to make the area to searched a bit smaller
=COUNTIFS($C$2:$E$75;A2;$C$2:$E$75;"AM") - still no count
=SUMPRODUCT($C$2:$E$75=A2;$C$2:$E$75="AM") - still no count

-- Its the little things that are sometimes the hardest..

[Cat101]

I thought I had looked, but I see this is a better place for this issue.. viewtopic.php?p=514023

[eeigor]

BTW, all holidays have been SHADED and BOLDED.
I can't understand, how did you achieve this? I don't see any set font properties.

[Cat101]

I used conditional formatting

[Cat101]

I've finalized this calendar, I'll be taking it live now.. But when I'm back to redoing this.. maybe in a year, or two, or three, or sooner...I will DEFINITELY do in a database!! This spreadsheet has established all the datafiles I'll need & I'm sure it will be MUCH easier.

I only started doing spreadsheet in the last few years, and only by kicking & screaming, cause my normal world is big systems & big relational databases or big systems with flat files or indexed files. But once I started playing, it's been kind of fun. And with the drive to more .csv files, actually beneficial. The willingness of all the volunteers to share the complex with us newbies is very impressive & all that helped me deserve a beer & pizza

Calendar4.ods

(61.42 KiB) Downloaded 185 times