Expressing time values

  • Thread starter Thread starter Tstephens
  • Start date Start date
T

Tstephens

I'm running into a problem tryin to express time values properly.

What I want to achieve seems simple: I have various time values and I
want to add them and express the result in minutes and seconds. For
example, in A1, I have 00:00:46, in B1 I have 00:00:20, in A2 I have
:00:00:14, in B2 I have 00:00:19, etc. I want to add the value in A1
to each value in column B (separately), then add A2 to each value in
Column B, and repeat for each value I have in column A. I know how to
do everything except get Excel to treat my times as absolute time
values instead of time of day.

When I add 00:00:46 to 00:00:20, I get 1:06, but when I add 00:00:46 to
00:00:19, I get 01:39. I'm sure it's somehow because of the time
formatting, but I've toyed with it and cannot get a correctly displayed
answer.

Keying in 00:00:46 is giving me 12:00:46 AM, which has to be the start
of my problem...

What do I need to do here?
 
Here are two alternatives:

Option 1:
Select from the Menu:
Format>Cells...>select Category:Custom then Type:[t]:mm:ss

Option 2:
Type in C1:
=IF(A1+B1>1/24,TEXT(A1+B1,"t:mm:ss"),TEXT(A1+B1,"m:ss"))

Hope it helps
Ola Sandtröm
 
T,

There is only a date-time type. For time only, the date part is omitted
(the value is less than 1), and will be shown as Jan 1, 1900 if formatted
for date. If you see a date in the cell, you simply need to change the
formatting (Format - Cells - Number) to a time-only format.

If you clear the date-time formatting,

0:0:46 should show as 0.000532407407407407
0:0:19 should show as 0.000219907407407407
The total, 0:1:05 should show as 0.000752315

Those are the actual values of those times. Excel does all dates and times
in units of days. 1.25 would be the second day, Jan 1, 1900, 6:00 A.M.
(0.25 days). Chip Pearson has some good stuff on dates and times in Excel.
www.cpearson.com.
 
Back
Top