0

I'd like to created a calculated column which subtracts the modified date/time from the creation date/time, is this possible?

EDIT: This worked for me - =TEXT(B2-A2,"h:mm:ss") replace the cell references with your actual columns.

  • Are you only interested in time difference. The above formula will not take days into account. – Nadeem Yousuf Aug 16 '13 at 5:14
  • Yea I just realized that after midnight the time started over at 0. I'd like to get the total time, if it does into days i'd like to know that as well. Ex. 1 day 3 hours 45 minutes XX seconds – Batman Aug 16 '13 at 5:38
1
=DATEDIF(Created,Modified,"d")&" days "&TEXT(Modified-Created,"hh:mm:ss")

This seems to be a slightly easier way to achieve it. You could obviously tweak the second part to break out the hours, minutes and seconds if required.

The functions available in calculated columns are available here http://msdn.microsoft.com/en-us/library/bb862071%28v=office.14%29.aspx

  • The datedif doesn't give accurate results apparently, it doesn't consider time. – Batman Aug 19 '13 at 5:40
  • Yep - datediff will only give the difference in number of days - but the second part gives you the time component? – Dave Paylor Aug 19 '13 at 8:33
  • From what I understand it doesn't take into consideration time in calculating days. So if I have day 18-15 it will give me 3 days when the answer could actually be 2 days and 17 hours for example – Batman Aug 19 '13 at 12:25
  • Ah OK - so try passing in just the date element to the datediff? – Dave Paylor Aug 19 '13 at 20:12
  • This worked for me: =INT(Modified-Created)&" day(s) "&TEXT(Modified-Created,"h:mm:ss") – Batman Aug 19 '13 at 20:26
0

One example over here shows Date Difference in days – Hours – Min format : e.g 4days 5hours 10min :

=YEAR(Today)-YEAR(Created)-IF(OR(MONTH(Today)<MONTH(Created),AND(MONTH(Today)=MONTH(Created),
DAY(Today)<DAY(Created))),1,0)&” years, “&MONTH(Today)-MONTH(Created)+IF(AND(MONTH(Today)
< =MONTH(Created),DAY(Today)<DAY(Created)),11,IF(AND(MONTH(Today)<MONTH(Created),DAY(Today)
> =DAY(Created)),12,IF(AND(MONTH(Today)>MONTH(Created),DAY(Today)<DAY(Created)),-1)))&” months,
“&Today-DATE(YEAR(Today),MONTH(Today)-IF(DAY(Today)<DAY(Created),1,0),DAY(Created))&” days”

This can help you accomplish your task.

  • I put this code as is and got a syntax error, am I suppose to change anything? – Batman Aug 16 '13 at 13:32
  • The Today function doesn't work in SP unfortunately – Batman Aug 16 '13 at 14:12
  • This was just an example to get you going. – Nadeem Yousuf Aug 16 '13 at 16:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.