Calculating date time difference
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: =CONCATENATE(IF(INT(([End Time]-[Start Time])*24)>1,INT(([End Time]-[Start Time])*24)&" hours",IF(INT(([End Time]-[Start Time])*24)=1,INT(([End Time]-[Start Time])*24)&" hour","")),IF(MINUTE([End Time]-[Start Time])>0,MINUTE([End Time]-[Start Time])&" minutes","")) 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? Start Time End Time Duration 1/24/2009 7:00 AM 1/24/2009 3:00 PM 8 hours 1/24/2009 8:00 AM 1/24/2009 6:00 PM 9 hours 1/25/2009 6:00 AM 1/25/2009 8:00 PM 14 hours 2/7/2009 8:00 AM 2/8/2009 5:35 PM 33 hours 35 minutes 2/8/2009 7:00 AM 2/9/2009 5:00 PM 34 hours 2/10/2009 8:00 AM 2/10/2009 8:30 AM 30 minutes 2/8/2009 9:00 AM 2/8/2009 1:00 PM 3 hours 2/11/2009 10:00 AM 2/11/2009 1:00 PM 3 hours 2/14/2009 11:00 AM 2/11/2009 1:00 PM 1 hour 2/16/2009 12:00 PM 2/16/2009 1:00 PM
February 8th, 2009 11:42am

Hi John, 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? Parvez
Free Windows Admin Tool Kit Click here and download it now
April 23rd, 2009 8:53pm

Hi John,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?Parvez
April 24th, 2009 10:45am

I just found the Microsoft website showing examples of the same formula to calculate the difference between two times. Below is the link:http://office.microsoft.com/en-us/sharepointtechnology/HA011609471033.aspx=TEXT([End Time]-[Start Time],"h:mm")But it seems to work only if the expected duration is less than 24 hours. In my case,The column data is like thisStart Time: 4/25/2009 7:00 AMEnd Time: 4/26/2009 7:00 AMCalculated 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 ifI change the EndTime 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 willshow 1 hour and ofcourse for 48 hours, itwill 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.Regards,Parvez
Free Windows Admin Tool Kit Click here and download it now
April 24th, 2009 5:12pm

Thanks, this was very helpful
August 30th, 2012 9:02am

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics