Calendar view

sahilhq

Hi,

I would like to set up something similar as shown in the screenshot below in Smartsheet. Dates for the rest of the summer to be shown in columns (vertically) and employee name in rows (horizontally). Once I have set this up on Smartsheets, would it be possible to switch to calendar view and in a nice format see which employee is on leave when? I realise that calendar view needs to have a date range (start and end date) so I am not sure if I will be able to achieve what I intend to. Please advise and provide any tweaks/suggestions so that I can set up my smartsheet accordingly. Thanks!

sahilhq

Update - so I have set up the sheet as per the template shared earlier (please see sheet set up below). I would like to now somehow display this information in calendar view. So anytime an employee (employee list in primary column) is on annual leave, I want the calendar to display their names on the dates they are due to be on annual leave. Is this possible? If not, is there a possible workaround to display the information in the calendar?

MCorbin

This isn't going to get you the exact format you want, but fairly close and it works well for me...

I set up a grid with my employee name, leave type, and start/finish dates. I have color coding (Conditional Formatting) set up based on which team each employee is on, but you could easily set it by person or leave type. The conditional formatting changes both the row and the Task Bar (important for calendar view)

I use the PRIMARY column for a formula that combines the employee name, type of leave, and # hours.

I display all that in the Calendar View. The color coding works in the Calendar view and the text from the Primary column displays.

If you want to continue in a Grid view like you have, you could consider using a Gantt view to give you the bars across the dates.

Hope that helps...

sahilhq

@MCorbin Hey! Many thanks for the response. That's a pretty impressive solution I must say. Would you mind sharing more details about your solution? The number and name of columns so I am guessing it is employee name, leave type and dates? Can you possibly share a screenshot? Can you also share the formula you are using to combine the employee name, type of leave, and number of hours? Thank you in advance!!!

MCorbin

Here you go:

Here's my raw data sheet. There's a form where users add PTO requests (supervisors only)

We track both planned PTO as well as unplanned (calling in sick, late, leaving early, etc). Because some data can be sensitive, access to the sheet itself is limited just to the department heads. Each Supervisor has a report that extracts their team's data, but is accessible to supervisors only. (Note: Because supervisors can't see the raw data sheet, they have a "Delete/Cancel" checkbox to indicate rows that should be removed. My metrics and reports exclude any row where that is checked).

The formula for the "PTO Detail" column (primary column) is: =[Team Member]@row + " (" + [# of Missed Hours]@row + " " + [Time of Day]@row + ")"

I then created a report that extracts the team's data (1 report per team). This report shows only the start date, end date, and PTO Detail columns.

I then PUBLISH the calendar, and make sure the publish link says to display in "Calendar View"

All of this rolls up into a Dashboard that provides the views, reports, data entry form, and team metrics for each supervisor: (Note, this is a fairly new solution, so we don't have metrics beyond the first 90 days yet).

Once last thing: Instead of choosing Calendar View for your report, you could choose GANTT view, which will give you a format closer to what you were originally looking for: (My conditional formatting is messing with the look of this a little, but you can play around with it and get the idea).

Hope that all helps

Melanie