#### How enter time DURATIONS mm:ss (not clock times), then sum?

```I want to enter a column of data as time DURATION (not clock time) in mm:ss
format and then sum the total. What cell format should I select for the
duration entries (mm:ss does not work) and what calculation steps do I need
to use to sum those durations?
```
 0
Utf
4/8/2010 6:09:02 PM
excel.worksheet.functions 4936 articles. 2 followers.

3 Replies
2538 Views

Similar Articles

[PageSpeed] 7

```Hi Roger

Try this.

The format should be [mm]:ss

Entry must be as 0:00:00

Use sum function to calculate total.
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.

"Roger D" wrote:

> I want to enter a column of data as time DURATION (not clock time) in mm:ss
> format and then sum the total. What cell format should I select for the
> duration entries (mm:ss does not work) and what calculation steps do I need
> to use to sum those durations?
```
 0
Utf
4/8/2010 6:50:01 PM
``` Your total can be hh:mm:ss if you want.
--
Russell Dawson
Excel Student

Please hit "Yes" if this post was helpful.

"Russell Dawson" wrote:

> Hi Roger
>
> Try this.
>
> The format should be [mm]:ss
>
> Entry must be as 0:00:00
>
> Use sum function to calculate total.
> --
> Russell Dawson
> Excel Student
>
> Please hit "Yes" if this post was helpful.
>
>
> "Roger D" wrote:
>
> > I want to enter a column of data as time DURATION (not clock time) in mm:ss
> > format and then sum the total. What cell format should I select for the
> > duration entries (mm:ss does not work) and what calculation steps do I need
> > to use to sum those durations?
```
 0
Utf
4/8/2010 6:59:02 PM
```On Thu, 8 Apr 2010 11:09:02 -0700, Roger D <Roger D@discussions.microsoft.com>
wrote:

>I want to enter a column of data as time DURATION (not clock time) in mm:ss
>format and then sum the total. What cell format should I select for the
>duration entries (mm:ss does not work) and what calculation steps do I need
>to use to sum those durations?

A common misconception, with regard to dates and times, is that the cell format
has something to do with how Excel parses the input.

It does not.

If you input 55:33, Excel will interpret that as 55 hrs 33 minutes no matter
what format you have set.  (Well, if you have set text, it will be interpreted
as a text string, but certain function will convert it to hrs:minutes.

If you want to input 55 min 33 sec, you'll need to enter 0:55:33.  In other
words, enter your value in h:m:s or h:mm:ss format.

Some alternatives:
If you are always going to enter your data as m:ss, in an adjacent column
enter the formula
=cell_ref/60
This will convert your entry to m:ss, but won't work if you are also entering
fractions of a second.

Use a VBA macro to do the same thing.

If you are entering fractions of a second, ALL THE TIME, it might work.  But
you'll have to remember to add the trailing zero.

In other words:

ENTER
55:33 	--> 55 hours, 33 minutes
55:33.0 --> 55 minutes, 33 seconds

--ron
```
 0
Ron
4/8/2010 11:24:37 PM

Similar Artilces:

Chart of Times
I am working with a simple data set comprised of two columns and 24 rows. In column ("A"), I have the numbers 1-24. In column B, I have these random times: 4:57 5:16 2:17 1:20 1:11 1:21 1:19 1:11 1:37 2:56 3:29 4:09 5:59 5:23 5:22 3:59 4:04 1:51 2:06 2:16 2:08 1:28 1:24 1:24 The chart I would like to make would have the numbers 1-24 on the X- axis and the times listed above on the Y-axis. Ideally, this would be a column chart. My Issue: The times listed in column ("B") are formatted as time. When the chart is created the X-axis an...

Why is Actual Work updated when entering time into Work Field?
We're using MS Project 2007 and seeing that sometimes when time is entered into the Work field for a task, that task's Actual Work is updated to the same value entered in the Work field. This happens to multiple resources, but only on some of their tasks and not on others. For example, for the weeks of 1/24 and 1/31, whatever hours I schedule (enter in the "work" cell in the Resource Usage View) for a given task also appears in the "actual" cell. The tasks are setup up differently. Some are Fixed Work, some are Fixed Duration. I believe all have the e...

run time error; can not write to object
in 07 have form w/ onclick event of main form that opens a new form then having text in an unbound control of main form written into a text box on this new form run time error; can not write to object seems be triggered because the new form is opening with no records. the new form is in Date Entry property yes and when I manually open it - it opens to a new blank record....but when opening via the onclick docmd...it opens with no record... am perplexed... GBA wrote: > in 07 have form w/ onclick event of main form that opens a new form > > then having text in ...

Excel 2003 spreadsheet. I enter the any number and it is entered.
If I enter a number into a cell it is put in like a decimal. For example, if I enter 10 it is put in as .1 500 is entered as a 5 Thank you Mary Mary, Goto Tools>Options and on the Edit tab you should see The Fixed Decimal Places box checked. Uncheck it. -- HTH RP "maryu" <maryu@discussions.microsoft.com> wrote in message news:65C0E0FB-43F3-4F34-8A86-FBA3F02EC9B1@microsoft.com... > If I enter a number into a cell it is put in like a decimal. For example, if > I enter 10 it is put in as .1 500 is entered as a 5 > Thank you > Mary ...

Open a Form at a Specific Time

Emails Receiving time in Inbox Differs
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Email Client: pop Hi, <br><br>I wonder if anyone can help me with this. I realised that my incoming mails received in the in the Inbox, the time received is different from actual time received. Meaning, the timing in the inbox shows the time that I download the emails. IS there a way to resolve this? ...

Service time billing
Can MSCRM be adapted to a service organization that primarily bills for time on an as-provided basis? When resolving a case, I see that the billable time is shown as the total of all activity time. Is this information only used when applied against a contract, or can I invoice for time or export a list of billable time with descriptions for use in Quickbooks? Also, can information, such as usernames and passwords be stored in the account record but secured from users that don't have sufficient privileges? Thank you, Rick S. The actual time is only automatically decremented from th...

Filter records by date in subform by date entered in mainform.
Hello, I've spent 3 days trying to understand what I'm doing wrong.....Here is the summary of my problem. I have a form named 'frm_EfficiencyStats' and a subform within the form named 'qry_Total hours subform' (the subform is based on a query). The form contains a text box (for date) which is bound to a table 'tbl_EfficiencyStats'. I would like to be able to enter a date in the text box on my form, and display the records in the subform based on the date I entered in the text box. The subform will only pull records from my table based on the date. Here are th...

how to enter simple list and sum
using excel 2002 with an xp sp2 system how would I enter a simple list of positive and negative numbers and then create a cell with the sum of these values? if the list would look like this 0.58 -1.22 1.79 -0.56 -0.92 the output would look something like 0.58 0.58 -1.22 -0.64 1.79 1.15 -0.56 0.59 -0.92 -0.33 John In B1 enter =A1 In B2 enter =B1+A2 Copy down to B5 Gord Dibben MS Excel MVP On Wed, 16 May 2007 06:34:13 +0300, "John" <johnj@multipl.es> wrote: >using excel 2002 with an xp sp2 system >how would I enter a simple list of positive and negative...

Excel Worksheet Change Event in a nick of time
1. Enter an Excel worksheet ; To Define Name > "TagNumber" > ReferTo := D2. 2. Intention ; After TagNumber is updated (in a Change Event), the focus would move to the adjacent cell to the right, Offset(0,1). 3. And therefore, the event procedure is devised as follows ;- Private Sub Worksheet_Change(ByVal Target As Range) If ActiveCell.Address = Range("TagNumber").Address Then Range("TagNumber").Offset(0, 1).Activate End If End Sub 4. However, at run-time ; As soon as Tagnumber is activated, the focus shifts to the adjacent cell to the right alrea...

calculating times
HEY, DOES ANYONE KNOW HOW TO SET A FORMULA TO CALCULATE THE MINUTES FROM TWO TIME? THANKS, SPIKE Hi not really sure what you're looking for? maybe the function MINUTE is working for you. e.g. =MINUTE(A1) -- Regards Frank Kabel Frankfurt, Germany spike wrote: > HEY, > DOES ANYONE KNOW HOW TO SET A FORMULA TO CALCULATE THE > MINUTES FROM TWO TIME? > THANKS, > SPIKE Hi Spike! Posting in all upper case makes it more difficult to read especially if your first language isn't the one used. To calculate the minutes between times in A1 (earlier time) and B1 (later ti...

Find folder within a folder which occurs multiple times in that folder
Hello! i have to find a folder named "Addon" in some folder named "Installers" and store the path of "Addon" folder in a array. but "Addon" folder may occur multiple times in "Installers" e.g. Installers\New Folder\Addon Installers\New Folder1\Addon Installers\New Folder2\Addon I have to store all these path in a array how can i do that? >Hello! i have to find a folder named "Addon" in some folder named >"Installers" and store the path of "Addon" folder in a arra...

Printing Multiple Receipts at a Time From Journal
Hey guys, Is there a way to print multiple journaled receipts at a time? Say all receipts for a batch or by date or other criteria? Thanks Tom, Not that I've seen. -- = Get Secure! - www.microsoft.com/security You must be using Outlook Express or some other type of newsgroup reader to see and download the file attachment. If you are not using a reader, follow the link below to setup Outlook Express. Click on "Open with newsreader" under the MS Retail Management System on the right. http://tinyurl.com/75bgz ========== "Tom Bombadill" <Genius_pos...

Browse file at run time in Integration manager
How can I set up the integration so that the system prompts the user for the file at run time. I do not want the users to edit the integration to point to the right file every single time. Is there a way out? Stacey, You would need to write VBScript code in the Before Integration area. The script would use the FSO (File System Object) to prompt the user for the existing file. You would then need to copy the file to the location your source query expects the file to be. It would also need to be the same name as specified in the source query. Be aware that using this method, you can on...

Resource Units for Tasks entered in PWA differs than tasks entered in Project 2007
Hello, I have an ePM user asking why if he enters a project task in PWA does it not check the resource max units allocation the same way as when he enters a task in Project Professional. For example: You have a project where resource A is 50% allocated. When you create a task in Project Professional for this resource, the resource allocation is set to 50%. Create a task under the same project in PWA, submit and approve the task. Open project professional and view the task information for the task entered in PWA, why is the resource allocation set to 100% instead of 50% set for th...

Enter formula by code.
Hi All can anyone help. I cant seem to get this to work. I keep getting run time error 1004 application or object defined error. Public Sub TLIdentify() finalrow = Worksheets("INV Bookings").Range("B65536").End(xlUp).Row Worksheets("INV Bookings").Range("AV26:AV" & finalrow).FormulaR1C1 = "=IF(OR(RC[-38]>0,RC[-32]>0,RC[-26]>0,RC[-20]>0,RC[-14]>0,RC[-8]>0,RC[-2]>0),VLOOKUP(RC4,'Team Summary'!R4C3:R16C4,2,0),"")" End Sub -- Regards and Thanks for any assistance. Francis Brown...

Make two or three times your cost on thousands of products
Make two or three times your cost on thousands of products. Gift items,Toys Tools,Jewlery and more.Free info. No obligation http://g2967h.sprinkleofgifts.com/income.htm ...

Time and Date increment
Hi I have a date and time field (which I have formatted as dd/mm/yy hh.mm) 16/07/04 09:30 I would like to add (incrementally) an hour on to the time each day, so the first few days will be: 17/07/04 10:30 18/07/04 12:30 19/07/04 15:30 I have tried right-clicking and using the "series" function, but when I go into the "series" option I am not sure what to do. I have chosen the options step value = 1 and "growth" but it only updates the Day (dd) by one day. I can't get it to update the Hour (hh) by one hour each day. Any ideas anyone? Hi You need to ad...

Update two workbooks at the same time, but not all the time.
Hi Folks, I have set up 2 workbooks. One is for RMA's and the other is an evaluation form for returned equipment. When I fill out the RMA form certain feilds are filled out on the eval form at the same time, however I only need to do this when equipment is being returned for evaluation. We get part returned for various reasons, but not for eval. So I need to be able to turn these functions on and off when necessary so I don't create an eval form for a non-eval part. Can anyone help??? -- John.. ...

Sum of all combinations
I have 7 categories, each category has 5 possible scores. I want to calculate the sum of each possible combination. Is there a way to do this in Excel? My grid looks like this: JobKnowl 15 12 9 6 3 CallAcc 15 12 9 6 3 CallQual 15 12 9 6 3 JobAcc 5 4 3 2 1 IPSkill 10 8 6 4 2 Attend 20 16 12 8 4 Punct 20 16 12 8 4 "cursednomore" wrote: > I have 7 categories, each category has 5 possible scores. > I want to calculate the sum of each possible combination. > Is there a way to do this in Excel? I would write a VBA subroutine. But .... > My grid looks like this: > JobK...

Entering a dollar amount w/out having to enter the decimal
What is the formula to have Excel automatically enter the decimal so all I have to enter is the numbers? It's one of the options you can specify. In Excel 2007, it's: Excel Options...>Advanced>Automatically insert a decimal point Regards, Fred "Julieanne" <Julieanne@discussions.microsoft.com> wrote in message news:3AABE398-3B77-4ACD-8938-05ED7F5CC58E@microsoft.com... > What is the formula to have Excel automatically enter the decimal so all I > have to enter is the numbers? ...

Open to Same Cell Each Time
Hi Is there anyway of getting a workbook to open up to the same cell or worksheet each time, regardless of where it was saved when it was last closed? Thanks Jennie ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ Jennie, In the workbook open event add code to activate a worksheet and cell. As an example Private Sub Workbook_Open() Worksheets("Sheet1").Activate Range("A1").Select End Sub This code should be put in the ThisWorkbook code...

How do I stop dates from being entered everytime I enter a number
Everytime I enter data in a box (ex: ages 5-9) I then press enter to go to the next cell and it automatically puts in may 9 . I have tried putting in general in formatting cells and it still does it. can someone help before I shoot this pc out the window? I am making a population pyramid with males and females and their ages. Thanks, Kicking and Screaming You can precede your entry with a single quote... '5-9 ....or format your cells as 'Text'. Hope this helps! In article <64AE5219-035D-46E3-9488-5D01F6B012FA@microsoft.com>, Kicking and Screaming <Kicking and...

entering time #2
hello all.. wondering if there is any way to do this... I have a column formatted for time as in this format 12:00 when entering a time it is cumbersome to have to enter the hour, then a colon, then the minutes. Is it possilbe to simply enter a 4 digit number such as 1245 and have it show up as 12:45 ?? thanks so much... I've found this group to be irreplaceable... glen in orlando Enter your times without colons in column A and put this formula in column B =(A1-RIGHT(A1,2))/2400+RIGHT(A1,2)/(24*60) and format column B for "Time" in the form you want it. Tod "Glen i...

How much time has passed?
Using Excel 2003 I am using the following formula to calculate how much time has passed since a particular event. =DATEDIF(D2,NOW(),"y") This returns the passage of time in full years. I am stumped trying to get the result in years and months such as "3 years 6 months." Can anyone help? Check out http://support.microsoft.com/kb/290190/en-us , particular event being a birthday. John "BK" <nospam@nospam.com> schreef in bericht news:%231vwgIXRIHA.5208@TK2MSFTNGP04.phx.gbl... > Using Excel 2003 > > I am using the following formula to calc...