Graphing Time

I have a datasheet with time values that are in minutes:seconds format and I need to graph them in some way. However, the only way i am finding to graph them is by manually rounding the number to just minutes. Manually rounding the numbers will not work in the long run because I need to have an automated process and no formula will work with my numbers.

here are some of my numbers in (minutes:seconds)
00001:40
00006:40
00013:20
00020:00
00005:00
00023:20
00008:20

That is the way they are given to me and I would like to use them in that format however I can't figured out how to have those numbers show up on the graph.
0
Lisa1 (290)
6/16/2004 2:44:21 PM
excel.charting 18370 articles. 0 followers. Follow

1 Replies
568 Views

Similar Articles

[PageSpeed] 41

Hi Lisa -

The problem is that Excel doesn't recognize your values as times. If you 
had values like 01:40, 06:40, etc. (without all the extra leading 
zeros), Excel would recognize them as hour:minute times, which also is 
not what you want.

Here's how to convert them. Assuming the values are in column A, 
starting in A1, put this formula in cell B1:

=TIME(0,LEFT(A2,5),RIGHT(A2,2))

The cells will say something like 12:01 AM. Format the cells with a 
custom number format. Select the cells, choose Cells from the Format 
menu, select the Number tab. Select Custom from the list on the left, 
and enter mm:ss in the box on the right. Now your cells look like this:

01:40
06:40
13:20
20:00
05:00
23:20
08:20

and Excel knows they are minutes and seconds. If you need all the extra 
zeros, use a custom number format like "000"mm:ss instead (with quotes 
around the zeros as shown).

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

lisa wrote:

> I have a datasheet with time values that are in minutes:seconds format and I need to graph them in some way. However, the only way i am finding to graph them is by manually rounding the number to just minutes. Manually rounding the numbers will not work in the long run because I need to have an automated process and no formula will work with my numbers.
> 
> here are some of my numbers in (minutes:seconds)
> 00001:40
> 00006:40
> 00013:20
> 00020:00
> 00005:00
> 00023:20
> 00008:20
>
> 
> That is the way they are given to me and I would like to use them in that format however I can't figured out how to have those numbers show up on the graph.

0
DOjonNOT (619)
6/17/2004 12:09:41 AM
Reply:

Similar Artilces:

x/y axis graph
i need to do an x/y axis graph, but my y axis must be alphabetical not numerical , how do i do that. please help ...

time calculations #6
Hello! Does anyone know how you can calculate worked hours in a day on the basis of a start and end time? =(end_time-start_time)*24 -- HTH Bob Phillips "Aline" <A_Mangelschots@hotmail.com> wrote in message news:1127724380.330877.175210@o13g2000cwo.googlegroups.com... > Hello! > > Does anyone know how you can calculate worked hours in a day on the > basis of a start and end time? > Hi Aline With start time in column A, and end time in column B, enter in C1 =MOD((B1-A1),1) will give the time interval, even allowing for time period where the start is on...

Timeline Graph
Hello, I am trying to make a timeline in excel but it's giving me some trouble. I concluded the best graph type to use was floating bars as they have a start position and a lengh. This is fine for dates of say: -100 to -50 and 100 to 150 but excel can't seem to handle going over the origin point, it won't draw -100 to 100. I'm just trying to do a basic rise and fall of different civilisations, can anyone help? Thanks. You could increase all the values by the lowest negative number, and create a chart from the adjusted numbers. For example, if -100 is the earliest year, inc...

Bar Graphs #5
Hi all, I have set-up a bar-graph consisting of four horizontal bars. I would like to overlay one particular bar onto another specific bar - leaving the other two as is. Is this possible? I have looked at the 'Overlap' option but unfortunately while this is on the right theme, it overlaps everything! Any help greatly appreciated! On Tue, 12 Jun 2007, in microsoft.public.excel.charting, Don <Don@discussions.microsoft.com> said: >I have set-up a bar-graph consisting of four horizontal bars. I would like >to overlay one particular bar onto another specific bar - leaving ...

Excel tutorials for first time Mac user
Operating System: Mac OS X 10.5 (Leopard) I'm fairly capable with M/S Office on a p.c. Just got my first Mac and feel like a total idiot. Can't find tutorial or other help info for real basic stuff--i.e. inserting a new line in an existing spreadsheet. Can anyone tell me where to go, in a nice way :)? Thanks much and sorry for asking such a stupid question. Bob Try MacTopia, the official website of the Macintosh BU. Excel Help and How-To: <http://tinyurl.com/6hap8w> Excel (and other apps) courses: <http://www.microsoft.com/mac/courses.mspx> On 08/07/08 5:09, in arti...

when is the time right to create a sub to do something?
hello i have a project where i have lots of fields that filter things on the forms. i have the typical apply filter button, but i have also been using a key catch for the enter key. when a user updates the filtering field and hits enter i cause it to requery the sub query that the filtering field creates a filter for. on some forms the amount of times i put in the code: If KeyAscii = vbKeyReturn Then Me.mySubform.Requery End If is ridiculous. so the question is should i make a routine for the form(s) that calls the little bit of code? it seems so repeatative to put th...

The ActiveX control "GridControl" ... requires a design-time license ???
This Active-X control is registered on all of my systems, not just the ones that have VC++6 installed, so I assume that it's part of the Windows run-time environment. Is this correct? Assuming that I would deem it useful, since all that happens if I try to instantiate it into a dialog is that I get the error message, how could I find out what sort of design-time license is required, who can issue these, etc...? TIA Norm Hi Norm, Thanks for your post. I'd like to share the following information with you: 1. By GridControl, do you mean the Grid OLE Control (grid32.ocx)? Based...

Create database based on 24 hour time; 15 minute increments
I need to set up a MS Access Database that stores test data that was performed at certain times (rounded to the nearest 15 minutes). I need to produce a query that shows each 15 minute interval within a 24 hour period. Any 15 minute increment that doesn't have test data needs to be represented by a -1. Any suggestions on how to do this? If you first create a table which has one row for every 15 minute interval over a suitable period you can then LEFT JOIN this to your table of test results to return a row for every time regardless of whether there is test data for that time....

How do I run outlook for the first time, to get the mail icon?
I want to set up multiple email accounts. Help menu indicates start - control panel - user accounts - mail, with a note that the mail icon will only appear after program is run first time. Basically, how do I get to see the mail icon? The mail icon should be in the control panel as soon as outlook is installed but you can go to the outlook shortcut on the Start menu, All programs, Microsoft Office to run outlook. -- Diane Poremsky [MVP - Outlook] Outlook Tips: http://www.outlook-tips.net/ Outlook & Exchange Solutions Center: http://www.slipstick.com/ Outlook Tips...

calculating Week numbers in a time table
I have a weekly timetable table for school terms with the following fields WeekID (automatic number), WkbeginDate (date), WkNo ( number 1 or 2). The WkNo can be either 1 or 2 . How can I get the Wkno field to automatically update with the correct week number for each new record ie if a record has week value 2 the next record will have a WkNo value of 1? I would prefer to do this using a query rather than a form. The timetable is not for the whole year and has 'holiday' breaks, sometimes restarting on a week 2, sometimes on a week 1. Hope this makes sense! Hilarys =?Utf-8?B?SGls...

help with line graph
i accidently and unknowingly tuned off the line on my line graph, when i try to do the line graph the line isn't there. please help!!!!!!!!! Can you see the markers on the chart? If so right click one, select Format Data Series; on the Pattern tab put check mark next to Line If you cannot see it; click on chart, tap up/down arrows watching Name box, when Series 1 show up, use menu item Format. -- Bernard Liengme www.stfx.ca/people/bliengme remove CAPS in e-mail address "darci" <anonymous@discussions.microsoft.com> wrote in message news:149a201c3f8f9$4cf70c70$a601280a@...

calculating time (for payroll)
I'm trying to use Excel like a "time clock" to check in/out of work. I want to be able to write the time in a certain cell that I came into work, left for lunch, came back from lunch, and left for the day. I then want to add up the hours for the week and any hours up to 45 I need to multiply by a dollar amount. Also, any hours above 45 I need to calculate for overtime. I can add the ours to get a total for the week but am having a hard time with multiplying "Time" by a dollar ammount. (Nevermind that I have to find the amount of hours overtime) Attached is a very s...

automatically insert system time in a cell
I have a s/sheet that needs to automatically have the system time inserted in a cell if the previous cell is blank. I have tried several things and have not had any joy. Any help offered would be welcome. You can use the select_change event on your sheet capture when a differenet cell has been selected. Keeping track of prior cell entries can be tracked with a phantom sheet to store prior values, if the prior value was null, then you can send the following command activecell.value = now http://ForPicks.com "PM" wrote: > I have a s/sheet that needs to automatically have...

task reminders, set default snooze time
in OL07, is there a registry setting (or simpler method), for changing the default time in snooze for task reminders? i've looked and searched, but find no reference to this. -- jeffrey "jeffrey" <astro46@gmail.com> wrote in message news:jcLCl.11709$jZ1.6064@flpi144.ffdc.sbc.com... > in OL07, is there a registry setting (or simpler method), for changing the > default time in snooze for task reminders? I don't believe that can be changed. -- Brian Tillman [MVP-Outlook] The default can't be changed. You don't need to select from the drop down...

Can't open Office programs on two computers at the same time
Version: 2004 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel I purchased Microsoft Office 2004 for mac several years ago and installed it on my MacBook. <br><br>Recently I purchased a new computer (a MacBook Pro) and transferred the software over, leaving a copy on my old computer for my wife to use. Both computers are connected to the same wireless network in our home. <br><br>Here's my problem: if one of us is using any of the Office suite programs (ex. Word) and the other tries to open that or any other program (ex. Excel, Power Point), no...

Averaging Time
I've tried to figure out a way to average a column of times, but to no avail. 3:03 :45 1:45 How would I average this column of times? Thanks On 27 Jan, 12:37, JP <John...@msn.com> wrote: > I've tried to figure out a way to average a column of times, but to no > avail. > > 3:03 > :45 > 1:45 > > How would I average this column of times? > > Thanks I've just tried it using Excel 2003 and the AVERAGE function seems to work just fine. You need to make sure that the :45 time is entered as 00:45 however or it won't work. Let me know how you g...

Date an time on WinXP.
Does anyone know where windows Xp gets the time and date it displays from? My system date and time often fails, resulting in the time beiing 5-5mn delayed compared to what it shoul have been . I wonder what can cause that???? ...

Formatting Times and converting to text
Hi, I have got a spreadsheet which has a column which holds a time calculated from another column. The times in both columns are in the format m:ss.000. The sheet when complete will be imported into Access which unfortunately xan't hold times with seconds with 3 decimal places so the recomended solution is to hold the time in a text field. So how in Excel can I convert the times into a text field without losing the currently displayed value, as whatever I try changes the value to the offset value (i.e. 7.88E-05 instead of 0:06.815). Thanks Graham Do it this way: =3DTEXT(A1,&q...

Same time, next year....
Hello, I am working on a massive (evil) Double-Declining Balance AMT spreadsheet...ugh! The first issue I would like to address is that I have a list of assets, all with different purchase dates in Column C. I would like column D to compute the date of 3/31/XX, where XX is the NEXT instance of the date 3/31. In other words, Date Entered Date Desired 8/17/05 3/31/06 2/15/87 3/31/87 6/24/94 3/31/95 I've worked with dates before and created a pretty intense timesheet, but I am at a loss as to where to begin for this particular concer...

date time problem
I would like to have a date cell that can have an input mask of __/__/__ so that a user need only enter the numerics of a given date; i.e 052405. I I tried setting the cell format to custom mm/dd/yy and I get a return date of 10/1/43 !!!!!???? Why has excel lost the correct date? Regions global show the correct year range, and Excel is set to 1900 time? What do I need to do to correct this? Thanks XL's parse engine and display engine are separate - the displayed format has nothing to do with how the entry is parsed (other than Text entries are not parsed at all). For a workaro...

Subtracting two times in hh:mm:ss format
I need to subtract two times that are in hh:mm:ss format and they occur on different dates. Example: Time 1: 03/22/2005 19:09:47 Time 2: 03/23/2005 01:02:51 Result = Time 2 - Time 1 I've tried different things but I keep getting #VALUE! in the result cell. Any help would be appreciated. Thanks. Make sure the cells are correctly formatted as date and time. -- HTH RP (remove nothere from the email address if mailing direct) "A Khan" <A Khan@discussions.microsoft.com> wrote in message news:C179333B-FEDF-4542-8107-2F9B6AA6241F@microsoft.com... > I need to subtract...

Remove time portion from date on Excel 2007 chart
I am trying to remove the auto generated time of 00:00 to every date in my chart. I have tried setting the format, but it just ignores whatever I try to input. Any help would be appreciated. Where does this appear? Which labels? - Jon ------- Jon Peltier Peltier Technical Services, Inc. http://peltiertech.com/ NeoFax wrote: > I am trying to remove the auto generated time of 00:00 to every date > in my chart. I have tried setting the format, but it just ignores > whatever I try to input. Any help would be appreciated. ...

E- Mails duplicate each time
My In Box recieves the same New Message Twice since I up-dated from "Express to Outlook 2003. There is surely a simple answer to solve this probleme. Thanks for helping in advance. Mark wrote: > My In Box recieves the same New Message Twice since I up-dated from > "Express to Outlook 2003. There is surely a simple answer to solve > this probleme. Thanks for helping in advance. Did you happen to set up two connections to the same mailbox? Check Tools - E-mail Accounts. -- f.h. ...

I think I messed up big time... v.Delete Administrative Groups
To make a long story short, we had an Exchange 2003 SP2 Clustered Server in an Administrative Group. We had a catastrophic failure of the hardware, and now we have an Administrative Group, that still thinks the server is there. This is the only server in the Administrative Group. As of right now, this entire Exchange 2003 environment is not in a production environment. If I select the server, the ESM will freeze for a second then just hop up to the Org name. Not allowing me to right click and delete. I am totally lost. I want to get delete this Administrative Group, but I have no idea h...

Pop3 COnnector time question
Is there any way to have the Pop3 Connector go and retrieve mail less than every 15 minutes? (maybe every 5 minutes?) Thank You! The great big kludge POP3 Connector is an SBS component. You should ask about it in an SBS newsgroup. -- Ed Crowley MVP - Exchange "Protecting the world from PSTs and brick backups!" "Keith C" <KeithC@discussions.microsoft.com> wrote in message news:A24BDE54-673A-4017-A74E-4673863BE654@microsoft.com... > Is there any way to have the Pop3 Connector go and retrieve mail less than > every 15 minutes? (maybe every 5 minutes?) Thank ...