Forum Discussion

Ganesh2195's avatar
Ganesh2195
Copper Contributor
Jul 05, 2023

HI experts, please help to calculate difference between the time columns in share point list

i have 2 sharepoint list columns "Time of breakdown" = "5/7/2023 10:00:00 PM" and "Rectification work time" = "6/7/2023 09:00:00 AM" , i want to calculate difference between those two and want a result like   x days y hours z mins. i used below formula:

 

=DATEDIF([Rectification work completed Time],[Time of breakdown],"d")&" days "&HOUR([Rectification work completed Time]-[Time of breakdown])&" hrs "&MINUTE([Rectification work completed Time]-[Time of breakdown])&" mins "&SECOND([Rectification work completed Time]-[Time of breakdown])&" secs"

 

its giving me Num error, because i think its not considering AM/PM, please suggest me the way to solve this.

Microsoft Lists
SharePoint Online
  • LeonPavesic's avatar
    LeonPavesic
    Silver Contributor
    Jul 05, 2023

    Hi Ganesh2195,

    you can try to use the following formula:

    =INT([Rectification work completed Time] - [Time of breakdown]) & " days " & HOUR([Rectification work completed Time] - [Time of breakdown]) & " hours " & MINUTE([Rectification work completed Time] - [Time of breakdown]) & " minutes"

    Make sure you apply this formula in a calculated column in your SharePoint list:

    • INT([Rectification work completed Time] - [Time of breakdown]) calculates the difference in days.
    • HOUR([Rectification work completed Time] - [Time of breakdown]) calculates the difference in hours.
    • MINUTE([Rectification work completed Time] - [Time of breakdown]) calculates the difference in minutes.

    SharePoint calculated columns do not support seconds, so the seconds part is excluded from the formula.

    Ensure that both "Rectification work completed Time" and "Time of breakdown" columns are set as Date/Time type columns in your SharePoint list.


    If my anwer helped you thow resolve your issue, you can click on Mark as best response.

    Kindest regards,

    Leon

Resources