Dynamic Leave Calendar in Google Sheets

1
votes

I have a list of leave requests from my team, and I need a dynamic calendar made on google sheets which will automatically mark the cells against the dates on which a particular employee has placed leave requests as "On Leave" as soon as another leave request is added to the list.

It would be preferable if this can be done through a formula.

Link to the sheet: https://docs.google.com/spreadsheets/d/17o5u0d3BdwB3VxnHmFaWywUJnauptqlK0MvxspW6v2w/edit#gid=989074266

1
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygs-vlookup

1 Answers

0
votes

paste this in F2 cell and drag down:

=ARRAYFORMULA(IFERROR(SUBSTITUTE(SUBSTITUTE(TRANSPOSE(TO_DATE(ROW(
 INDIRECT("A"&DATEVALUE(A2)&":"&"A"&DATEVALUE(B2))))), A2, ), B2, )))

0

paste this in B2 cell and drag down:

=ARRAYFORMULA(IF(IFERROR(HLOOKUP(B$1:$1, SPLIT(TEXTJOIN(" ", 1, 
 QUERY('Leave Requests'!A$2:D, "select A,B where D='"&A2&"'", 0),
 FILTER('Leave Requests'!G:DD, 'Leave Requests'!D:D=A2)), " "), 1, 0))<>"", 
 "On Leave", ))

0