web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id : RnyNLAnsbUCWF+JXyIPXNY
Power Automate - Building Flows
Unanswered

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

Like (0) ShareShare
ReportReport
Posted on 26 Jan 2023 17:25:31 by

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 Profile Picture
    11,059 Moderator on 31 Jan 2023 at 11:58:12
    Re: SharePoint List calculated column to count in minutes, not 100's

    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.

     

    grantjenkins_0-1675166222518.png

     

  • LukeSykes Profile Picture
    on 31 Jan 2023 at 11:31:45
    Re: SharePoint List calculated column to count in minutes, not 100's

    Thank you for this

     

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

  • grantjenkins Profile Picture
    11,059 Moderator on 29 Jan 2023 at 13:19:36
    Re: SharePoint List calculated column to count in minutes, not 100's

    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 Profile Picture
    on 29 Jan 2023 at 09:50:51
    Re: SharePoint List calculated column to count in minutes, not 100's

    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 Profile Picture
    11,059 Moderator on 29 Jan 2023 at 06:43:34
    Re: SharePoint List calculated column to count in minutes, not 100's

    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.

    grantjenkins_0-1674974107312.png

     

    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.

    grantjenkins_1-1674974155123.png

     

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

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

    grantjenkins_2-1674974221167.png

     

    Hours add the hours plus the minutes divided by 60.

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

    grantjenkins_3-1674974272226.png

     

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

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

    grantjenkins_4-1674974352217.png

     

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

    grantjenkins_5-1674974432268.png


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

  • grantjenkins Profile Picture
    11,059 Moderator on 29 Jan 2023 at 06:22:50
    Re: SharePoint List calculated column to count in minutes, not 100's

    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.

  • LukeSykes Profile Picture
    on 27 Jan 2023 at 16:21:30
    Re: SharePoint List calculated column to count in minutes, not 100's

    This is a snapshot of the flow

     

    LukeSykes_0-1674836061080.png

    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'

    LukeSykes_1-1674836158327.png

     

    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 Profile Picture
    11,059 Moderator on 27 Jan 2023 at 12:44:55
    Re: SharePoint List calculated column to count in minutes, not 100's

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

  • LukeSykes Profile Picture
    on 27 Jan 2023 at 11:19:59
    Re: SharePoint List calculated column to count in minutes, not 100's

    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 Profile Picture
    11,059 Moderator on 27 Jan 2023 at 03:46:55
    Re: SharePoint List calculated column to count in minutes, not 100's

    Hopefully this will keep your project alive 😉

     

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

    grantjenkins_0-1674791067369.png

     

    The formula used in the Calculated Column is:

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

     

    grantjenkins_1-1674791166904.png


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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing our 2025 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for…

Paul Stork – Community Spotlight

We are honored to recognize Paul Stork as our July 2025 Community…

Congratulations to the June Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Automate

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 497 Super User 2025 Season 2

#2
David_MA Profile Picture

David_MA 436 Super User 2025 Season 2

#3
Riyaz_riz11 Profile Picture

Riyaz_riz11 244 Super User 2025 Season 2