Pivot table to display data as calendar

[cdfjdk]

I have a form that enters employee's travel plans into an Excel worksheet, including starting and ending dates - one row per travel plan.


I want to visualize these plans (start date to end date) in a calendar view in the same spreadsheet. However, I do not want to use VBA, because I do not want the spreadsheet to be macro-enabled. Also I do not want to export to Outlook.


I have looked for an example of a pivot table that could display data in a calendar layout without success - could anyone point me to an example?


Thanks in advance!

CJ

 

[sandy666]

post a link to shared example excel file (GoogleDrive, OneDrive or any similar) with raw data and expected result
remember to desensitize data if necessary

 

[cdfjdk]

post a link to shared example excel file (GoogleDrive, OneDrive or any similar) with raw data and expected result
remember to desensitize data if necessary

It is here
https://www.dropbox.com/s/jr59yugv8ixp8yn/Travel plan2.xlsx?dl=0

Cols A to K come from the form.

Many thanks for your help!

 

[sandy666]

How about PowerQuery? (Get&Transform)

basic table is transformed to be a proper source for PivotTable then simply PivotTable is created
IMHO you can't create PivotTable like you show, from your source table directly

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]End Date[/td][td=bgcolor:#DDEBF7]Name - Destination[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

09/06/2019​

[/td][td]Mary Smith - NYC USA[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

15/06/2019​

[/td][td]John Doe - GVA SWI[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

21/06/2019​

[/td][td]John Doe - Paris France[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

28/06/2019​

[/td][td]John Doe - Madrid SPA[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

02/07/2019​

[/td][td]John Doe - Sofia BLG[/td][/tr]

[tr=bgcolor:#FFFFFF][td]

12/07/2019​

[/td][td]John Doe - ROM ITA[/td][/tr]
[/table]

Travel Plan file

 

[SpillerBD]

Calendar View?
For the Pivot Table, each date would need data on the Travel plan, otherwise you only have a start date and end date popping on the Pivot Table.

Basically.
Travel days are valued at 1
The calendar is built by Years, Month and WeekNumber for the Rows. WeekDay # for the Columns. Label the calendar days with the Calendar day in Values.
Add the Travel days to the values and make sure the Sigma Values item is moved to the Rows.
You can use conditional formatting to highlight off days and use specific color for each person.

 

[cdfjdk]

Thank you Sandy666 and SpillerBD - it sounds like a pivot table would be a tough solution - I'll try exporting to a new workbook using VBA instead.