Calc Time in Excel

A

Ahleah1031

I have columns & worksheets that contain "Call" times.
Which is the length of time reps are on the phones. In
attempting to obtain a daily variance on their increase
or decrease of time on the phone, I continue to receive
pound signs (#)whenever the first part of the formula is
greater than the second. Example being Column A (Monday)
has 11:34 as the value, and Column B(Tuesday) has 12:53.

How would I calc this to get a value of -1:19?
Thank you in advance for your assistance!
 
M

Myrna Larson

You have two options. One is to switch the worksheet to the 1904 date system.
It can display negative times, though I don't know what the range is. You do
that at Tools/Options/Calculation, check 1904 date system. If your cells
contain only the time, without a date, you'll be fine.

But if your date consists of a date with a time, you will find that the dates
have shifted forward by 4 years and 1 day. September 14, 2004 will become
September 15, 2008. To get them to display correctly with the 1904 date
system, you must subtract 1462 from each date. You can do that by typeing 1462
in a cell somewhere, Edit/Copy that cell, then select the cells containing
your dates+times and Edit/Paste Special, and select the Values and Subtract
options.

The other option is to use formulas in two other columns, say C and D, in
which the data is converted from Excel's internal time format (i.e. a fraction
of a 24 hour day) to number of hours, minutes or seconds (whatever resolution
you need). To convert to hours, the formula is =A1*24; to minutes =A1*1440; to
seconds, =A1*86400. Then do your calculations on these columns.

If you need to display a negative result, you won't be able to do that
conveniently. You'll have to modify formulas to check for a negative result,
reverse the arguments, and keep track of the sign. If you've converted
everything to minutes, you can display the result so it looks like a negative
with a formula like this:

=IF(A1>B1,TEXT((A1-B1)/1440,"mm:ss"),"-"&TEXT((B1-A1)/1440,"mm:ss"))

but this result is text, not a time, so you can't do any more calculations
with it.
 
Top