SharePoint List calculated column to count in minutes, not 100's

[LukeSykes]

Is there a way to make a SharePoint list calculated column to multiply in 60s instead of 100s

to do this in excel is used to use the [h]:mm custom format but obviously you cant use this here.

reason being, a working day in my workplace is 7hrs 24minutes, 4 days should add up to 29hrs 36minutes in the hours and minutes format but the calculated column says 28hrs 96 minutes

If this is not possible my project is dead 😞

[grantjenkins]

Hopefully this will keep your project alive 😉

For this example, I'm using a Number column called Duration, and a Calculated Column called Difference.

The formula used in the Calculated Column is:

=INT([Duration]/60)&" Hours, "&MOD([Duration],60)&" Minutes"

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[LukeSykes]

Thanks for this, that's actually quite neat and I believe I can use it elsewhere. -  I actually made a mistake in how I explained this.

I need to sum the total hours and minutes also.

7.24 goes into the 'Hours' column which is a number column and its the totals for that number column that are working it out as 29.36. it would be ideal if there was a way of customising the 'Totals' but of course there isn't, neither is there a way of totalling a calculated column which I think is ridiculous.

I don't think there is a way round this is there?

[grantjenkins]

Can you show your list with some of the data to get a better idea of what you're after?

[LukeSykes]

This is a snapshot of the flow

The 'Outputs...' next to hours is from the compose solution you kindly helped me with and it feeds into a number column called 'Hours Used'

The sum for 'Hours Used' adds up the hours like a calculator as expected, but as my re-explanation suggests, I need it to add up like hours and minutes. The moment you go above 60 in the minutes decimal it throws it out completely.

[grantjenkins]

Are you able to show the expression you used to calculate the Hours Used in your HTTP request Body? I'm still not sure what you are using as source data for the calculation. Should definitely be able to get what you're after though.

[grantjenkins]

Hopefully this is what you're looking for.

See full flow below. I'll go into each of the actions. Note they are all Compose actions.

Used contains the full used hours that will be converted to the appropriate hours and minutes. I've used 28.96 here a per your initial example.

Times splits the data into two parts (hours and minutes).

split(string(outputs('Used')), '.')

Hours add the hours plus the minutes divided by 60.

add(int(first(outputs('Times'))), div(int(last(outputs('Times'))), 60))

Minutes uses the mod (modulus operator) to get the remainder of minutes divided by 60.

mod(int(last(outputs('Times'))), 60)

Running the flow, we would get the following number of hours and minutes (29 hours and 36 minutes)

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[LukeSykes]

Those looks great. However I'm not back in work til Tuesday so will have to try it then.

Looks like I will need 40 composes! Lol

28.96 is actually 4 days of annual leave, so 4 x 7.24

[grantjenkins]

I'm sure there's a much easier way to do this conversion but haven't found anything online yet.

This is what I came up with that you could use as an expression for your fields. Note that this is using a variable called days (string or integer both work). If you didn't need it to be converted to a number, you could remove the float expression. Kind of crazy - there's got to be an easier way 😉

//You would just need to replace variables('days') with your field name

float(
    concat(
        string(
            add(
                int(first(split(string(mul(int(variables('days')), 7.24)), '.'))), 
                div(int(last(split(string(mul(int(variables('days')), 7.24)), '.'))), 60)
            )
        ),
        '.',
        string(mod(int(last(split(string(mul(int(variables('days')), 7.24)), '.'))), 60))
    )
)

[LukeSykes]

Thank you for this

Where would it go in my flow, the HTTP request?

[grantjenkins]

I would assume you would use it in the body of your HTTP request for Hours. And maybe need to replace variables('days') with the field that contains the Inclusive Days perhaps.