Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 20th 07, 01:00 PM posted to microsoft.public.excel.worksheet.functions
Joe Joe is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 476
Default Adding hh:mm times in a row as part of a "named" cell

Hello, I need to add up the times (in h:mm format) of a row of cells. The
sheet I have been given has its base cell (C7) that has been named as Mhours.
This is defined as adding the values in row seven cells from D to AA. The
values for these cells are copied from another spreadsheet where the hours
are displayed in the h:mm format.
My problem is that to calculate I have to drop the : and replace it with a .
instead. The sum of D7:AA7 is not a true h:mm value and, therefore, makes
the final value incorrect.
What formula should be in the definition of the named cell or is there an
alternative formula without naming the cell (C7)? I have tried the
'=TEXT(D7:AA7,"h:mm")' formula, but this does not work; an error seems to
indicate a conflict with the two :'s.
Thanks.

  #2   Report Post  
Old August 20th 07, 01:58 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 4,339
Default Adding hh:mm times in a row as part of a "named" cell

Try:

=TEXT(SUM(D7:AA7),"[h]:mm")

"Joe" wrote:

Hello, I need to add up the times (in h:mm format) of a row of cells. The
sheet I have been given has its base cell (C7) that has been named as Mhours.
This is defined as adding the values in row seven cells from D to AA. The
values for these cells are copied from another spreadsheet where the hours
are displayed in the h:mm format.
My problem is that to calculate I have to drop the : and replace it with a .
instead. The sum of D7:AA7 is not a true h:mm value and, therefore, makes
the final value incorrect.
What formula should be in the definition of the named cell or is there an
alternative formula without naming the cell (C7)? I have tried the
'=TEXT(D7:AA7,"h:mm")' formula, but this does not work; an error seems to
indicate a conflict with the two :'s.
Thanks.

  #3   Report Post  
Old August 20th 07, 02:15 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Feb 2007
Posts: 8,651
Default Adding hh:mm times in a row as part of a "named" cell

Why do you say that to calculate you have to drop the semi-colon and replace
it with a decimal point? If you have cells which are genuinely times in
Excel h:mm format, then you can calculate, and give the result in [h]:mm
format.
If your original cells are text, then you can convert them to Excel times by
a number of methods which you'll find outlined in the archive of this group.
In your numbers where in a column we would probablty recommend Data/ Text to
columns, but as you've got numbers in a row, one option is to use
=TIMEVALUE(A7) if your text in in A7. Another option is to take a blank
cell, copy it, select your text time cells, and Edit/ Paste Special/ Add,
then format as time.
--
David Biddulph

"Joe" wrote in message
...
Hello, I need to add up the times (in h:mm format) of a row of cells. The
sheet I have been given has its base cell (C7) that has been named as
Mhours.
This is defined as adding the values in row seven cells from D to AA. The
values for these cells are copied from another spreadsheet where the hours
are displayed in the h:mm format.
My problem is that to calculate I have to drop the : and replace it with a
.
instead. The sum of D7:AA7 is not a true h:mm value and, therefore, makes
the final value incorrect.
What formula should be in the definition of the named cell or is there an
alternative formula without naming the cell (C7)? I have tried the
'=TEXT(D7:AA7,"h:mm")' formula, but this does not work; an error seems to
indicate a conflict with the two :'s.
Thanks.





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem with adding "+1" to one cell while another cell decreased by "1"? Michael Slater New Users to Excel 12 July 28th 07 01:06 AM
conditional formatting "if part of cell contents contains string" tom ossieur Excel Worksheet Functions 1 March 13th 07 11:11 AM
How do I reference a cell as PART of a vlookup "Table_Array" locat -Rocket Excel Worksheet Functions 2 January 4th 07 03:49 PM
Array as a "named range" - formula ok in cells, but error as "named range" tskogstrom Excel Discussion (Misc queries) 11 December 28th 06 04:44 PM
Using the "$" as part of a cell reference awy32 Excel Worksheet Functions 3 June 30th 06 12:43 AM


All times are GMT +1. The time now is 12:34 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017