web

You’re offline. This is a read only version of the page.

News and Announcements icon

Power Platform CommunityForums Power Automate Transfer formula to fl...

Power Automate

Unanswered

Transfer formula to flow with dynamic content

(0) Share

Report

Report

Posted on by Weefergie56

9

Hi, novice here and looking for a bit of advice.

I am trying to work out an age from an ID number provided in an MS form and email it back to the responder. The first six digits is the DOB.

ID: 0101010000 = 01/01/01 = 24 years old

It works fine for me in excel using this formula:

=DATEDIF(DATE(IF(MID(A2,5,2)>TEXT(TODAY(),"YY"),"19"&MID(A2,5,2),"20"&MID(A2,5,2)),MID(A2,3,2),LEFT(A2,2)),TODAY(),"y")

How to I transfer this to my flow replacing A2 with dynamic content? It might be that my formula is a bit convoluted but it got me there in excel.

I have tried various ways of replacing A2 with the dynamic content and moving the output after datedif and date but no luck. Any ideas?

I know my caveat is that if they are > 100 then this will cycle them back to being a baby but there isn't enough data to account for that here.

Thanks

Categories:

I have the same question (0)

All responses Img

All responses (2)

Answers Img

Answers (0)

Chriddle 8,443 Super User 2025 Season 2 on at

Like (0)

Report
Assuming the value is in the variable ("ID") and its format is 'ddMMyy0000',
subtract the year part of that ID from the corresponding utcNow positions as an integer subtraction.

Then add 100 and calculate modulus 100 to handle negative values (the year is in the 1900s).

If the current month/day is less than the positions in the Id (you need to change the positions 0,1 with 2,3), substract 1

mod( sub( add( sub( int(utcNow('yy')), int(substring(variables('Id'), 4, 2)) ), 100 ), if( less( utcNow('MMdd'), join(reverse(chunk(substring(variables('Id'), 0, 4), 2)), '') ), 1, 0 ) ), 100 )

Was this reply helpful? Yes No
David_MA 12,982 Super User 2025 Season 2 on at

Like (0)

Report

You will need eight digits otherwise how do you know if the date is from the 1900s or 2000s? If you get eight digits, you can use this expression:

div(div(sub(ticks(utcNow()), ticks(formatDateTime(concat(substring('01101972',4,4),'-',substring('01101972',0,2),'-',substring('01101972',2,2)), 'yyyy-MM-dd'))), 864000000000), 365.25)

Just replace 01101972 with your dynamic value. For the value shown, if run today, it would return 52.99657768651608. You'll need to do some extra work to return just 52. I will not bother explaining that if you cannot use eight digits for the DOB.

Was this reply helpful? Yes No

Join the conversation