Graph from tabel with multiple values in one kolom

[Gjanus]

Hello There,

English is not my first language so i hope i can explain myself


I get an export excel file from our planning software.

It gives the time traveled underneath each other

I would love to see a graph with on the X as the daytime (08:00 - 16:00 for instance)
A registration number is seen multiple times

And an the Y the (registration) numbers


I hope my attached file explains it a bit better.:
Grafiek.xlsx


Thanks

[protonLeah]

Since I do not know what Vertrek Kantoor and Vertrek klus mean, I am assuming they are like Start/Depart and End/Return times.
In the file the Named Ranges are:
.

• Nummer --> D9:D13
• VertrekKantoor --> E9:E13
• AankomstKlus --> F9:F13
• VertrekKlus --> G9:G13

.

In J9:AC14 use:

Please Login or Register  to view this content.

To count if the times in the table match the Grid Header (J8:AC8) times.
use conditional formatting on the grid with:=J9=1 for red and J9 > 1 for yellow (overlap (may not be necessary))

[Gjanus]

Thanks protonleah.

Looks nice.

one problem - the export is in minutes.
But i don't think the company minds if it is in quarter of an hour / half an hour.

Thanks again

[protonLeah]

You don't have to have the data validation list in columns E, F & G. You can clear/delete it and manually type in any time value.
Instead of 30 minute intervals in the sample grid I posted, you could have shorter intervals such as 5, 10 or 15 minutes; the grid will get much bigger but the formulas should still work.

[Gjanus]

Thanks again. Works nice