Line graph from date ranges

Question

I have a list of guests in Excel. After each guests name there is a from date, and a to date. Obviously indicating the date range they are going to stay.

How do I make a line graph that shows how many guest we have on any given day, based on thees ranges?

Eg.

GUEST NAME     ROOM      DATE FROM     DATE TO
John Doe       101       26.03.14      01.04.14
Peter Parker   208       24.03.14      28.03.14
Some guy       105       25.03.14      29.03.14
Superman       101       01.04.14      15.04.14
Wonder Woman   101       01.04.14      15.04.14

Answer 1

Suppose the data provided is contained in the range A1:D6. Find the minimum and maximum dates for items in the list. Let F2:F24 contain dates for each day between the minimum and maximum dates and the corresponding cells in G2:G24 contain the formula that will count the number of guests for each day. Assuming you are using Excel 2007/2010/2013, in G2 you would have the formula =COUNTIFS($C:$C,"<="&F2,$D:$D,">="&F2). You would then fill this formula down from G2 to G24. In order to obtain a line graph, you just create a graph with source data in F1:G24. Hope this helps.

Note: I changed the date format in your example data from dd.mm.yyyy to mm/dd/yyyy, so this is what you're seeing in the screenshot above.