Home 
Search 
Today's Posts 
#1




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




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




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 semicolon 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 
Display Modes  


Similar Threads  
Thread  Forum  
Problem with adding "+1" to one cell while another cell decreased by "1"?  New Users to Excel  
conditional formatting "if part of cell contents contains string"  Excel Worksheet Functions  
How do I reference a cell as PART of a vlookup "Table_Array" locat  Excel Worksheet Functions  
Array as a "named range"  formula ok in cells, but error as "named range"  Excel Discussion (Misc queries)  
Using the "$" as part of a cell reference  Excel Worksheet Functions 