SharePoint Lists- Calculation Error - Converting Duration of Event to Days, Hours, Minutes

Question

Hi All,

Having some issues with a formula on Microsoft Lists.

Goal:

I have two separate columns both representing a start and end date of an injection period.

Example:

Injection Start

2/1/2023 08:00AM

Injection End

10/27/2023 05:30PM

In the third Column I attempting to Calculate Duration of Injection Period, I am easily able to represent in a decial if I take [Injection End]-[Injection Start] in this case it would be 268.40. I am attempting to convert this decimal to a separate column with the format "Days, Hours, Minutes.

[Duration of Injection] = [Injection End]-[Injection Start]

I am attempting to use the following formula =INT([Duration of Injection]*24)&" days "&TEXT(MOD([Duration of Injection]*24,1),"h\h mm\m")

I receive the following value once calculated 6441 days 12h and 00min which is incorrect it should read 268 days, 9 hours, 30 minutes. Although the first value is the correct number of hours (6441) just seems to be an issue converting to days and subsequently transferring to h and m units.

Any thoughts/comments would help.

Answer 1

Dear JSteeber,

Based on our test, we'd recommend you try below Formula to check the outcome:

=ROUNDDOWN([Injection End]-[Injection Start],0)&" days "&ROUNDDOWN((([Injection End]-[Injection Start])-ROUNDDOWN([Injection End]-[Injection Start],0))*24,0)&" Hours "&ROUND(([Injection End]-[Injection Start]-ROUNDDOWN([Injection End]-[Injection Start],0)-ROUNDDOWN((([Injection End]-[Injection Start])-ROUNDDOWN([Injection End]-[Injection Start],0))*24,0)/24)*24*60,0)&" minutes"

Below attached is our test result for your reference:

Hope the above information can help.

Please feel free to post back if there's any updates.

Best Regards,

Rhoda | Microsoft Community Moderator

Answer 2

Dear JSteeber,

Greetings! Thank you for posting in Microsoft Community.

As per your description, it seems that you are attempting to calculate the duration period within the two columns value on a third column with the format "Days, Hours, Minutes".

If the understanding above is right, we might still need some more time to do further test to see if we can correct the formular or provide a workaround that works.

We'll post back as soon as we find out the solution.

Your understanding and patience will be highly appreciated. We hope that you are keeping safe and well!

Sincerely，

Rhoda | Microsoft Community Moderator