sharepoint list difference in hours between two dates

%3CLINGO-SUB%20id%3D%22lingo-sub-2624651%22%20slang%3D%22en-US%22%3Esharepoint%20list%20difference%20in%20hours%20between%20two%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2624651%22%20slang%3D%22en-US%22%3E%3CP%3Ei%20have%20a%20calculated%20column%20in%20a%20sharepoint%20list%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(OR(ISBLANK(%5BFinish%20Date%20and%20Time%5D)%2CISBLANK(%5BStart%20Date%20and%20Time%5D))%2C%22%22%2CTEXT(%5BFinish%20Date%20and%20Time%5D-%5BStart%20Date%20and%20Time%5D%2C%22hh%3Amm%22))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20works%20ok%20if%20columns%20%5B%20Start%20Date%20and%20Time%5D%20and%20%5BFinished%20Date%20and%20Time%5D%20are%20on%20the%20same%20day.%3C%2FP%3E%3CP%3EBut%20take%20for%20example%20the%20first%20duration%20in%20this%20image%20-%20the%20calculation%20ignores%20the%20fact%20that%20start%20and%20finish%20are%20on%20different%20days%20should%20be%2028%3A26.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22cheezy_0-1628422275002.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F301603iF2B0F2609FEEAC27%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22cheezy_0-1628422275002.png%22%20alt%3D%22cheezy_0-1628422275002.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3Eany%20ideas%3F%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2624651%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMicrosoft%20Lists%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

i have a calculated column in a sharepoint list:

 

=IF(OR(ISBLANK([Finish Date and Time]),ISBLANK([Start Date and Time])),"",TEXT([Finish Date and Time]-[Start Date and Time],"hh:mm"))

 

This works ok if columns [ Start Date and Time] and [Finished Date and Time] are on the same day.

But take for example the first duration in this image - the calculation ignores the fact that start and finish are on different days should be 28:26. 

 

cheezy_0-1628422275002.png

any ideas? 

1 Reply

@cheezyit looks like your problem is with the TEXT function - it doesn't take days into account. If you had used the decimal format, you would have got the correct results.
Regards :)