Forum Discussion

HarryHuckerby's avatar
HarryHuckerby
Copper Contributor
Sep 18, 2023

Mapping data into a calendar on excel

Hi, 

 

I'm currently building an annual leave tracker whereby a power automate flow automatically populates an excel table with names and dates once a submisson is approved. 

 

Currently i have the data table and a 'calendar' dashboard in excel but I am unsure on the formulas needed to essentially map the annual leave data into the calendar.

 

Currently what i want to do is include a H in the calendar for an annual leave day and a h for a half day. 

 

It currently looks like this, any help would be appreicated.

 

 

 

excel
Formulas and Functions

1 Reply

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Sep 18, 2023

    HarryHuckerby 

     

    I presumed you don't want to include the 'H' or 'h' on weekends. Here's my solution:

     

    =LET(
    rows, ROWS(Staff),
    cols, COLUMNS(AllYear),
    Leave, LAMBDA(r, c,
        LET(
            date, INDEX(AllYear, , c),
            person, INDEX(Staff, r),
            IF(
                WEEKDAY(date, 2) > 5,
                "",
                FILTER(
                    Table1[Code],
                    (Table1[Employee] = person) * (date >= Table1[First Day of Leave]) *
                        (date <= Table1[Last Day of Leave]),
                    ""
                )
            )
        )
    ),
    MAKEARRAY(rows, cols, Leave)
)

     

Resources