Forum Discussion
Change X-Axis scale to a time format on a scatter chart
Doree515 Axis options are set to automatic by default. So Excel tries to create a nice looking axis with not too much clutter. You can override that by right-clicking (mouse) or two-finger-clicking (track pad) on the axis. Select "Format Axis..." Now, in the Options tab you can set the Bounds and Units for the axis.
BUT, it's not as straight forward with time values as it is with "normal" numbers. Time is stored as numbers, relative to one whole day of 24 hours (or 1440 minutes).
The above settings will create an axis from 00:00 (Minimum 0) to 08:00 (Maximum 0.33333). Note that the time value for 08:00 (8 AM) = 8 / 24 = 0.33333
Similarly, the Major Unit is set by taking the number of minutes between each label and divide that by 1440. Above I took 30 minute intervals. Thus, 30 / 1440 = 0.020833
The Minor unit calculated automatically.
In your particular situation, try setting the lower bound to 0.7986111 (being the time value for 01:55 i.e. 115/1440). Play around a bit with the upper bound and major units until you have the axis that looks good for you.
Doree515 Axis options are set to automatic by default. So Excel tries to create a nice looking axis with not too much clutter. You can override that by right-clicking (mouse) or two-finger-clicking (track pad) on the axis. Select "Format Axis..." Now, in the Options tab you can set the Bounds and Units for the axis.
BUT, it's not as straight forward with time values as it is with "normal" numbers. Time is stored as numbers, relative to one whole day of 24 hours (or 1440 minutes).
The above settings will create an axis from 00:00 (Minimum 0) to 08:00 (Maximum 0.33333). Note that the time value for 08:00 (8 AM) = 8 / 24 = 0.33333
Similarly, the Major Unit is set by taking the number of minutes between each label and divide that by 1440. Above I took 30 minute intervals. Thus, 30 / 1440 = 0.020833
The Minor unit calculated automatically.
In your particular situation, try setting the lower bound to 0.7986111 (being the time value for 01:55 i.e. 115/1440). Play around a bit with the upper bound and major units until you have the axis that looks good for you.
Hi Riny,
Thank you!!! You taught me something that I will share with my class. I have a new time format issue, and I hope you can help me. I want to create a scatter chart with my marathon finish times on the X-Axis and my pace per mile on the Y-Axis. I created the chart and the data points were plotted. The range on the Y-Axis was greater than I needed, so I adjusted the bounds using the info you shared earlier. The bounds on the X-Axis were 2, 4, 6, 8, 10, and 12, so I wanted to change them to match my other chart. When I entered the minimum and maximum bounds all data points disappeared from the chart, and I don't know why. Do you have any suggestions on how I can fix this?
- I had the same issue with my graph: after adjusting the limits to a certain time period using the decimal format, all my data disappeared. It turned out my data was in Date AND Time. So for example, 16:13 is 0.676 as decimal (16x60 + 13)/1440
but the cell entry was 2023-08-18 16:13. So to include the date, I had to convert it to number as well (45156; Excel counts January 1, 1990 as 1 and increments up from there). I didn't calculate the number value of the date myself, Excel actually displayed it for me. So I had to set my lower limit as 45156.0676 or else the range of my graph would be way back in time. Doree515 Difficult to answer without seeing the chart. Can you share it via OneDrive or similar?
- Hi Riny, I can share it via One Drive, although I'm not sure how. I have two versions with different time/date formatting issues when charting. I saved the documents to the drive. How do I send them to you?
Doree515 Perhaps easiest to click on my name tag and send a file to me by a direct message.
