Calculate Time

[LadyDi]

I have a spreadsheet that has a column for effective date (column R), effective time (column S), complete date (column T), and complete time (column U). I need to be able to calculate the time difference between these fields, and then I need to be able to run analysis on the results. The formula I am using is =(T4-R4)+(U4-S4) and then the column is formatted using a custom format - [h]:mm:ss. Everything looks right here, but when I try to run sumif or averageif formulas and use the same format, my numbers don't seem to line up (the results are way too high). I even did a simple calculation to find out how many hours the report covers (24*86) -- the report spans 86 days. Before I apply the format, the number of hours show 2064. After I apply the format, my number suddenly changes to 49536:00:00. Why is it doing that? How can I get my formulas on both page to return accurate results? I need to be able to calculate the average difference between the two dates and times based on various criteria. Any assistance you can provide would be greatly appreciated.

 

[Mihail]

Upload the spreadsheet with some data for tests. Use .xls format.
Give name to columns.
In the calculated columns write, using TEXT format, the formulas using the columns names.

Example:

[B]EfectiveTime     CompleteTime                   CalculatedTime[/B]
  08:12             09:13              CompleteTime - EfectiveTime (minutes)

 

[namliam]

Before I apply the format, the number of hours show 2064. After I apply the format, my number suddenly changes to 49536:00:00. Why is it doing that?

Your understanding of date/time seems to be "wanting"
Date and time are actually a double, a "normal" number with many decimal places. Each whole number is a day since roughly (depending on your settings) 12/31/1899. Your 2064 that is showing therefor is the number of days calculated, which if multiplied by the 24 hours in the day becomes 49536... which is what you are asking it to do.... the results are accurate.

My best guess without access to the actual sheet is that your times are actually like 14,5 for 14:30 or you are manipulating them in such a way that it is working with decimal time instead of actual time....

I therefor agree with Mihail, if you want or need more detailed help you will have to upload a spread sheet with some of your actual information and formula's in there so we can actually see what is going wrong.