formula help

[Guest]

Here's what I'm working with:

appt time |time in | variance from appt. time | time out |variance from
appt. time

I am trying to show the variance from appointment time and actual time in,
and the variance from the appointment time and the time out. (clear as mud?)
I'd like to show it in positive or negative time. In other words, show a
distinction between time in that was EARLIER from the appt. time and LATER
than the appt. time.

Can anyone help?

 

[Biff]

Hi!

There is no "easy" way to handle negative times in Excel. You could use a
setting, 1904 Date system, that will allow for "normal" handling of negative
times but this comes with a tradeoff. This can affect dates in the file by
causing them to be off by 4 years.

You could do something like this but any negative times will be TEXT values
and not true Excel times:

Format the cell as h:mm

For variance from apt time (time in minus appt time):

=IF(B2<A2,"-"&TEXT(ABS(B2-A2),"h:mm"),B2-A2)

For variance from apt time (time out minus appt time):

=IF(D2<A2,"-"&TEXT(ABS(D2-A2),"h:mm"),D2-A2)

If you wanted to get grand totals it could still be done but would be a
little more complicated due to any TEXT values.

Biff