PDA

View Full Version : Solved: subtract time



debauch
06-14-2006, 09:07 AM
23:25

0:01

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

mdmackillop
06-14-2006, 09:28 AM
Enter 24:01 instead of 0:01

Cyberdude
06-14-2006, 12:24 PM
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
06-14-2006, 01:25 PM
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
06-14-2006, 03:28 PM
Things are much clearer with samples. As you already have the Range Names, try
=IF(Logout>Login,Logout-Login,(Logout+24)-Login)

debauch
06-14-2006, 03:43 PM
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
06-14-2006, 03:44 PM
Like my new signature?

debauch
06-14-2006, 04:02 PM
Perfect.

debauch
06-14-2006, 04:38 PM
Actually ... Quick question ... where do you find the named ranges? I cannot find theM? Does it just grab the top row reference?

mdmackillop
06-15-2006, 12:05 AM
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!!!