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?

--
Graham J

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Mon, 1 Jul 2024 18:09:51 +0100, Graham J wrote:

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?

Not a bug, known or unknown. As far as I know, the storage format of
dates and times hasn't changed since about Excel 5. (I'm currently
running Excel 2010, but when I was in tech support I also had Excel
2013 and 2016, and they handled dates the same as 2010.)

The first thing to bear in mind is that a date and/or time is not an
actual data type, like Integer or Double Precision. Rather, it's a
number, coding date alone, time alone, or date and time -- the format
can be number or date/time, but the bits of the number are the same.

* A date with no time is a whole number, the number of days after 31
December 1899. Day 1 in the Microsoft era was 1 January 1900, and
today (2 July 2024) is day number 45475. If you format the number as
a date, it's a date; if you format the _same_ number as a number,
it's not a date. But the stored bits don't change.

* A date with a time is a number with a fractional part. The integer
part is interpreted the same as in the previous paragraph. The
fractional part is the part of a day that runs from midnight to the
time: multiply that fractional part by 24 to get hours and fractional
hours. For example, 45474.75 is 0.75 or 3/4 of the way through 2 July
2024. =24*.75 = 18 hours, namely 6 PM on that date.

* A time with no date is a decimal between zero and 1. It can be
interpreted like the time in the preceding paragraph, or like an
amount of time rather than a time of day you'd read off of a clock.
Thus 0.75 formatted as a time can be regarded as 6 PM on an
unspecified day, or 18 hours because 24*0.75 = 18.

Microsoft set up dates and times this way so that you could add a
number of days to a date and/or time, or subtract one, and get the
appropriate answer. Example: =TODAY()+6.5 is 12 noon (0.5*24 = 12
hours) 6 days after today, and TODAY()-0.25 is 6 PM yesterday. (0.25*
24 = 6 hours, but _before_ the beginning of the current calendar
day).

What time is it 11 hours and 42 minutes after 1:15 PM? Solution:
a. 11 hours and 42 minutes is (11+42/60) hours or (11+42/60)/24 =
0.4875 of a day..
b. 1:15 PM is (13+15/60)/24 hours or (13+15/60)/24 = .552083333333333
of a day past midnight.
c. Add the two and you get 1.03958333333333 days past midnight, or 0.03958333333333 days past midnight of the following day; formatted
as a time that's 12:57 AM of that day.

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.

--
Stan Brown, Tehachapi, California, USA https://BrownMath.com/
Shikata ga nai...

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Mon, 1 Jul 2024 18:09:51 +0100, Graham J <[email protected]>
wrote:

Is there a more appropriate group where I should post this?

comp.apps.spreadsheets
alt.comp.apps.spreadsheets


--
Steve Hayes from Tshwane, South Africa
Web: http://www.khanya.org.za/stevesig.htm
Blog: http://khanya.wordpress.com
E-mail - see web page, or parse: shayes at dunelm full stop org full stop uk

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Stan Brown wrote:[snip my original description]
[snip your detailed explanation of the way dates & times are stored

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.

Indeed the cell in the spreadsheet does display correctly.

Try this: make a spreadsheet using my values. Specify a header row.
Format Date column as "Date"; format Time-of-day column as "Number".
create scatter chart. It appears as I expect.

Now format Time-of-day column as "Time hh:mm:ss". Chart looks similar
but strange numbers annotate Y-axis.

Now delete the chart.

Without changing anything in the columns of data, create a new chart.
Date now appears on Y-axis starting at 01/01/1900, the X-axis is
annotated 0 to 2.5. Date and Time-of-day appear as 4 separate blobs in
the chart. Given that this was my starting point for creating a chart, I
was very confused.

Delete the chart.

Converting the cell containing the number 12.50 to time actually
displays 12:00:00 which comes from 12/01/1900 12:00:00 (as you
describe). But any new chart is created incorrectly.

So a chart created from a number in the Time-of-day column looks
correct, and continues to appear much the same after the Time-of-day
column is displayed as "time hh:mm:ss". But any new chart is created
wrongly.

My Time-of-day values are extracted from a date-stamp of the form
13/07/2024 10:06:00 so they are a number in the range 0.00 - 0.99.
While they remain in this form I can create a scatter chart that has the correct general appearance. I can multiply by 24 to get a y-axis that
looks like the time of day.

But if I change the display to "time" then create the chart it all goes
wrong.

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?


--
Graham J

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

sticks wrote:

[snip]

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.

Many thanks.

My issue was with the incorrect display of the chart when first creating
it from a correctly formatted table.

I understand your description of creating a chart from a table of
numbers then editing the display of the table - which is essentially the work-around I have now used. But this is for my own use, so I'm happy
to tolerate the perverse method used to get the chart as I would like.


--
Graham J

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 7/1/2024 12:09 PM, Graham J wrote:

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?

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.

First, you create the table with the date and time-of-day columns
exactly as you want with the information formatted as you would like to
see it displayed.

Next, copy both columns to another location and format them as numbers

Now create the scatter chart and select data using the two columns you
have selected to be formatted as numbers, NOT the columns in your table

Next right click on the axis and click "format axis" and under axis
options you can set the minimum and maximum numbers exactly as you have
them in your columns, or go a little lower or higher if you choose.

Next, scroll down in the format axis options to the second tab "number"
and you can select the chart to display the numbers as either date or
time as you would like it to appear.

You can fiddle around with the major and minor units to be displayed, as
well as some of the other categories to make it more easily understood.

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.

BTW, I have found 2003 to be like using XP. Yes, you can get it to open
files, especially if people help you out and save them in a format it
can open, but it lacks the ability to do some much the newer packages
can do I gave up on it. 2010 isn't bad, but there are still many things
it lacks in capabilities. 2019 is almost as good as 2021, and the
extras 2021 adds are things I don't care about. I don't recall exactly
but IIRC they were things like working on documents in an online
environment where others had the same access and stuff like that. But
as far as what they can do otherwise, 2019 and 2021 are basically the same.

HTHHAND!



--
Stand With Israel!

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 7/3/2024 10:39 AM, sticks wrote:

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.

I would like to add that I was wrong on this point and you *can* hide
data used for the chart or graph. You simply right click on the chart
and click "select data". On the "Select Data Source" page at the bottom
left corner is a button "Hidden and Empty Cells." Just click the "show
data in hidden rows or columns" and click ok, then OK to close out the
select data source box. It works!

--
Stand With Israel!

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Wed, 3 Jul 2024 10:16:07 +0100, Graham J wrote:

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?

No, because 23:99 is not a valid time. :-) I suspect you meant
23:59.

But I answered this question yesterday. As I said (and tested), Excel
2010 will interpret a decimal >=0 and <1 as a time, if you simply set
its format to time. (Most likely that applies to every Excel.) I
don't know how Excel actually computes the time, but whatever it does
with a decimal in Cell C2 is equivalent to
a. Hour to display = TRUNC(C2*24)
b. Minute to display = MOD(TRUNC(C2*24*60), 60)
c. Second to display = MOD(C2*24*60*60, 60)

If you have a date and time in a cell, you can give it a custom
format of "yyyy-mm-dd hh:mm:ss AM/PM" (without quotes) to display all
the information. (Testing this, I entered YYYY-MM-DD, and was
surprised when Excel changed it to yyyy-mm-dd. Apparently Excel
interprets mm as month number or minute number, depending on
context.)

If you have a date and time in, say, cell A1, you can extract just
the date as =TRUNC(A1) and just the time as
=MOD(A1,1) or =A1-TRUNC(A1).

--
Stan Brown, Tehachapi, California, USA https://BrownMath.com/
Shikata ga nai...

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)