Calculate difference between times in excel

[Sardamil]

I'm trying to calculate the difference between two times in excel. Usually that's easy enough, but now I have to calculate the difference between the times of two different days.

Here's exactly what I have to do. Sombody starts working at 7 pm and finishes at 2:30 am. If I try it the regular way, excel will give me an error message. There must be an easy way, but I somehow can't find it.

Can anybody help me?

 

[AnotherHiggins]

In A1, type 11/29/04 19:00:00
In A2, type 11/30/04 02:30:00

In any other cell, type [COLOR=blue white]=A2-A1[/color]
Format the calculated cell to hh:mm

If you don't want to have to enter dates, you could use this: [COLOR=blue white]=24-(A1-A2)[/color]

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[Sardamil]

Thanks. I knew it had to be this easy.

 

[AnotherHiggins]

Glad to help.


[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[Sardamil]

Just one more question. How would I enter a fixed time in a formula. Suppose I would want to state in an if-statement that a time should be smaller than 6 am. How would I do that. Also easy enough, but I don't know how to do this in excel. The way access works doesn't sem to apply to excel.

 

[AnotherHiggins]

First a word on how Excel deals with dates/times.

Excel sees all dates as whole numbers. For instance, today is 38323 because it has been that many days since Jan 1 1900.

Excel sees times as a percentage of 24 hours. Noon is .5 because it is half a day. Likewise, 6 AM is 1/4 of a day, or 25% - so Excel sees it as .25. So today at noon will be 38323.5.

Having said that, there are a few ways to get around your problem. The problem, by the way, is that Excel isn't seeing "06:00:00" in the formula as a number.

[ul][li]1: With the knowledge of how Excel sees numbers, you can just use [COLOR=blue white]=if(A1<.25,"yes","no")[/color] to get the results you want.[/li]
[li]2: You can type [COLOR=blue white]06:00:00[/color] in a different cell (let's say B1) and reference it in your formula like this: [COLOR=blue white]=if(a1<b1,"yes","no")[/color][/li]
[li]3*: You can force the [COLOR=blue white]06:00:00[/color] in the formula to a number by adding 0 like this [COLOR=blue white]=if(a1<"06:00:00"+0,"yes","no")[/color][/li][/ul]
*The 3rd option is probably the best because you don't have to reference another cell and you don't have to convert your time to a percentage of the day (6AM works well, but 5 or 7 is a little uglier).

Good luck!


[tt]-John[/tt]
The Member Profiles are finally working again!
________________________
To get the best answers fast, please read faq181-2886

 

[Sardamil]

Thanks. I think I can manage now. There's one more presentation issue I'm dealing with, but I'll try to work it out myself first before I bother you with it.