adding decimal time

[rbobcat]

I have access database that keeps track of time spent performing a task.
I export the table to excel and attempt to add the “time_spent†(Decimal)
column as hours/minutes.
The problem is how I add this decimal column and it treat the answer as time?
Example;
1.20 would be 1 h 20 m
..45 would be 45 m
But when I add this it returns 1.65 instead of 2.05

 

[Harald Staff]

Hi

To Excel, Access, SQLserver, Windows, MacOS, .... 1.2 is 1.2 days, not 1.2
hours. Better than fight it, why not use the native time format 1:20 and
avoid all those problems ?
See http://www.cpearson.com/excel/datetime.aspx for theory on this.

HTH. Best wishes Harald

 

[Rick Rothstein]

Assuming your "times" are in Column A and run from Row 2 to no more than Row
1000 (if you have different limits, change the 2 ranges in the formula to
reflect them), then this array-entered** formula will return the value you
seek...

=--SUBSTITUTE(TEXT(SUM(INT(A2:A1000)+TEXT(100*MOD(A2:A1000,1),"00")/60)/24,"h:mm"),":",".")

**Commit this formula using Ctrl+Shift+Enter, not just Enter by itself

As written, the formula returns a numeric value (which really will have no
numerical significance in any calculation you might try to use it in as it
is NOT a real time value). If you want the formula to simply return a text
value, remove the 2 minus sign after the equal sign.