Link to home
Start Free TrialLog in
Avatar of c45
c45

asked on

Calculating time in excel

Hi. This is silly question

Cell c2 = start time (eg 07:30:00)
Cell h2 = finish time (eg 19:05:01).
H2 is 24 hours later.

So in I2 I want the difference to calculate the total time taken in this case 35:35:01

I tried (h2-c2) + time(24,0,0). And no joy ;(

Thanks
Avatar of E C
E C
Flag of United States of America image

Format Cell C2 as "Date 3/14/01 1:30 PM"
Format Cell H2 as the same ("Date 3/14/01 1:30 PM")

In Cell I2 enter the formula "=H2-C2"
Then, format I2 as "Time 37:30:55"

This will give you the number of hours, minutes, and seconds elapsed
   35:35:01
You can use this UDF (or use the code in VBA.

Function NextDayTime() As String
Dim Hours As Long
Dim minutes As Long
Dim seconds As Long

Hours = (DateDiff("n", Range("c2").Value, Range("h2").Value)) / 60 + 24
minutes = (DateDiff("n", Range("c2").Value, Range("h2").Value)) Mod 60
seconds = (DateDiff("s", Range("c2").Value, Range("h2").Value))
seconds = seconds / 3600
NextDayTime = Hours & ":" & minutes & ":" & seconds
End Function

Open in new window


So if you wanted the result in J2 you would do the following in J2

=NextDayTime()

and copy down if you want to do it to a column.
Hi,

It is always a good partice to keep both Date and Time in the Start Time and End Time.  This way you can do a direct minus between the given to Date and Time.

So if you update both time and date then, what if you want to see only the Time in the Date and time Column.  This can be solved by simpliy formating the column as Time format.  This way even if you enter both Date and Time, only time is visible.

Attached is some sample date and time and their calculations.
data.xlsx
ASKER CERTIFIED SOLUTION
Avatar of felixdsouza
felixdsouza
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Or simply use a custom format on the output of:

[hh]:mm

This will return the total hours and keep them as hours rather than converting to days which weren't showing in the result because of the formatting.

Thanks
Rob H
The formula

(h2-c2) + time(24,0,0)

which you used is correct, although long. What you need to do is change the custom number format for the cell to

[h]:mm:ss
Adding the additional 24 hours will make the result wrong by 24 hours.

You only need the formula:

=h2-c2

and formatted as:

[hh]:mm

Thanks
Rob H
Rob, the question provides 2 "time values" which do not have dates. You have to add the 1day or the 24 hours to get the desired result.
Apologies to ssaqibh, I had thought that the question had given two dates and times but, having re-read the question and some of the posts, looks I got the idea of cells including dates from one of the subsequent posts.

To clarify for benefit of c45:

If cells have date and time, formula is only:

=H2-C2

If cells only have time but you know they are 1 day apart (how???), the formula should be

=H2-C2+1

Both formatted depending on how you want the single hour results to show; with preceding zero would be [hh]:mm or as a single digit would be [h]:mm

If the 1 day part needs to be calculated from date only fields, let us know where they are and we can do something with that as well.

Thanks
Rob H
Exactly Rob.

That is precisely why I had provided the formula:

=(H2-C2)+1
Note that TIME function always returns a value < 1 so, in fact, =TIME(24,0,0) = 0

regards, barry
Note that TIME function always returns a value < 1 so, in fact, =TIME(24,0,0) = 0


Huh?

=TIME(24,0,0)  

returns 12:00 AM
Unless I am missing something, I think everyone here is over-complicating this issue.

In my original post, I mentioned that as long as the cells are properly formatted, you can simply use subtraction.

This will give you the number of hours, minutes, and seconds elapsed no matter how many 24 hour periods have gone by:

Format Cell C2 as "Date 3/14/01 1:30 PM"
Format Cell H2 as the same ("Date 3/14/01 1:30 PM")

In Cell I2 enter the formula "=H2-C2"
Then, format I2 as "Time 37:30:55"

This will give you the number of hours, minutes, and seconds elapsed
   35:35:01
This will give you the number of hours, minutes, and seconds elapsed no matter how many 24 hour periods have gone by
That assumes that the day is in the raw data and I don't see anything from the Author one way or the other.
>Huh?

Martin, yes, if you put =TIME(24,0,0) in a cell and format as a time value you get 12:00 AM - but that's just zero formatted as a time (try formatting the cell as general)

My point is that the original question quoted this formula

=(h2-c2) + time(24,0,0)

....but the time part is superfluous as it always returns zero so you would get the same result with

=h2-c2

regards, barry
Well it's really not superfluous given that the Asker was trying to add 24 hours to the result; it just didn't work. If he wanted to add anything that would make the total anything less that 24 would work, e.g. if h2 were 7:00 and c2 were 2:00  =(h2-c2) + time(5,0,0) would give 10:00 as the result.
@ecarbone

I am with you on this, it should be a simple case of one less the other; but as the others have mentioned, that assumes that the source cells include date and not just time.

However, to be frank, the formatting (style as opposed to the content) of the source cells will not affect the result. I assume the time formatting that you are suggesting is new to 2010, I have always had to use a custom format of [hh]:mm

Hopefully, c45 will come back online this morning and clarify the content of the cells whether they are "date and time" or "time" or, to throw in a curve ball, they might even be text!!

Thanks
Rob H
Barry, I had tested it on 2003 and time(24,0,0) returns 1 and not zero provided the formatting is done accordingly.

On later versions, yes it returns zero.

Saqib
Hello Saqib,

I don't have access to Excel 2003 right now but I'm surprised - Excel help 2003 says

"The decimal number returned by TIME is a value ranging from 0 (zero) to 0.99999999, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.)."

so normally if the "Time" entered equals or exceeds one day it will just show the remainder value once whole days are removed.....

MartinLiss

Yes, I understood that the asker was trying to add 24 hours, I think other solutions have been proposed that would do that (by adding 1) - I was trying to explain why the original approach doesn't work - of course you are correct in that TIME function can be used to add time values < 1 day

regards, barry
Avatar of c45
c45

ASKER

thanks for all the input guys,  The Problem was that the spreadsheet provided with all the start times to me didn't have the start date.. Which Sucked..    I have told the organizers that they need to get me the start date on the spreadsheet as well.<br /><br />thanks for all the input again..