calling excel experts again...

[tops]

dont think this needs an expert.. but still dont know how to do this.
wanna get the difference between two different time fields in terms of hours and mins. for example the difference between 10pm and 2 pm should be 4 hours. how do i implement this?
sorry if this is too simple and its something thats obvious.. i know theres something in the time funcion that does this but i couldnt figure it out.
thanks..

 

[Mitzi]

Easy one... just format the columns which contain the times to time (highlight the columns and go to Format->Cells and choose time). Then in another cell (i.e. C1) just use the SUM function.

Example:

If you had the time 13:55:21 in A1 and 12:21:21 in B1 and you wanted to subtract the second time from the first, in C1 enter the formula =SUM(A1-B1).

 

[Bglad]

No, I don't think you can do that Mitzi. Formatting the cells will display a date but you can't do any calculations with it.

You have to make the cell a function by hitting = and then choosing time from the function menu or entering it manually. For example 1:35:00am would be "=TIME(1,35,0)". Then you can subtract one from the other.

 

[Mitzi]

No, I don't think you can do that Mitzi. Formatting the cells will display a date but you can't do any calculations with it.

Just tried it and it works fine using the method I described (using Excel 2002)

 

[Bglad]

Hmmm, I tried it in Excel 2000 and get an error.

 

[tops]

i've got excel 2000 too and got an error. tried SUM(B1-A1) and that worked fine as long as the time in B2 was after the time in A1. So can't use this to calculate if the time period I wan tto calculate spans 2 days or more. Would I have to have a date field as well to incorporate this? and would i then have to split up the period 22:00 hrs - 2:00hrs as 22:00-23:59 and then 00:00 to 2:00?

 

[Bglad]

Hmmm, good question.

All of the time/date functions seem to separate date and time meaning you can measure either the number of days or time but the maximum measure of the time would be 24 hours.

I think you're gonna have to format two columns, one for date and one for time. Then have two answer columns, one subtracting the start date from the today date and one subtracting time to give exact time. Not a very easy way to do it.

You can however format your answer column for the days to show hours. Then add that to the hours/minutes answer column.

Anyone else have any suggestions?

 

[HotWire]

I believe you may have to break in into a few more columns with perhaps A# to B# handling a 24 hour time period then setting up D# to E# as a continuance of the total but now incorporating the second 24 hour period. Then the totals of C# and F# would give you the 48 hour sum. ..........................my 2 minutes worth