Answered by:

Calculating date time difference

Question

Hi, I have a list with 2 columns (Start Time, End Time) that contains
date & time information and a third column (Duration) that is
calculated field. I wanted to calculate the difference between the
first 2 columns and show it in the 3rd field. It's working fine, but
in some cases, the result is incorrect. Below are the details:

I wanted to show the Duration column values like "2 hours 45 minutes".
If it's 0 minutes, then just show "2 hours". The complete formula I used is:

In the above formula the part that calculates the hour difference is:
INT(([End Time]-[Start
Time])*24)

If the start time is at 9:00am and the end time is at 1:00pm, the
result is 3 hours although it should be 4 hours. If the start time is
10:00am and end time is 1:00pm, the result is 3 hours, which is right.
But if the start time is 11:00am and end time is 1:00pm, the result is
1 hour, but it should be 2 hours. Also, if the start time is 12:00pm
and the end time is 1:00pm, the result is 0 hours. Interestingly, if the start time is 9:00am and the end time is 1:05pm, the result is 4 hours.

Below is a table where I've put all my sample data and you can see that
some results are correct, but where I have end time is 1:00pm,
sometimes it's incorrect. Just compare the result of the last 4 rows
with. The last row's Duration is empty because the calculation result
is 0 hour.

Can you shed any light on this? Is it a bug in sharepoint or is it the formula that I am using? Is there any simpler formula than the one I'm using but will give me the same result?

Thanks for your reply. I think your TEXT formula will just show in numbers, for example "1:30". Can the TEXT formula be modified to show it as "1 hour 30 minutes". Does it need to be combined with some other formula?

I was just trying out the TEXT forumal you showed above. It's working great, but for some reason the result is "0" if the hour difference is 24 or 48 or 72 hours. I didn't add anything to it. Simply used it as you had. Am I missing something?

But it seems to work only if the expected duration is less than 24 hours. In my case, The column data is like this

Start Time: 4/25/2009 7:00 AMEnd Time: 4/26/2009 7:00 AM

Calculated Column: 0:00 (it should be 24 hours)

If I change the End Time to 6:00 AM then the calculated column will show 23:00, which is correct. And if I change the End Time to 12:00PM, the result is 5:00 instead of 29:00.

So, the text formula doesn't seem to be working as expected if the duration is over 24 hours. And the hour gets reset again if the duration is over 48 hours and 72 hours and so on... So, if the duration is supposed to be 49 hours, the above formula will show 1 hour and ofcourse for 48 hours, it will show 0 hours, just like the duration of 24 hours or 72 hours. Any thoughts?

I had the calculated column as "single line of text" format, but I changed it to "Date and Time" format, but it shows the exact same result.

Microsoft is conducting an online survey to understand your opinion of the Technet Web site. If you choose to participate, the online survey will be presented to you when you leave the Technet Web site.