+ Reply to Thread
Results 1 to 36 of 36

No way to change the timescale to minutes !

  1. #1
    Registered User
    Join Date
    01-08-2016
    Location
    Brussels
    MS-Off Ver
    Office 2016 Mac
    Posts
    17

    No way to change the timescale to minutes !

    Hi,

    I have downloaded an Excel template from Microsoft here

    The timeline unit is DAY (we can choose from DAY, MONTH or YEAR). We have to enter beginning and ending dates to limit the graphic.

    I try to modify the unit to MINUTE. As my project to be charted stands for less than an hour, I try to put beginning limit to 00:00:00 and ending to 01:00:00.

    But I can't manage that:
    No way to select any unit shorter than DAY...


    I include a screen copy were you can see original file (Classeur source) and the modified one (Classeur modifie).
    As you can see on the modified one, the events on the horizontal axis are regularly displayed (and they should not as the values haven't same intervals)

    I include also the modified file in case of. I am about to think that it's a bug... but I'm not an Excel specialist

    Could you help me please!


    Thanks a lot!
    PAul
    Attached Images Attached Images
    Last edited by Mister Paul; 01-08-2016 at 08:05 PM.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: No way to change the timescale to minutes !

    In order to get a "time" axis, you have to use an XY scatter plot. Convert chart to an XY scatter chart, then format the X axis with the desired "time" number format.
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: No way to change the timescale to minutes !

    Hi, welcome to the forum

    Please upload your file to the forum. Not all members are able - or willing - to access file-hosting sites

    Also, if that is set up for dates, what you need to understand about dates and times in excel is...

    a date is just a number representing the number of days passed since 1/1/900...and then formated in a way that we recognise as a date. So, for instance, today (Fri 08 Jan 2016) is actually 42377

    Time is actually a decimal part of 1 (day), so 06:00 AM is 0.25, 12 noon is 0.5 and 18:00 (or 6 PM) is 0.75

    So your template is probably looking at whole numbers, but you are trying to use decimals for the chart
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    01-08-2016
    Location
    Brussels
    MS-Off Ver
    Office 2016 Mac
    Posts
    17

    Re: No way to change the timescale to minutes !

    Quote Originally Posted by MrShorty View Post
    then format the X axis with the desired "time" number format.
    The problem is that even if I modify the number format to mm:ss;@ the results are wrong: the values are not well positioned on the horizontal axis

    As FDibbins asked me, I upload the file directly here.
    (sorry for my late reply)
    Attached Files Attached Files
    Last edited by Mister Paul; 01-08-2016 at 08:30 PM.

  5. #5
    Registered User
    Join Date
    01-08-2016
    Location
    Brussels
    MS-Off Ver
    Office 2016 Mac
    Posts
    17

    Re: No way to change the timescale to minutes !

    Quote Originally Posted by FDibbins View Post
    So your template is probably looking at whole numbers, but you are trying to use decimals for the chart
    Yes it seems to look for Dates with Day as unit. But I can't find how I could make it work in Minutes ...
    Last edited by Mister Paul; 01-08-2016 at 08:55 PM.

  6. #6
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: No way to change the timescale to minutes !

    The problem is that even if I modify the number format to mm:ss;@ the results are wrong: the values are not well positioned on the horizontal axis
    In order for time to show up correctly on an axis, the axis must be a value axis. I don't think that this combination of line + column chart is really going to show what I think you want to show.

    Here's how I think I would do this.

    1) Delete the 2nd data series. and its data labels.
    2) Change chart type to XY scatter.
    3) Format X axis as desired
    4) Add a 2nd XY scatter data series. Date column as the X values and Position as the Y values. Format as marker without lines.
    5) Add data labels to this 2nd data series.
    6) Add error bars to series 1, custom values, positive values in Position column.

    See if that gets you closer to what you want. Remember that, in order for the times to space themselves correctly along the axis, the horizontal axis must be a value axis, so you must use an XY scatter plot for anything that needs to be linked to the times.

  7. #7
    Registered User
    Join Date
    01-08-2016
    Location
    Brussels
    MS-Off Ver
    Office 2016 Mac
    Posts
    17

    Re: No way to change the timescale to minutes !

    But the original file (Classeur source.xlsx) works like a charm!
    In fact I try only to work with mm:ss instead of j mmm

    But as soon as I change format, or what else, it breaks out the positions of the green bullets. And they get displayed at regular intervals despite of their value (I think that's the "normal" behavior when there is an incoherence in calculation).

    For me, Excel newbie, that's tough guys!
    ;-)
    Attached Files Attached Files

  8. #8
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: No way to change the timescale to minutes !

    Now I am confused. In your first file (post #4), your "date" column consisted of time values spanning a total of about 20 minutes. In your new file, your date column consists of date values that span about 5 months. Your new date values are "integers", meaning the time information in those dates is "midnight of the given date". (For a newbie, I would suggest something like this: http://www.cpearson.com/Excel/datetime.htm#SerialDates to understand how Excel stores date/time information).

    For a project that spans 5 months, what does it mean to "only work with minutes and seconds"? Are you really trying to measure this in terms of tens of thousands of minutes rather than calendar days?

    If you add a column that computes elapsed time (=B19-$B$19 note the relative and absolute references), and use that for your horizontal axis data, then you can format as elapsed minutes:seconds ("[mm]:ss" this page explains how to create custom number formats https://support.office.com/en-us/art...rs=en-US&ad=US ). For a project that spans 5 months, resolution of 1 day should still be adequate, so a line/column chart with a date axis should still work, and you will end up with an axis that spans from 0 to 200,000+ minutes. Is that really what you are looking for?

  9. #9
    Registered User
    Join Date
    01-08-2016
    Location
    Brussels
    MS-Off Ver
    Office 2016 Mac
    Posts
    17

    Re: No way to change the timescale to minutes !

    Thank you for your patience!

    There is 2 files:
    1° In post #7, the file is almost the original from Microsoft. It's working with dates that spans 5 months. Consider it as the template. It works fine.
    2° In post #5, the file is my attempt to customize this file for an event that spans less than 1 hour. And I can't make it work...

  10. #10
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: No way to change the timescale to minutes !

    Now I understand that part of it.

    Did you try following the steps I outlined in post #6? If you did, which steps did you have trouble with?

  11. #11
    Registered User
    Join Date
    01-08-2016
    Location
    Brussels
    MS-Off Ver
    Office 2016 Mac
    Posts
    17

    Re: No way to change the timescale to minutes !

    I must apologize… I didn't understand most of these points unfortunately. It's far too advanced for me…
    I only tried directly with the Excel interface but I can't go under 1 Day (Jours = Days)
    see screenshot
    Attached Images Attached Images

  12. #12
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: No way to change the timescale to minutes !

    So, you got stuck on the first step -- delete the 2nd data series. The highlighted part of your screenshot is the axis formatting pane, which has nothing to do with adding or removing a data series. To remove a data series, first find the "select data" dialog (my French is rusty, but I think that will be "Selectionner des donnees"). From this dialog, you should be able to select the 2nd data series, click "remove" and that data series should be removed from the chart. As an alternate procedure, select the data series and press delete. Repeat for the data labels.

    2) To change the chart type, select the chart and click on "modifier le type de graphique".
    3) Then bring back up the format axis pane for the x axis to format the axis the way you want.
    4) To add the 2nd data series, click on "selectionner des donnees" again, and add the 2nd data series.
    5) ADd data labels to the 2nd data series: https://support.office.com/en-us/art...rs=en-US&ad=US
    6) Then add the error bars.

  13. #13
    Registered User
    Join Date
    01-08-2016
    Location
    Brussels
    MS-Off Ver
    Office 2016 Mac
    Posts
    17

    Re: No way to change the timescale to minutes !

    Thank you.
    I tried and removed what I though to be 2nd data series and the data labels. The whole chart vanished.
    Anyway, I'm afraid that's far too complicated for me.
    There are also imbricated series (see screenshot)

    I'm about to give up
    Attached Images Attached Images
    Last edited by Mister Paul; 01-09-2016 at 12:28 PM.

  14. #14
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: No way to change the timescale to minutes !

    I tried and removed what I though to be 2nd data series and the data labels. The whole chart vanished.
    Unless you accidentally deleted the entire chart, the chart is probably there, but difficult to see against the black background. Assuming the chart is still there, select the chart and change the chart type to XY scatter and add the 2nd data series and see if the chart reappears.

  15. #15
    Registered User
    Join Date
    01-08-2016
    Location
    Brussels
    MS-Off Ver
    Office 2016 Mac
    Posts
    17

    Re: No way to change the timescale to minutes !

    Vanished…


    But let's try another approach: why not directly use the original file and change the time unit? That's all we need as all the rest is OK in the template.

    The only problem is that I can't go under 1 day, as said in #11
    I saw also this template in vertex42. In the video I see that it's possible to lower the unit easily (see screenshot)
    Why not in my template?
    Attached Images Attached Images

  16. #16
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: No way to change the timescale to minutes !

    The only problem is that I can't go under 1 day, as said in #11
    I saw also this template in vertex42. In the video I see that it's possible to lower the unit easily (see screenshot)
    Why not in my template?
    As I have explained from the beginning, to get time less than a day on the x axis, you must use an XY scatter chart. Microsoft's example that you first started with is based on a line chart. Vertex42's example, as they explain, is based on an XY scatter chart, not a line chart. (If it is really as easy to use as they suggest, it might be worth $20 to download their template.)

    If you want to start from scratch using MS's template that you already have:
    1) Unhide column F (select columns E:G->right click->unhide)
    2) Select your Date, Position, and Reference columns.
    3) Insert -> chart -> XY scatter -> I think you will want markers without lines.

    Just that much should be real close to what you want.

  17. #17
    Registered User
    Join Date
    01-08-2016
    Location
    Brussels
    MS-Off Ver
    Office 2016 Mac
    Posts
    17

    Re: No way to change the timescale to minutes !

    Quote Originally Posted by MrShorty View Post
    As I have explained from the beginning, to get time less than a day on the x axis, you must use an XY scatter chart.
    Oops I missed that point…

    XY scatter is "Nuage de points (XY)"?
    Attached Images Attached Images

  18. #18
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: No way to change the timescale to minutes !

    Yes, that would be correct "nuage to points"

  19. #19
    Registered User
    Join Date
    03-30-2014
    Location
    Pittsburgh,PA
    MS-Off Ver
    Office 2010, Home Student 2013
    Posts
    62

    Re: No way to change the timescale to minutes !

    It took a bit of experimentation, and I had a few interruptions, but I think I've got the graph Mister Paul wants (the graph with minutes and seconds). I hope it helps. The link:


    The original is a combination line and column chart. The Chart Area and the plot area are also set to transparent: this makes chart item selection somewhat difficult.

    The chart I created is an xy or scatter chart. I used three series: one for items on the x-axis, one for those below the x-axis, and a third series for those points above the axis. The vertical lines are error bars. The two series above and below the axis are formatted to have no markers and no lines.

    I was not able to duplicate the method for the original chart's data labels. I had to create the labels I used in a new block of cells.

  20. #20
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: No way to change the timescale to minutes !

    Thisoldman (hi from anotheroldman) Thanks for the input, would you mind uploading your file to the forum please? Not all members are able - or willing - to access file-hosting sites

  21. #21
    Registered User
    Join Date
    03-30-2014
    Location
    Pittsburgh,PA
    MS-Off Ver
    Office 2010, Home Student 2013
    Posts
    62

    Re: No way to change the timescale to minutes !

    Sorry about not attaching the file. Different forums have different capabilities.
    Attached Files Attached Files

  22. #22
    Registered User
    Join Date
    01-08-2016
    Location
    Brussels
    MS-Off Ver
    Office 2016 Mac
    Posts
    17

    Re: No way to change the timescale to minutes !

    Thank you Thisoldman!
    It works great!

    I have been able to aggregate the two series above and below the axis. In fact, as far as I saw, there was only an esthetic problem with the vertical lines, resolved by filling the label (there was no filling) and positioning the text vertically in the middle.
    Thanks again.


    I have 2 more questions for you guys!


    I'd need (if possible...):
    - to modify the color of the green bullets on the axis depending of the textual content of a cell (I'd have 3 or 4 different kind of events)
    - to position the red flag image automatically depending of the final value of the table (= same position than the last bullet)

    Is that possible ?

  23. #23
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: No way to change the timescale to minutes !

    I find that a technique like this (http://peltiertech.com/conditional-f...-excel-charts/ ) is the easiest way to get "conditional formatting" for chart data points. In essence, you are turning each "color" into its own data series.

    To position the red flag, you extend the same technique to include the flag as the marker for the final point (added as its own data series). You can see an example of this here (http://peltiertech.com/Excel/Charts/FormatMinMax.html ). Follow through to "Use an Arrow to Indicate Special Points" to see how to add something like that flag as the data marker.

  24. #24
    Registered User
    Join Date
    01-08-2016
    Location
    Brussels
    MS-Off Ver
    Office 2016 Mac
    Posts
    17

    Re: No way to change the timescale to minutes !

    MrShorty thank you!

  25. #25
    Registered User
    Join Date
    01-08-2016
    Location
    Brussels
    MS-Off Ver
    Office 2016 Mac
    Posts
    17

    Re: No way to change the timescale to minutes !

    Well I have been too optimistic: when I tried today to apply conditional formatting to the chart, I couldn't manage it...


    I'd like the green bullets on the horizontal axis to be colorized depending of a value in column C (Type).

    I created the column with a drop down lists to define that value for each line.
    Depending of the value entered, the time is automatically displayed into another column. OK

    But I couldn't go further from there and couldn't manage the conditional formatting of the horizontal axis bullets.

    I enclosed the new file if you could be nice again and help me...
    Attached Files Attached Files

  26. #26
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: No way to change the timescale to minutes !

    It might help us help you if you showed or explained what you had tried.

    One problem you have is that you used empty string ("") to denote your "blanks" in columns D:G. These will be your X axis values, and these text values will cause problems in your chart (See the section called "simulating empty cells" here: http://peltiertech.com/mind-the-gap-...g-empty-cells/ ). For charting purposes, a better choice would be to return the NA() function. In D28: =IF($C28=D$27,$B28,NA()) Also instructive, note how I used an absolute reference to column C and row 27 to simplify copying this formula across D28:G40. With the change to N/A, you should be able to simply add 4 data series to the chart (back to the "selectionner des donnees" dialog). The Y values for each of these series will be the "reference" column (K28:K40) and the X values will be D28:D40, E28:E40, F28:F40, and G28:G40. With the N/A marking the "blank" cells instead of a text string, Excel should correctly plot the milestone markers.

  27. #27
    Registered User
    Join Date
    01-08-2016
    Location
    Brussels
    MS-Off Ver
    Office 2016 Mac
    Posts
    17

    Re: No way to change the timescale to minutes !

    MrShorty thanks again!

    :applauses:

  28. #28
    Registered User
    Join Date
    01-08-2016
    Location
    Brussels
    MS-Off Ver
    Office 2016 Mac
    Posts
    17

    Re: No way to change the timescale to minutes !

    Well I have a small problem with negative time in label:

    I'd need to get negative time before starting horizontal axis at 00:00
    I entered events in the graphic at -4:00 and -2:30

    I had to modify date starting time to 1904 in preferences and it's OK (=I have negative events well positioned on the horizontal axis)
    The problem is that negatives time values aren't displayed. If they are positive they appear. If I modify the number format I cal see -0,002441239
    So it's not a label problem, but a problem of negative time value in labels. I have mm:ss;@ as format. Should I modify something?

    Thanks for your help!

  29. #29
    Registered User
    Join Date
    03-30-2014
    Location
    Pittsburgh,PA
    MS-Off Ver
    Office 2010, Home Student 2013
    Posts
    62

    Re: No way to change the timescale to minutes !

    The horizontal axis, the x-axis has been set with fixed minimum and maximum bounds. Change this to automatic to allow negative times.

    capture_2016-01-20_0255.png

  30. #30
    Registered User
    Join Date
    01-08-2016
    Location
    Brussels
    MS-Off Ver
    Office 2016 Mac
    Posts
    17

    Re: No way to change the timescale to minutes !

    Yes I did it.
    But the problem isn't that the event isn't positioned on the horizontal axis (it is) but there's no label filled because it's negative for this format mm:ss;@ (see screenshot)

    I have also inclosed the xls file

    NB:
    with Standard as number format I get : -0,00625
    Attached Images Attached Images
    Attached Files Attached Files

  31. #31
    Registered User
    Join Date
    03-30-2014
    Location
    Pittsburgh,PA
    MS-Off Ver
    Office 2010, Home Student 2013
    Posts
    62

    Re: No way to change the timescale to minutes !

    This is the change, from X Value to Value From Cells. Then select your cells from column B
    format_data_labels.PNG

  32. #32
    Registered User
    Join Date
    01-08-2016
    Location
    Brussels
    MS-Off Ver
    Office 2016 Mac
    Posts
    17

    Re: No way to change the timescale to minutes !

    Thank you thisoldman
    But unfortunately I haven't this option on my Excel version (Office 2016 for Mac)
    Attached Images Attached Images

  33. #33
    Registered User
    Join Date
    03-30-2014
    Location
    Pittsburgh,PA
    MS-Off Ver
    Office 2010, Home Student 2013
    Posts
    62

    Re: No way to change the timescale to minutes !

    Please see next post, this one becameslighttly garbled.

  34. #34
    Registered User
    Join Date
    03-30-2014
    Location
    Pittsburgh,PA
    MS-Off Ver
    Office 2010, Home Student 2013
    Posts
    62

    Re: No way to change the timescale to minutes !

    I hope this works for you – try the format mm:ss;-mm:ss;mm:ss;@

    Custom number formats are in the form:
    [positive_values];[negative_values];[zero];[text]

    Four fields separated by semicolons.

  35. #35
    Registered User
    Join Date
    01-08-2016
    Location
    Brussels
    MS-Off Ver
    Office 2016 Mac
    Posts
    17

    Re: No way to change the timescale to minutes !

    Unfortunately not... In all labels with positive values, it doubles the value and adds a negative sign. But it doesn't create any label below 00:00
    Attached Images Attached Images

  36. #36
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: No way to change the timescale to minutes !

    I cannot find the old thread, but I seem to recall bumping up against this issue before. As I recall, something happened in the "upgrade" (to paraphrase Eeyore "If it was an upgrade, which I doubt") between 2003 and 2007 to the chart engine's ability to handle negative times, even with the 1904 date/time system selected. In this case, if I open the file in 2007, save as in '97 to 2003 file format type, and open in 2002, the negative times show up just fine on the chart without further modification.

    Possible solutions:
    1) "Downgrade" everyone who needs to see this file to Excel 2003 or older. (not likely feasible).

    2) Abandon Excel's built in time formats, and perform your calculations in decimal minutes. Format your axis and cells to a "fixed" or "general" number format. Enter 1440 (1440 minutes in a day) in a cell. Copy the 1440, select your "time" cells, paste special, multiply. This should convert all of those "fractions of a day" numbers to decimal minutes. IMO, this is going to be the easiest solution for the programmer. Since you seem inexperienced with Excel, I would recommend this solution.

    3) If you decide that you must have the sexagesimal (hh:mm:ss) notation, this can be added to (2). You would need to keep the copy of the decimal minutes cells (perhaps hidden), and add text formulas [a =TEXT() or =CONCATENATE() function or combination] that will convert the decimal minute to a text string. Then using "data label" tricks to add those text strings to the axis labels. It can be done, but it will be additional work beyond (2).

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Change to minutes
    By mdwnn7 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 06-26-2015, 11:17 AM
  2. [SOLVED] change minutes to hours and minutes
    By Steved in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 07-05-2006, 10:00 PM
  3. [SOLVED] External data retrieve timescale
    By Richard Edwards in forum Excel General
    Replies: 1
    Last Post: 04-27-2006, 08:50 AM
  4. [SOLVED] External data retrieve timescale
    By Richard Edwards in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-27-2006, 08:50 AM
  5. [SOLVED] Timescale in Charts
    By Sally in forum Excel General
    Replies: 2
    Last Post: 02-13-2006, 11:25 PM
  6. [SOLVED] One dimensional timescale
    By [email protected] in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 01-20-2006, 10:25 AM
  7. [SOLVED] how to change a decimal number (minutes) into hours and minutes?
    By Erwin in forum Excel General
    Replies: 2
    Last Post: 11-05-2005, 12:25 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1