A Microsoft Excel forum. ExcelBanter

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

Hours and minutes



 
 
Thread Tools Display Modes
  #1  
Old February 23rd 09, 05:27 PM posted to microsoft.public.excel.worksheet.functions
Eán[_2_]
external usenet poster
 
Posts: 30
Default Hours and minutes

If I want to record time spent is there a function I can use to add time,
that is for example:

15mins + 30mins + 90mins = 2hrs 15mins or 135mins.

Please and thank you
Ads
  #2  
Old February 23rd 09, 05:33 PM posted to microsoft.public.excel.worksheet.functions
Glenn
external usenet poster
 
Posts: 1,240
Default Hours and minutes

Eán wrote:
> If I want to record time spent is there a function I can use to add time,
> that is for example:
>
> 15mins + 30mins + 90mins = 2hrs 15mins or 135mins.
>
> Please and thank you


Look at "Add times" in the help file.
  #3  
Old February 23rd 09, 05:41 PM posted to microsoft.public.excel.worksheet.functions
joel
external usenet poster
 
Posts: 9,101
Default Hours and minutes

It depends how your numbers are entered. Is 15mins a number or a text
string? I have a UDF that can add strings I can modify it to return a time.

"Eán" wrote:

> If I want to record time spent is there a function I can use to add time,
> that is for example:
>
> 15mins + 30mins + 90mins = 2hrs 15mins or 135mins.
>
> Please and thank you

  #4  
Old February 23rd 09, 05:44 PM posted to microsoft.public.excel.worksheet.functions
Eán[_2_]
external usenet poster
 
Posts: 30
Default Hours and minutes

currently the numbers are entered as numbers. i.e. 15+30+90 etc.

"Joel" wrote:

> It depends how your numbers are entered. Is 15mins a number or a text
> string? I have a UDF that can add strings I can modify it to return a time.
>
> "Eán" wrote:
>
> > If I want to record time spent is there a function I can use to add time,
> > that is for example:
> >
> > 15mins + 30mins + 90mins = 2hrs 15mins or 135mins.
> >
> > Please and thank you

  #5  
Old February 23rd 09, 05:45 PM posted to microsoft.public.excel.worksheet.functions
Fred Smith[_4_]
external usenet poster
 
Posts: 2,389
Default Hours and minutes

Which one do you want, time or minutes? In either case, it's just addition:
=a1+a2+a3

The key is how you enter it. If you want your total in minutes, just enter
numbers, like 15, 30 and 90.
If you want your total in hours and minutes, enter hours and minutes, as in
0:15, 0:30, 1:30.

If, for some reason, you want to enter minutes, but display your total as a
time, then divide by the number of minutes in a day, as in:
=(a1+a2+a3)/24/60

Remember to format your result as a time.

Regards,
Fred.

"Eán" > wrote in message
...
> If I want to record time spent is there a function I can use to add time,
> that is for example:
>
> 15mins + 30mins + 90mins = 2hrs 15mins or 135mins.
>
> Please and thank you


  #6  
Old February 23rd 09, 05:48 PM posted to microsoft.public.excel.worksheet.functions
Rick Rothstein
external usenet poster
 
Posts: 5,934
Default Hours and minutes

You can use the SUM function to get total minutes then. Let's say your
values are in A1 through A50, then use this...

=SUM(A1:A50)

--
Rick (MVP - Excel)


"Eán" > wrote in message
...
> currently the numbers are entered as numbers. i.e. 15+30+90 etc.
>
> "Joel" wrote:
>
>> It depends how your numbers are entered. Is 15mins a number or a text
>> string? I have a UDF that can add strings I can modify it to return a
>> time.
>>
>> "Eán" wrote:
>>
>> > If I want to record time spent is there a function I can use to add
>> > time,
>> > that is for example:
>> >
>> > 15mins + 30mins + 90mins = 2hrs 15mins or 135mins.
>> >
>> > Please and thank you


  #7  
Old February 23rd 09, 05:55 PM posted to microsoft.public.excel.worksheet.functions
Roger Govier[_3_]
external usenet poster
 
Posts: 2,480
Default Hours and minutes

Hi

Assuming you have your values entered in cells A1, A2, A3 etc.
With a summation formula in A11
=SUM(A1:A10)/1440
A11>Format>Cells>Number>Custom>[h]:mm

Excel stores times as fractions of a day, so there are 24 * 60 = 1440
minutes in a day.
Adding all your value and dividing by 1440 gives the result as a time 2:15

If you just wanted the result as decimal hours then it would be
=ROUND((SUM(A1:A10)/60),2)
which would give a result of 2.25
--
Regards
Roger Govier

"Eán" > wrote in message
...
> currently the numbers are entered as numbers. i.e. 15+30+90 etc.
>
> "Joel" wrote:
>
>> It depends how your numbers are entered. Is 15mins a number or a text
>> string? I have a UDF that can add strings I can modify it to return a
>> time.
>>
>> "Eán" wrote:
>>
>> > If I want to record time spent is there a function I can use to add
>> > time,
>> > that is for example:
>> >
>> > 15mins + 30mins + 90mins = 2hrs 15mins or 135mins.
>> >
>> > Please and thank you


  #8  
Old February 23rd 09, 05:59 PM posted to microsoft.public.excel.worksheet.functions
joel
external usenet poster
 
Posts: 9,101
Default Hours and minutes

If you want the results to display like 2 Hours and 15 minutes then you have
to enter the number as time not straight numbers. Format the input cells to
a time format. Enter minutes as 0:15 or hours and minutes to 2:15 (2 hours
and 15 minutes). Then sum the time and format the formula cell also to time.

"Eán" wrote:

> currently the numbers are entered as numbers. i.e. 15+30+90 etc.
>
> "Joel" wrote:
>
> > It depends how your numbers are entered. Is 15mins a number or a text
> > string? I have a UDF that can add strings I can modify it to return a time.
> >
> > "Eán" wrote:
> >
> > > If I want to record time spent is there a function I can use to add time,
> > > that is for example:
> > >
> > > 15mins + 30mins + 90mins = 2hrs 15mins or 135mins.
> > >
> > > Please and thank you

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Converting total minutes into hours and minutes in Excel colette Excel Worksheet Functions 11 December 26th 07 08:24 PM
converting Days Hours & minutes into just minutes in excel Six Sigma Blackbelt Excel Discussion (Misc queries) 5 April 28th 06 09:45 PM
how to change a decimal number (minutes) into hours and minutes? Erwin Excel Discussion (Misc queries) 2 November 5th 05 05:22 PM
add hours & minutes to other hours & minutes to receive total hou. Wes Excel Worksheet Functions 2 March 2nd 05 12:33 AM
add column of minutes, show total in hours & minutes glider pilot Excel Worksheet Functions 1 December 30th 04 11:27 PM


All times are GMT +1. The time now is 12:03 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
Copyright 2004-2014 ExcelBanter.
The comments are property of their posters.