Leap Years

[WDRC]

All

Any idea how I automate adding say 365 days to a date or 366 in a leap year.  The issue in mind is that an insurance certifcate runs out at the end of the month.  So during a process I always want to be able to add the correct amount of days for the different condtions.  So thus the certicate expiry should allways be the end of the month not say a day early in case of a leap year.  It then needs to write the correct date to the SP list.  I only ask this as 2020 will be such a year.

Regards,

Andrew

[v-yamao-msft · Accepted solution]

Hi @WDRC,

Generally, if (year is not divisible by 4) then (it is a common year)

else if (year is not divisible by 100) then (it is a leap year)

else if (year is not divisible by 400) then (it is a common year)

else (it is a leap year)

About your scenario, it seems that you would like to add 365 days to a date if it is a common year, while 366 days to a date if it is a leap year.

I have made the following flow for your reference.

For testing, it is triggered by a Manual button. Add a Date input for the trigger.

Add Compose action to get the year of a date:

formatDateTime(triggerBody()['date'],'yyyy')

 Add Compose 2 action to convert the returned year from Compose to a number:

float(outputs('Compose'))

Add Compose 3 action to determine whether the year is a leap year or not. If yes, return 366, if not, return 365:

if(or(equals(mod(outputs('Compose_2'),400),0),and(equals(mod(float(outputs('Compose')),4),0),not(equals(mod(outputs('Compose_2'),100),0)))),'366','365')

Hope it could be your reference.

 

Best regards,

Mabel

[WDRC]

@v-yamao-msft,

Thanks, will give that a go tomorrow and let you know if I have any issues.

Regards,

Andrew

[WDRC]

@v-yamao-msft,

I have now been able to put this into flow and have the following:

So thank you, it proves that all the compose actions works even with my renaming. Now to get the rest of my flow woking.

Regards

Andrew

[v-yamao-msft]

Hi @WDRC,

Thanks for updating.

If it works for you, please mark is as Answer.

Best regards,

Mabel

[WDRC]

Mabel @v-yamao-msft & Barry @v-bacao-msft,

Thank you for your collective help on the above over several posts to this forum.

Let’s reaffirm what we need to with an actual example for one supplier as below:

Existing Expiry	Days to Add	New Expiry
31/03/2017	365	31/03/2018
31/03/2018	365	31/03/2019
31/03/2019	366	31/03/2020
31/03/2020	365	31/03/2021

There are two ways I have been trying to achieve this without much success.

USING FLOW

I know the compose functions above are working well and I generally understand what they are doing.  The issue comes with trying to use them in the rest of the flow to set the new expiry when the company send in their new certificate in.  The easiest thing would be to get the company to rename their certificate and then use part of the file name (date) and then write that to the list.  I cannot rely on them renaming the file in this specific way as this will be too complicated to explain in an email and to tell them why I am needing this. 

So, the best way is to accept what we are given and work with that.  So, with flow1 we send out request for new certificate and they reply with the new one attached.  This is the trigger for flow2 (this flow).  We then check them against the SP list (got this working  and to ensure we write the data against the correct line item).  This is where I believe I need to be using the existing expiry date and undertaking a test by adding 12 months to it.  But this where the complexity starts and using the various compose functions to undertake this test defeats me. 

USING SP LIST CALCULATION

The other option is to look at the existing expiry column in the SP list and then have another column called new expiry.  The new expiry shall be a calculated column and shall add the correct amount of days to achieve the same results of the above.  The issue is I need to have a calculation like the below expression to achieve this that works in a SP column:

if(or(equals(mod(outputs('Convert_Year_to_Number'),400),0),and(equals(mod(float(outputs('Get_the_Year_of_a_Date')),4),0),not(equals(mod(outputs('Convert_Year_to_Number'),100),0)))),'366','365')

We may need to achieve this via multiple columns and I am happy to do this if required.

So which way do I go the flow route to add the correct amount of days or the SP list way.  Whichever way I do go.  The main aim is to ensure the correct amount of days are added to the existing expiry.

Perhaps I am not thinking about this as logically as I should, and I may need to combine both methods to get one result.  Or I need to be thinking about a completly different way which is far simpler.

I am going to keep the "date testing" open and make all future comments on this matter against the “leap years” post.  When all is sorted and running I shall mark the “date testing” as solved.  I am sorry for the cross posting.

Regards,

Andrew

[eliotcole]

I realise that this is old, however I have a one liner solution for determining a leap year that you can produce a handy boolean with.

This works on the principle that in any leap year February will have 29 days in it.

equals(formatDateTime(addDays(concat(formatDateTime(utcNow(), 'yyyy'), '-02-01T00:00:00.0000000Z'), 28), 'MMM'), 'Feb')

equals(formatDateTime(addDays(concat(formatDateTime(utcNow(), 'yyyy'), '-02-01T00:00:00.0000000Z'), 28), 'MMM'), 'Feb')

Breaking that down:

1. formatDateTime(utcNow(), 'yyyy') - This gets the year the flow is running in, now.
2. concat(#1, '-02-01T00:00:00.0000000Z') - This ensures the string from #1 is the full time and date string for midnight on the first of February of this year.
3. addDays(#2, 28) - This adds 28 days to the value created in #2.
4. formatDateTime(#3, 'MMM') - This formats the date produced in #3 into the shortcode for a month (Jan, Feb, Mar, May, etc).
5. equals(#4, 'Feb') - If #4 month shortcode equals "Feb" this will show true that this year is a leap year.