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

Notifications

Announcements

Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Automate / Send Email - calculate...
Power Automate
Answered

Send Email - calculate number of weeks between two dates

(0) ShareShare
ReportReport
Posted on by 9

What I am trying to accomplish is to pull from MS Forms the difference between two dates into weeks. this is being done in Send an email (v2). I have a formula working to pull the number of days successfully, but cannot figure out how to either further divide that date or use a completely different formula to get number of weeks instead.

 

div(sub(ticks(outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb']),ticks(outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7'])),864000000000)
 
As an added bonus, I would love to be able to take the number of weeks it produces and multiply it by a set dollar amount to get a total amount of money as a separate field after that calculation above is complete.
 
EDIT: Just adding some screenshots of flow and current result, noticing now it is pulling the entire compose, but hoping you can help confine to just weeksAltFloat for now instead of all variables! Haha
 
PA 1.JPGPA 2.JPGPA 3.JPG
 
 
PA 4.JPG
Categories:
I have the same question (0)
  • v-jefferni Profile Picture
    on at

    Hi @ClassyLemon ,

     

    If you mean the difference between week numbers of two dates, please refer to below blog to get the two week numbers of the dates, and use a sub function to get the result:

    https://ryanmaclean365.com/2020/04/29/calculating-iso-8601-week-number-for-dates-in-power-automate/

     

    Best regards,

  • Verified answer
    eliotcole Profile Picture
    4,363 Moderator on at

    Hi, @ClassyLemon, could you quickly edit into your original question (above) a screenshot of your flow?

     

    Glad to see the use of ticks(), it is definitely the way to go, here. Here's a link to details on the 'ticks' process to assist with any mathematics needed.

     

    So, since you already have the days, using the 864000000000 division, then surely to work out weeks you would just divide the result by 7, no?
     

    Divide By Seven

    I've tried one way to do this, as I didn't have it in my reference stack, and it should be a useful one.

     

    This can all be drawn much further out creating variables for seconds, hours, days, week, differences, which are higher/lower, and whether something is a 'negative' difference. If you really want to do that, it can simplify the expressions here as all of them will be done in their respective variables.

     

    In the below code I've given an example of what you could place in a Compose action to give you a JSON object with everything that you need to work with. I've used a couple of functions in there that you've not got in yours, and that is the createArray(), min(), and max(). All this does each time is to create an array from the two dates, pick the highest, or pick the lowest ticks value. This allows the equation to work, whether someone makes either date higher / lower than the other.
     
    Hopefully you should just be able to paste the entire code from the copy/paste one into the Input field of a Compose action in your flow, otherwise I've detailed it a bit more in the Expressions row:
    Expressions
    Spoiler (Highlight to read)
    JSON without expressions
    {
     "days": DAYS_INT,
     "hasWeeks": HAS_WEEKS_BOOL,
     "weeks": WEEKS_INT,
     "weeksAlt": WEEKS_ALT_INT,
     "weeksAltFloat": WEEKS_ALT_FLOAT,
     "weeksDollar": WEEKS_DOLLAR_INT
    }
    Where:
    • DAYS_INT - Is the integer value of the difference in days
    • HAS_WEEKS_BOOL - Indicates if there's enough days to make a week
    • WEEKS_INT - Only provides an integer count of weeks if there's at least 7 days (rounding down)
    • WEEKS_ALT_INT - Provides an integer count of weeks (rounding down)
    • WEEKS_ALT_FLOAT - Provides an exact count of weeks
    • WEEKS_DOLLAR_INT - Multiplies the amount of weeks by a float type Variable called dollarVAR
    Expressions
    DAYS_INT
    Spoiler (Highlight to read)
    div(
     sub(
     max(
     createArray(
     ticks(
     outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb']
     ), 
     ticks(
     outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7']
     )
     )
     ), 
     min(
     createArray(
     ticks(
     outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb']
     ), 
     ticks(
     outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7']
     )
     )
     )
     ), 
     864000000000
    )
    HAS_WEEKS_BOOL
    Spoiler (Highlight to read)
    greaterOrEquals(
     div(
     sub(
     max(
     createArray(
     ticks(
     outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb']
     ), 
     ticks(
     outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7']
     )
     )
     ), 
     min(
     createArray(
     ticks(
     outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb']
     ), 
     ticks(
     outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7']
     )
     )
     )
     ), 
     864000000000
     ),
     7
    )
    WEEKS_INT
    Spoiler (Highlight to read)
    if(
     greaterOrEquals(
     div(
     sub(
     max(
     createArray(
     ticks(
     outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb']
     ), 
     ticks(
     outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7']
     )
     )
     ), 
     min(
     createArray(
     ticks(
     outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb']
     ), 
     ticks(
     outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7']
     )
     )
     )
     ), 
     864000000000
     ),
     7
     ),
     div(
     div(
     sub(
     max(
     createArray(
     ticks(
     outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb']
     ), 
     ticks(
     outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7']
     )
     )
     ), 
     min(
     createArray(
     ticks(
     outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb']
     ), 
     ticks(
     outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7']
     )
     )
     )
     ), 
     864000000000
     ),
     7
     ),
     null
    )
    WEEKS_ALT_INT
    Spoiler (Highlight to read)
    div(
     div(
     sub(
     max(
     createArray(
     ticks(
     outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb']
     ), 
     ticks(
     outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7']
     )
     )
     ), 
     max(
     createArray(
     ticks(
     outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb']
     ), 
     ticks(
     outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7']
     )
     )
     )
     ), 
     864000000000
     ),
     7
    )
    WEEKS_ALT_FLOAT
    Spoiler (Highlight to read)
    div(
     float(div(
     sub(
     max(
     createArray(
     ticks(
     outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb']
     ), 
     ticks(
     outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7']
     )
     )
     ), 
     min(
     createArray(
     ticks(
     outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb']
     ), 
     ticks(
     outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7']
     )
     )
     )
     ), 
     864000000000
     )),
     float(7)
    )
    WEEKS_DOLLAR_INT
    Spoiler (Highlight to read)
    mul(
     div(
     div(
     sub(
     max(
     createArray(
     ticks(
     outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb']
     ), 
     ticks(
     outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7']
     )
     )
     ), 
     min(
     createArray(
     ticks(
     outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb']
     ), 
     ticks(
     outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7']
     )
     )
     )
     ), 
     864000000000
     ),
     7
     ),
     variables('dollarVAR')
    )
    JSON without expressions { "days": DAYS_INT, "hasWeeks": HAS_WEEKS_BOOL, "weeks": WEEKS_INT, "weeksAlt": WEEKS_ALT_INT, "weeksAltFloat": WEEKS_ALT_FLOAT, "weeksDollar": WEEKS_DOLLAR_INT } Where: DAYS_INT - Is the integer value of the difference in days HAS_WEEKS_BOOL - Indicates if there's enough days to make a week WEEKS_INT - Only provides an integer count of weeks if there's at least 7 days (rounding down) WEEKS_ALT_INT - Provides an integer count of weeks (rounding down) WEEKS_ALT_FLOAT - Provides an exact count of weeks WEEKS_DOLLAR_INT - Multiplies the amount of weeks by a float type Variable called dollarVAR Expressions DAYS_INT div( sub( max( createArray( ticks( outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb'] ), ticks( outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7'] ) ) ), min( createArray( ticks( outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb'] ), ticks( outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7'] ) ) ) ), 864000000000 ) HAS_WEEKS_BOOL greaterOrEquals( div( sub( max( createArray( ticks( outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb'] ), ticks( outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7'] ) ) ), min( createArray( ticks( outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb'] ), ticks( outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7'] ) ) ) ), 864000000000 ), 7 ) WEEKS_INT if( greaterOrEquals( div( sub( max( createArray( ticks( outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb'] ), ticks( outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7'] ) ) ), min( createArray( ticks( outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb'] ), ticks( outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7'] ) ) ) ), 864000000000 ), 7 ), div( div( sub( max( createArray( ticks( outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb'] ), ticks( outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7'] ) ) ), min( createArray( ticks( outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb'] ), ticks( outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7'] ) ) ) ), 864000000000 ), 7 ), null ) WEEKS_ALT_INT div( div( sub( max( createArray( ticks( outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb'] ), ticks( outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7'] ) ) ), max( createArray( ticks( outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb'] ), ticks( outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7'] ) ) ) ), 864000000000 ), 7 ) WEEKS_ALT_FLOAT div( float(div( sub( max( createArray( ticks( outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb'] ), ticks( outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7'] ) ) ), min( createArray( ticks( outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb'] ), ticks( outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7'] ) ) ) ), 864000000000 )), float(7) ) WEEKS_DOLLAR_INT mul( div( div( sub( max( createArray( ticks( outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb'] ), ticks( outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7'] ) ) ), min( createArray( ticks( outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb'] ), ticks( outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7'] ) ) ) ), 864000000000 ), 7 ), variables('dollarVAR') )
    Copy/Paste
    Spoiler (Highlight to read)
    {
     "days": @{div(
     sub(
     max(
     createArray(
     ticks(
     outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb']
     ), 
     ticks(
     outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7']
     )
     )
     ), 
     min(
     createArray(
     ticks(
     outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb']
     ), 
     ticks(
     outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7']
     )
     )
     )
     ), 
     864000000000
    )},
     "hasWeeks": @{greaterOrEquals(
     div(
     sub(
     max(
     createArray(
     ticks(
     outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb']
     ), 
     ticks(
     outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7']
     )
     )
     ), 
     min(
     createArray(
     ticks(
     outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb']
     ), 
     ticks(
     outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7']
     )
     )
     )
     ), 
     864000000000
     ),
     7
    )},
     "weeks": @{if(
     greaterOrEquals(
     div(
     sub(
     max(
     createArray(
     ticks(
     outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb']
     ), 
     ticks(
     outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7']
     )
     )
     ), 
     min(
     createArray(
     ticks(
     outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb']
     ), 
     ticks(
     outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7']
     )
     )
     )
     ), 
     864000000000
     ),
     7
     ),
     div(
     div(
     sub(
     max(
     createArray(
     ticks(
     outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb']
     ), 
     ticks(
     outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7']
     )
     )
     ), 
     min(
     createArray(
     ticks(
     outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb']
     ), 
     ticks(
     outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7']
     )
     )
     )
     ), 
     864000000000
     ),
     7
     ),
     null
    )},
     "weeksAlt": @{div(
     div(
     sub(
     max(
     createArray(
     ticks(
     outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb']
     ), 
     ticks(
     outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7']
     )
     )
     ), 
     min(
     createArray(
     ticks(
     outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb']
     ), 
     ticks(
     outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7']
     )
     )
     )
     ), 
     864000000000
     ),
     7
    )},
     "weeksAltFloat": @{div(
     float(div(
     sub(
     max(
     createArray(
     ticks(
     outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb']
     ), 
     ticks(
     outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7']
     )
     )
     ), 
     min(
     createArray(
     ticks(
     outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb']
     ), 
     ticks(
     outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7']
     )
     )
     )
     ), 
     864000000000
     )),
     float(7)
    )},
     "weeksDollar": @{mul(
     div(
     div(
     sub(
     max(
     createArray(
     ticks(
     outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb']
     ), 
     ticks(
     outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7']
     )
     )
     ), 
     min(
     createArray(
     ticks(
     outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb']
     ), 
     ticks(
     outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7']
     )
     )
     )
     ), 
     864000000000
     ),
     7
     ),
     variables('dollarVAR')
    )}
    }
    { "days": @{div( sub( max( createArray( ticks( outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb'] ), ticks( outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7'] ) ) ), min( createArray( ticks( outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb'] ), ticks( outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7'] ) ) ) ), 864000000000 )}, "hasWeeks": @{greaterOrEquals( div( sub( max( createArray( ticks( outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb'] ), ticks( outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7'] ) ) ), min( createArray( ticks( outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb'] ), ticks( outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7'] ) ) ) ), 864000000000 ), 7 )}, "weeks": @{if( greaterOrEquals( div( sub( max( createArray( ticks( outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb'] ), ticks( outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7'] ) ) ), min( createArray( ticks( outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb'] ), ticks( outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7'] ) ) ) ), 864000000000 ), 7 ), div( div( sub( max( createArray( ticks( outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb'] ), ticks( outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7'] ) ) ), min( createArray( ticks( outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb'] ), ticks( outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7'] ) ) ) ), 864000000000 ), 7 ), null )}, "weeksAlt": @{div( div( sub( max( createArray( ticks( outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb'] ), ticks( outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7'] ) ) ), min( createArray( ticks( outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb'] ), ticks( outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7'] ) ) ) ), 864000000000 ), 7 )}, "weeksAltFloat": @{div( float(div( sub( max( createArray( ticks( outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb'] ), ticks( outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7'] ) ) ), min( createArray( ticks( outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb'] ), ticks( outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7'] ) ) ) ), 864000000000 )), float(7) )}, "weeksDollar": @{mul( div( div( sub( max( createArray( ticks( outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb'] ), ticks( outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7'] ) ) ), min( createArray( ticks( outputs('Get_response_details')?['body/rf7c97046e5d6431085761868856019cb'] ), ticks( outputs('Get_response_details')?['body/re43df35f36244cd2a201d30d20569fe7'] ) ) ) ), 864000000000 ), 7 ), variables('dollarVAR') )} }

    If you then just initialize an Object type Variable, you can use that Compose action to set the value and call any of those wherever you need. Or you can just call them directly from the Compose, or make a JSON Parse of it afterwards.

     

    The weeksAltFloat value provides the *exact* amount of weeks (in a decimal floating point number) to times the dollar value by. I just wrapped the expressions in float() conversions.

     

    EDIT - If you've already copied, please try again, I'd accidentally left two 'max' values in each expression.

  • ClassyLemon Profile Picture
    9 on at

    Thanks very much for your reply and sorry for the delay just getting back from some time off. I am closer to the desired result!

     

    I'm going to edit my original post with some added screenshots to demonstrate what my flow looks like and the current result I am receiving from the 'weeksAltFloat'. Just a note I am super novice!!! I removed the 'weeksdollar' part for now as it is more important to get the weeks with decimal for the time being. The dollar figure will vary based on certain types of positions at two different pay rates.

  • eliotcole Profile Picture
    4,363 Moderator on at

    If you're going to go with putting the number in a variable, make sure that the 'weeksAltFloat' variable is a Float type variable. 😉

  • ClassyLemon Profile Picture
    9 on at

    One more question and then I will leave you alone... In my last screenshot you can see how the text is displaying in the email. How should I alternatively use the flow so that it just shows the numeric value as opposed to "weekAltFloat": 5.3243234 

     

     

  • eliotcole Profile Picture
    4,363 Moderator on at

    OK, I'll let you into a secret that will mean that you don't need your weeksAltFloat variable.

    1. Change that variable to an 'Object' type
    2. Rename it to dataVAR
    3. Initialise/Set dataVAR selecting that Compose action

     

    Now, whenever you want ANY of the values in that the dataVAR object variable you can just use this expression (this one will get the weeksAltFloat):

    variables('dataVAR')?['weeksAltFloat']

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

Forum hierarchy changes are complete!

In our never-ending quest to improve we are simplifying the forum hierarchy…

Ajay Kumar Gannamaneni – Community Spotlight

We are honored to recognize Ajay Kumar Gannamaneni as our Community Spotlight for December…

Leaderboard > Power Automate

#1
Michael E. Gernaey Profile Picture

Michael E. Gernaey 501 Super User 2025 Season 2

#2
Tomac Profile Picture

Tomac 323 Moderator

#3
abm abm Profile Picture

abm abm 237 Most Valuable Professional

Last 30 days Overall leaderboard