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

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

You can use this UDF (or use the code in VBA.

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.

```
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
```

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

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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

[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

(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

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

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

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

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

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

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 byThat 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

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

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

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

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

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

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..

"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