Log In
Register
Home
May 18, 2023
Offline
I've been working on a leave tracker calendar (attached) for my team at work. It is based on one that is currently being used, but which has no automation or conditional formatting - making adding entries a rather tedious process.
I know most leave calendars tend to have the user list each individual of leave, and then conditionally format using duplicates (similar to what I have done for the public holiday list) - but this really only works where the leave is for a short period (one week or less).
As such, I've tried creating a list that has start and end dates to, hopefully, format the range of leave.
There are two issues that I am currently having:
1) Initially I tried using XLOOKUP to find the start and end dates of the range. Unfortunately, this only returns the first instance of a leave type (I understand this is a limitation of XLOOKUP) so I tried switching to FILTER - except this didn't work at all (typing it into a cell returns a FALSE result, and I know conditional formatting only works with TRUE results).
2) When it comes to flexible working, staff have the choice of opting into a 9-day fortnight, giving them a day-off every second week. This option also only lasts for 6 months before staff have to reapply, and the day off may change from one period to the next.
So this has the same issue as above when it comes to formatting within a range; however, I would also need a formula to only format every second week of whichever day the RDO falls on.
I contemplated using MOD for this; however, I'm not sure what the correct formula would be.
Any help that people can offer would be greatly appreciated.
Moderators
January 31, 2022
Offline
No file was attached. Please try again. Press the 'Start upload' button before you submit your reply.
May 18, 2023
Offline
My apologies. I did hit the "start upload" button, so I'm not sure if my editing the post before it went live would have affected things.
May 18, 2023
Offline
So I've worked out that I can use Power Query to list all of the dates in a range (for example, expanding all "rec leave" dates into a single table) - which therefore allows me to use conditional formatting to find duplicates.
The only issue with this is that I would have to manually set-up the queries on the worksheet for each staff member, otherwise each query just references the original table.
Fortunately, there are only 10 people in my team so this is doable - and will probably be the route I go unless/until I find something better.
I'm still stuck on a potential formula to only format a particular day in every second week for people on a 9-day fortnight, but I'm investigating whether there is a way to use Power Query for this too.
May 18, 2023
Offline
So, after a couple of week of fiddling, I've managed to get everything working as expected thanks to a handy formula I found online to convert date ranges into stacked columns.
The formula for the 9 day fortnight column is still very clunky and probably needs to be cleaned up, but I can't get my head around how to do it.
Still, I'm very happy with the result!
July 16, 2010
Offline
Very nice, Todd. I'm not sure where the 9 day fortnight column is that you're referring to.
Here's a tip for conditional formatting:
This formula:
=ISNUMBER(MATCH(E6,Range_Holidays,0))
Can be written:
=MATCH(E6,Range_Holidays,0)
Because any time a number is returned other than zero, Excel will treat the result as TRUE for the purpose of conditional formatting (among other things). Zero and errors are treated as FALSE.
This means you can simplify all conditional formatting formulas that use ISNUMBER. It doesn't make a huge difference in this file, so probably not worth changing them. Just something to remember for future.
Mynda
May 18, 2023
Offline
Thanks Mynda.
Definitely a helpful tip regarding ISNUMBER. I'll keep that in mind for future updates to the file.
Also, I realise now that referring the 9 day fortnight "column" as a column is a misnomer. It's actually just a range, the formula for which sits in cell H60.
As I said, it does what I need it to - but it's very clunky and inelegant.
July 16, 2010
Offline
Thanks for clarifying, Todd. That sure is a doozy! I couldn't follow how you're supposed to fill out the flexible working table to trigger the formula to return a result, so it's difficult to see what it's supposed to return in order to make sense of it.
Mynda
May 18, 2023
Offline
I forget that most people's brains don't work exactly like mine does, so I've thrown some data in to help clarify.
There definitely will need to be user instructions added at some point.
July 16, 2010
Offline
Hi Todd,
Thanks for clarifying. I simplified it from this:
=IFERROR(VSTACK(FILTER(FILTER(DROP(SORT(UNIQUE(TOCOL(D60+DROP(REDUCE(0,E60-D60,Custom_Range),1)))),0),IF(NOT(ISBLANK(DROP(SORT(UNIQUE(TOCOL(D60+DROP(REDUCE(0,E60-D60,Custom_Range),1)))),0))),WEEKDAY(DROP(SORT(UNIQUE(TOCOL(D60+DROP(REDUCE(0,E60-D60,Custom_Range),1)))),0)),"")=F60),MOD(FILTER(DROP(SORT(UNIQUE(TOCOL(D60+DROP(REDUCE(0,E60-D60,Custom_Range),1)))),0),IF(NOT(ISBLANK(DROP(SORT(UNIQUE(TOCOL(D60+DROP(REDUCE(0,E60-D60,Custom_Range),1)))),0))),WEEKDAY(DROP(SORT(UNIQUE(TOCOL(D60+DROP(REDUCE(0,E60-D60,Custom_Range),1)))),0)),"")=F60),2)=1),IFERROR(FILTER(FILTER(DROP(SORT(UNIQUE(TOCOL(D61+DROP(REDUCE(0,E61-D61,Custom_Range),1)))),0),IF(NOT(ISBLANK(DROP(SORT(UNIQUE(TOCOL(D61+DROP(REDUCE(0,E61-D61,Custom_Range),1)))),0))),WEEKDAY(DROP(SORT(UNIQUE(TOCOL(D61+DROP(REDUCE(0,E61-D61,Custom_Range),1)))),0)),"")=F61),MOD(FILTER(DROP(SORT(UNIQUE(TOCOL(D61+DROP(REDUCE(0,E61-D61,Custom_Range),1)))),0),IF(NOT(ISBLANK(DROP(SORT(UNIQUE(TOCOL(D61+DROP(REDUCE(0,E61-D61,Custom_Range),1)))),0))),WEEKDAY(DROP(SORT(UNIQUE(TOCOL(D61+DROP(REDUCE(0,E61-D61,Custom_Range),1)))),0)),"")=F61),2)=1),""),IFERROR(FILTER(FILTER(DROP(SORT(UNIQUE(TOCOL(D62+DROP(REDUCE(0,E62-D62,Custom_Range),1)))),0),IF(NOT(ISBLANK(DROP(SORT(UNIQUE(TOCOL(D62+DROP(REDUCE(0,E62-D62,Custom_Range),1)))),0))),WEEKDAY(DROP(SORT(UNIQUE(TOCOL(D62+DROP(REDUCE(0,E62-D62,Custom_Range),1)))),0)),"")=F62),MOD(FILTER(DROP(SORT(UNIQUE(TOCOL(D62+DROP(REDUCE(0,E62-D62,Custom_Range),1)))),0),IF(NOT(ISBLANK(DROP(SORT(UNIQUE(TOCOL(D62+DROP(REDUCE(0,E62-D62,Custom_Range),1)))),0))),WEEKDAY(DROP(SORT(UNIQUE(TOCOL(D62+DROP(REDUCE(0,E62-D62,Custom_Range),1)))),0)),"")=F62),2)=1),""),IFERROR(FILTER(FILTER(DROP(SORT(UNIQUE(TOCOL(D63+DROP(REDUCE(0,E63-D63,Custom_Range),1)))),0),IF(NOT(ISBLANK(DROP(SORT(UNIQUE(TOCOL(D63+DROP(REDUCE(0,E63-D63,Custom_Range),1)))),0))),WEEKDAY(DROP(SORT(UNIQUE(TOCOL(D63+DROP(REDUCE(0,E63-D63,Custom_Range),1)))),0)),"")=F63),MOD(FILTER(DROP(SORT(UNIQUE(TOCOL(D63+DROP(REDUCE(0,E63-D63,Custom_Range),1)))),0),IF(NOT(ISBLANK(DROP(SORT(UNIQUE(TOCOL(D63+DROP(REDUCE(0,E63-D63,Custom_Range),1)))),0))),WEEKDAY(DROP(SORT(UNIQUE(TOCOL(D63+DROP(REDUCE(0,E63-D63,Custom_Range),1)))),0)),"")=F63),2)=1),"")),"")
To this:
=LET(
Dates,VSTACK(
IFERROR(FILTER(WORKDAY.INTL(D60,SEQUENCE(SUM(--(WEEKDAY(SEQUENCE(E60-D60+1,,D60),12)=1))),XLOOKUP(F60,Data_Days[NO.],Data_Days[Days])),MOD(SEQUENCE(SUM(--(WEEKDAY(SEQUENCE(E60-D60+1,,D60),12)=1))),2)),""),
IFERROR(FILTER(WORKDAY.INTL(D61,SEQUENCE(SUM(--(WEEKDAY(SEQUENCE(E61-D61+1,,D61),12)=1))),XLOOKUP(F61,Data_Days[NO.],Data_Days[Days])),MOD(SEQUENCE(SUM(--(WEEKDAY(SEQUENCE(E61-D61+1,,D61),12)=1))),2)),""),
IFERROR(FILTER(WORKDAY.INTL(D62,SEQUENCE(SUM(--(WEEKDAY(SEQUENCE(E62-D62+1,,D62),12)=1))),XLOOKUP(F62,Data_Days[NO.],Data_Days[Days])),MOD(SEQUENCE(SUM(--(WEEKDAY(SEQUENCE(E62-D62+1,,D62),12)=1))),2)),""),
IFERROR(FILTER(WORKDAY.INTL(D63,SEQUENCE(SUM(--(WEEKDAY(SEQUENCE(E63-D63+1,,D63),12)=1))),XLOOKUP(F63,Data_Days[NO.],Data_Days[Days])),MOD(SEQUENCE(SUM(--(WEEKDAY(SEQUENCE(E63-D63+1,,D63),12)=1))),2)),"")
),
FILTER(Dates,ISERROR(XLOOKUP(Dates,Range_Holidays,Range_Holidays))))
You could probably do more, but I ran out of time!
The XLOOKUP Data_Days[Days] is explained here: https://www.myonlinetraininghub.com/excel-functions/excel-workday-intl-function
Mynda
May 18, 2023
Offline
Wow!
That is a massive improvement, and so much more elegant.
Thank you so much, Mynda. Definitely plugging that one in now.
July 16, 2010
Offline
Cheers, Todd. I think this is probably a candidate for a recursive lambda function, but I didn't have the headspace to write it last night!
1 Guest(s)
