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.

Improve this question
2
  • Are you only interested in time difference. The above formula will not take days into account.
    – Nadeem Yousuf-AIS
    Commented Aug 16, 2013 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
    Commented Aug 16, 2013 at 5:38

2 Answers 2

Reset to default
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

Improve this answer
6
  • The datedif doesn't give accurate results apparently, it doesn't consider time.
    – Batman
    Commented Aug 19, 2013 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
    Commented Aug 19, 2013 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
    Commented Aug 19, 2013 at 12:25
  • Ah OK - so try passing in just the date element to the datediff?
    – Dave Paylor
    Commented Aug 19, 2013 at 20:12
  • This worked for me: =INT(Modified-Created)&" day(s) "&TEXT(Modified-Created,"h:mm:ss")
    – Batman
    Commented Aug 19, 2013 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.

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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