Plot time scale in Excel

#1 05-03-2005, 04:06 PM

I need help on plotting time in x axis with 15 minute intervals that will
roll over to the next day i.e midnight roll over.

Need this very urgently...

#2 05-03-2005, 09:06 PM

Shuvro,

Generally, per Excel’s help you can’t create time-scale charts from data
that is measured in intervals of hours, minutes, or seconds. Only days,
months, and years are considered base units in time-scale charts.

To get around this limitation, assume you have the following time data in
cells A1:A11 and plotted data in cells B1:B11:

Col A is Time Format Data
05/03/2005 23:00 6
05/03/2005 23:15 4
05/03/2005 23:30 5
05/03/2005 23:45 7
05/04/2005 0:00 6
05/04/2005 0:15 5
05/04/2005 0:30 3
05/04/2005 0:45 4
05/04/2005 1:00 5
05/04/2005 1:15 7

Insert a helper column between columns A and B. Add the following text
conversion formula to cell B1 (the new helper column):

=TEXT(A1,"mm dd yy hh:mm")

Copy that formula down the range B1:B11. Finally create your chart using
the range B1:C11.

Col A is Time Format Col B is Text Data
05/03/2005 23:00 05 03 05 23:00 6
05/03/2005 23:15 05/03/05 23:15 4
05/03/2005 23:30 05/03/05 23:30 5
05/03/2005 23:45 05/03/05 23:45 7
05/04/2005 0:00 05/04/05 00:00 6
05/04/2005 0:15 05/04/05 00:15 5
05/04/2005 0:30 05/04/05 00:30 3
05/04/2005 0:45 05/04/05 00:45 4
05/04/2005 1:00 05/04/05 01:00 5
05/04/2005 1:15 05/04/05 01:15 7

The 15 minute difference can be calculated a 1 / 24 / 4, or 0.010417

----------------------------
Regards,
John Mansfield
http://www.pdbook.com

"Shuvro Basu" wrote:

> I need help on plotting time in x axis with 15 minute intervals that will
> roll over to the next day i.e midnight roll over.
>
> Need this very urgently...

#3 05-04-2005, 06:06 PM

I am curious how this helper column chart differs from a chart with the=20
x-axis set to a 'category scale' (as opposed to 'time scale').

--=20
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
=3D Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

In article <396F633F-EC63-4FD8-9AB1-F2B6BA39F547@microsoft.com>,=20
JohnMansfield@discussions.microsoft.com says...
> Shuvro,
>=20
> Generally, per Excel=E2=A4=3D3Fs help you can=E2=A4=3D3Ft create time-sca=
le charts from data=20
> that is measured in intervals of hours, minutes, or seconds. Only days,=
=20
> months, and years are considered base units in time-scale charts.
>=20
> To get around this limitation, assume you have the following time data in=
=20
> cells A1:A11 and plotted data in cells B1:B11:
>=20
> Col A is Time Format Data
> 05/03/2005 23:00=09 6
> 05/03/2005 23:15=09 4
> 05/03/2005 23:30=09 5
> 05/03/2005 23:45=09 7
> 05/04/2005 0:00=09 6
> 05/04/2005 0:15=09 5
> 05/04/2005 0:30=09 3
> 05/04/2005 0:45=09 4
> 05/04/2005 1:00=09 5
> 05/04/2005 1:15=09 7
>=20
> Insert a helper column between columns A and B. Add the following text=
=20
> conversion formula to cell B1 (the new helper column):
>=20
> =3DTEXT(A1,"mm dd yy hh:mm")
>=20
> Copy that formula down the range B1:B11. Finally create your chart using=
=20
> the range B1:C11.
>=20
> Col A is Time Format Col B is Text Data
> 05/03/2005 23:00=09 05 03 05 23:00=09 6
> 05/03/2005 23:15=09 05/03/05 23:15=09 4
> 05/03/2005 23:30=09 05/03/05 23:30=09 5
> 05/03/2005 23:45=09 05/03/05 23:45=09 7
> 05/04/2005 0:00=09 05/04/05 00:00=09 6
> 05/04/2005 0:15=09 05/04/05 00:15=09 5
> 05/04/2005 0:30=09 05/04/05 00:30=09 3
> 05/04/2005 0:45=09 05/04/05 00:45=09 4
> 05/04/2005 1:00=09 05/04/05 01:00=09 5
> 05/04/2005 1:15=09 05/04/05 01:15=09 7
>=20
> The 15 minute difference can be calculated a 1 / 24 / 4, or 0.010417=20
>=20
> ----------------------------
> Regards,
> John Mansfield
> http://www.pdbook.com
>=20
>=20
> "Shuvro Basu" wrote:

#4 05-04-2005, 06:06 PM

Ideally, you should have date-and-time information on the x-axis. Since
your data are equally spaced along the x-axis, you can create any kind
of chart. For anything other than a XY Scatter chart, just ensure that
the x-axis type is set to 'category' (select the chart then Chart |
Chart Options... | Axes tab).

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

In article <46388E02-8EC2-4447-8E89-0762E694BE7B@microsoft.com>, "=?Utf-
8?B?U2h1dnJvIEJhc3U=?=" <Shuvro Basu@discussions.microsoft.com> says...
> I need help on plotting time in x axis with 15 minute intervals that will
> roll over to the next day i.e midnight roll over.
>
> Need this very urgently...
>

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)