Solved: subtract time

[debauch]

23:25

0:01

is there any way to subtract 0:01 (12:01 am) from 23:25 and get :36?

[mdmackillop]

Enter 24:01 instead of 0:01

[Cyberdude]

Hi, Debauch!

I?m not clear about what you are trying to accomplish, but let me emphasize that when doing arithmetic with time on two different days, then you must account for the date change too. Also, when subtracting times, then often you need to use brackets in the cell format to enclose the hours value. Instead of using ?hh:mm:ss?, use ?[hh]:mm:ss?.

Look at this example:

In cell A1: 6/15/06 23:25
In cell A2: 6/16/06 00:01
Format cell A3 as [hh]:mm
then in A3 put the formula = A2 ? A1
The result in A3 should be 0:36:00

HTH
SId

[debauch]

Thanks both of you for the reply.

I tried the [ ] formatting on the dates and could not seem to get it working.
For the most part, the subtraction works fine, until I cross over midnight. I have attached a sample, and you can see the errors, where I am having trouble.

I have also highlighted the times that are causing the issue. I tried some simple if statements, but they seem to keep reversing what I want it to do.

(p.s - a few columns look the same, its just different variationsa of what I was trying to do).

[mdmackillop]

Things are much clearer with samples. As you already have the Range Names, try
=IF(Logout>Login,Logout-Login,(Logout+24)-Login)

[debauch]

Genious ... I think I was on the right track ... and you nailed it right on the money. Thanks. Solved.

P.S - sorry for no attachment the first go 'round.

[mdmackillop]

Like my new signature?

[debauch]

Perfect.

[debauch]

Actually ... Quick question ... where do you find the named ranges? I cannot find theM? Does it just grab the top row reference?

[mdmackillop]

You're absolutely right! I was misreading when I highlighted columns D and D. There must be an "automatic" naming system which I never new about.
Serendipity!!!