public holidays

my timesheet looks like this. guess how i’ve spent the previous month!

more excel madness:

1. dropdown month selector

2. year open field

3. =DATE($C$6,MONTH(1&$B$6),1)

4. =IF(B9<DATE($C$6,MONTH(1&$B$6)+1,0),B9+1,””)

5. =TEXT(B9,”ddd”)

makes for a nice auto-updating calendar

worse of all was

6. =IF(B8=”sat”,8,IF(B8=”sun”,8,IF(ISERROR(VLOOKUP(B9,publicholiday,3,FALSE)),””,8)))

just coz excel doesnt have a simple function to check whether a text is found in a list and return a yes/no string. 3 nested IF statements plus ISERROR and VLOOKUP in order to check for weekends and public holidays, compared to just one IF and one OR for only weekends.

did you know excel can do up to 32 nested IF statements? it is a pain to close the brackets.

and some fluff:

7. =DAY(DATE(YEAR(B9),MONTH(B9)+1,))

to display number of days in the month. omg what is that huh.

public holidays

Leave a Reply

Your email address will not be published. Required fields are marked *