Search code examples
excelexcel-formulacustom-formatting

Difference between two dates and times from a SharePoint List

I am working with data from a SharePoint list in the format

"hh:mm dd/mm/yy"

in Excel. I would like to find the difference in nearest hours from a start and end time to make a box and whisker plot of total time expended for a given manufacturing process.

I tried the TimeValue function, which worked in another sheet where the start and end times for time periods that were over the same day. I have also had trouble with the hours and minutes since they are retuned in one or two digit values.

=INT(M2-L2)&"days"&TEXT(M2-L2,"h""hrs""m""mins"""),

and have used the left function to capture the days and multiply it by 24 to get the number of days. I am not very experienced with excel.

Solution

  • If these are truely date/time values as you said you could simply substract the older one from the newer one and format the outcome in two different ways to return the amount of hours as an integer:

    Your sample data:

    enter image description here

    1) You could use the TEXT function to format an outcome like so:

    =TEXT(A2-A1,"[H]")

    2) You could subtract the two values (=A2-A1) and format the cell with a custom format:

    Right click > Cell Properties > Number tab > Custom format > [H]