Time difference between start and end date and time

[AG134]

Hello everyone,

Question with how to successfully create a cell that is calculating the time difference between two cells that are using a format of month/day/year hh:mm:ss and identifying the difference solely in a hh:mm:ss format,

[TABLE="width: 500"]
<tbody>[TR]
[TD]Start of project[/TD]
[TD]End of project[/TD]
[TD]Time difference[/TD]
[/TR]
[TR]
[TD]month/day/year hours:minutes:seconds[/TD]
[TD]month/day/year hours:minutes:seconds[/TD]
[TD]hours:minutes:seconds[/TD]
[/TR]
</tbody>[/TABLE]

I've tried a simple cell of start - cell of end formula however that doesn't create anything of value and results in a cell of #############,

I believe it's primarily a formatting issue, but I'm not having much luck with this at the moment,

 

[NotoriousPopol]

If I set the format of the last cell as "Hour", for me it works!

 

[Norie]

Format the cell with the result as [h]:mm:ss.

PS Also, shouldn't it be end cell - start cell?

 

[AG134]

Format the cell with the result as [h]:mm:ss.

PS Also, shouldn't it be end cell - start cell?

Hello,

You're absolutely correct, I had a typo in there, your code also works well! But I have bumped into another problem,

From that time difference column I'm breaking it into 3 segments, completion time of less than 8 hours, 8 - 16 hours and 16+,

I have the following 3 codes; column J holds the completion time got by subtracting the end vs. the start

=IF($J5 < "8:00:00",1,0)

=IF($J5>="08:00:00",IF($J5<"16:00:00",1,0),0)

=IF($J5 >= "16:00:00",1,0)

I think the reason this code is not working is due to the change in formatting style of [h]:mm:ss

All entries are recognized as <8 hours though this is not the case

 

[Norie]

Or it could be because "8:00:00" and "16:00:00" aren't time values.

Try using either TIMESERIAL(8,0,0) and TIMESERIAL(16,0,0) or TIMEVALUE("08:00:00") and TIMEVALUE("16:00:00").

Altertanively you could put 08:00 and 16:00 in cells and reference those cells in the formula.