Scatter Chart to Show Durations

[HJay]

I am not sure if this is possible in excel but...

I have my data set up as Date, Event 1 Start Time, Event 1 Duration, Event 1 End Time, Event 2 Start Time, Event 2 Duration, Event 2 End Time. (see image)

What I would like is a scatter chart with Date on the x-axis. I want all the dates from the first day in the range to the last day in the range displayed, i.e. if an event didn't happen on a particular day I want there just to be a blank day.

I would also like time on the y-axis, I want the range to be midnight to midnight, i.e. a full day.

I would like to track events so if Event 1 occurred from 9:00 am and lasted 30 mins, I want to be able to see a vertical line from 9:00 to 9:30.

I also have more than one event type that I would like to see overlayed.

Is this possible? Copilot thought it was but it turns out that it was unable to explain how to do it...

Many thanks

Using Excel for Mac, Version 16.92 (24120731), 365 Subscription

Also posted here Scatter Chart with Event Duration

 

[Rijnsent]

Something like this? How I got to this:
- first, times in Excel are basically numbers from 0 to 1 where 00:00 equals 1, 12:00 equals 0,5 and 24:00 equals 1.
- create a scatterplot of the column "EV1 Start Time" (only that one) and set the X-values to the dates, that should get you something like this:

- next, make sure that the EV Duration column is not in minutes (so "30" for 30 minutes), but in a fraction of a day (so 00:30, which equals 1/48th). If you want to convert those numbers, the magic factor is 24*60 (so if you do: 30/(24*60) you get 00:30 .
- select the points, and through the menu "Chart design"->"add chart element"->"error bars" ->"more options for error bars"
- use the vertical error bar and choose "error display" -> "adjusted" to select for "positive error margin" the range in "EVI Duration"

I hope that gets you started!

Map1
	C	D	E	F
15	Date	EVI Start Time	EVI Duration	EVI End time
16	1-12-2024	09:00	00:30	09:30
17	4-12-2024	11:00	00:30	11:30
18	4-12-2024	10:00	00:30	10:30
19	4-12-2024	14:25	00:30	14:55
20	6-12-2024	09:00	00:05	09:05
21	6-12-2024	11:00	00:20	11:20
22	8-12-2024	10:00	00:45	10:45
23	8-12-2024	23:25	00:30	23:55
24	9-12-2024	09:00	00:02	09:02
25	10-12-2024	11:00	00:05	11:05
26	12-12-2024	10:00	00:10	10:10
27	12-12-2024	17:44	01:30	19:14
28	13-12-2024	23:05	00:37	23:42
29	17-12-2024	11:00	00:02	11:02
30	17-12-2024	22:00	00:49	22:49
Blad1

Cell Formulas
Range	Formula
E16:E30	E16	=(F16-D16)

 

[HJay]

View attachment 120401
Something like this? How I got to this:
- first, times in Excel are basically numbers from 0 to 1 where 00:00 equals 1, 12:00 equals 0,5 and 24:00 equals 1.
- create a scatterplot of the column "EV1 Start Time" (only that one) and set the X-values to the dates, that should get you something like this:
View attachment 120402
- next, make sure that the EV Duration column is not in minutes (so "30" for 30 minutes), but in a fraction of a day (so 00:30, which equals 1/48th). If you want to convert those numbers, the magic factor is 24*60 (so if you do: 30/(24*60) you get 00:30 .
- select the points, and through the menu "Chart design"->"add chart element"->"error bars" ->"more options for error bars"
- use the vertical error bar and choose "error display" -> "adjusted" to select for "positive error margin" the range in "EVI Duration"

I hope that gets you started!

Map1
	C	D	E	F
15	Date	EVI Start Time	EVI Duration	EVI End time
16	1-12-2024	09:00	00:30	09:30
17	4-12-2024	11:00	00:30	11:30
18	4-12-2024	10:00	00:30	10:30
19	4-12-2024	14:25	00:30	14:55
20	6-12-2024	09:00	00:05	09:05
21	6-12-2024	11:00	00:20	11:20
22	8-12-2024	10:00	00:45	10:45
23	8-12-2024	23:25	00:30	23:55
24	9-12-2024	09:00	00:02	09:02
25	10-12-2024	11:00	00:05	11:05
26	12-12-2024	10:00	00:10	10:10
27	12-12-2024	17:44	01:30	19:14
28	13-12-2024	23:05	00:37	23:42
29	17-12-2024	11:00	00:02	11:02
30	17-12-2024	22:00	00:49	22:49
Blad1

Cell Formulas
Range	Formula
E16:E30	E16	=(F16-D16)

Wow! Thank you so much! that is exactly what I want!

Thank you for explaining it so clearly and especially showing me how to change 30 to 00:30
Brilliant!