Forum Discussion
Time off from List to Timeline
Dear Community, here is the case:
I just inherited a team of 15 people who are used to input their time off in an excel sheet with 3 columns: Name, Start Date, End Date. My idea is to create a Timeline from that. The issue I'm having is that a user can have more than one record for time offs.
I've found a couple of formulas online for the timeline. The problem I'm having is that names repeat multiple times in the time off sheet. For example someone can have a couple of day offs in June and some more in July.
So, my idea is to have a timeline with unique names and all days off for that person.
I've attached what I have so far.
8 Replies
- What I do please hlep
- The verification code does not reach the mobile phone
That has nothing to do with his discussion. Please start a new discussion and ask your question there.
Please read Welcome to your Excel discussion space! before doing so.
"I've attached what I have so far.". Unfortunately nothing is attached. It's better to discuss an issue based on your sample data.
- Sorry, I just noticed I need to figure out how to attach my excel file.
If you cannot attach a file here, you can make it available through OneDrive, Google Drive, Dropbox or similar. Obtain a link to the uploaded and shared file and paste that link into a reply.
One solution to create a timeline with unique names and all days off for each person would be to use a PivotTable.
Here are the steps:
- Select the range of cells that contain the time off data, including the headers.
- Go to the Insert tab and select "PivotTable".
- In the Create PivotTable dialog box, make sure the range is correct, and choose to create the PivotTable in a new worksheet or existing worksheet.
- In the PivotTable Fields pane, drag the "Name" column to the Rows section and the "Start Date" column to the Columns section.
- Drag the "End Date" column to the Values section.
- Click on the arrow next to the "End Date" field in the Values section, select "Value Field Settings", and choose "Count" as the summarization function.
- Now you have a PivotTable that shows each person's name and the number of days they took off in each month.
- To create the timeline, select the PivotTable and go to the Insert tab. Select "Timeline" and choose the "Start Date" field.
- In the Timeline dialog box, choose the date range for your timeline and click OK.
- Now you have a timeline that shows each person's name and all the days they took off during the selected time period.
Note: You may need to adjust the PivotTable and timeline settings to fit your specific needs, but this should give you a good starting point.
- Thanks for the suggestion. I'll give it a try!!
