I have a table like this:
Date Time-of-day
01/07/2024 12:30
02/07/3024 14:45
etc.
I use the Excel format-cell function to display the date and time correctly.
I want a scatter chart with date along the X-axis and Time-of-day on the Y-axis. The date range should cover just the values in the Date column.
But when I try to create it I get Y-axis showing the date, starting at 01/01/1990 - so totally not what I want. The X-axis shows the number range.
After much experiment:
Date Time-of-day
01/07/2024 12.50
02/07/2024 14.75
etc.
... with the Time-of-day custom formatted as NUMBER 00.00 the scatter
chart displays correctly. The time is hours plus decimal proportions of
an hour for minutes, which I can live with.
Is this a known bug?
Does it work properly on more modern versions of Excel?
Is there a more appropriate group where I should post this?
Is there a more appropriate group where I should post this?
Your 12.50 and 14.75 are 12-1/2 and 14-3/4 hours past midnight, if I understand your notation correctly. While they got the job done, you
didn't need to reinvent the wheel. Your 12.50 could have been
=12.50/24 and would then have displayed as 12:30 PM with a normal
Microsoft time format. Similarly, =14.75/24 displays as 12:45 PM.
I have 2003, 2010, 2019 and 2021. They all treat the issue pretty much
the same. It's not really a problem or an issue. I'll explain how I
get your scatter to display correctly, though you might have to fiddle
to make it fit your exact table.
I have a table like this:
Date Time-of-day
01/07/2024 12:30
02/07/3024 14:45
etc.
I use the Excel format-cell function to display the date and time
correctly.
I want a scatter chart with date along the X-axis and Time-of-day on the Y-axis. The date range should cover just the values in the Date column.
But when I try to create it I get Y-axis showing the date, starting at 01/01/1990 - so totally not what I want. The X-axis shows the number
range.
After much experiment:
Date Time-of-day
01/07/2024 12.50
02/07/2024 14.75
etc.
... with the Time-of-day custom formatted as NUMBER 00.00 the scatter
chart displays correctly. The time is hours plus decimal proportions of
an hour for minutes, which I can live with.
Is this a known bug?
Does it work properly on more modern versions of Excel?
One thing I didn't like testing this in 2010 version is that you cannot
hide the second set of columns that you are using as the data for the chart. If you do, the chart data disappears. I haven't checked if this
is the same with 2019 and 2021, but it doesn't work in 2010. You would
either have to put it in an area outside your viewing area, or you can
even place it in another tab sheet in the workbook. You just can't hide
it.
So what I would like is to display a number in the range 0.00 - 0.99 as
a time in the range 00.00 - 23:99 while leaving the encoding of the cell
as a number. Is there a way to achieve this?
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 361 |
Nodes: | 16 (2 / 14) |
Uptime: | 123:31:10 |
Calls: | 7,716 |
Files: | 12,861 |
Messages: | 5,727,956 |