2

I am trying to use Flows to get a range of Dates as Integers that start in 2017 and end in 2018

I get my Data from the Get Events (V2) Connector and getting the Start Time and End Time to get the dates.

I am using the range(), sub() and dayOfYear() functions.

My function looks like this:

range(dayOfYear(StartDate), sub(dayOfYear(EndDate),dayOfYear(StartDate)))

I run into issues when the Start Date is before new year and End Date is after New Year.

Is there a way around it?

0

I faced a similar problem with the financial year running over two years.

I would suggest using your Start Date field as a Date field, so no need to convert it to an Integer. The calculated fields below work on Date fields and return the data as Text.

Here's the plan:

I used some calculated fields to grab the month and year - using the 'Created Date' as the date source field. My data is returned as text rather the integers, but maybe you can adapt your fields or the formula so it works for you?

To grab the year data (Year field)

=""&YEAR(Created)

To grab the month data (Month field)

=TEXT(Created,"mm")

Period field

=IF(Month<"11",Year,TEXT(Year+1,"0"))

This calculation looks at the Month field. If the Created date is in Nov 2017 or Dec 2017, the calculation will add +1 to the Period and return 2018.

If you want your Period/Financial Year to run from e.g. April then substitute the 11 for a 4 - in this case your financial year would run from 01/04/2017 - 30/03/2018. If a Created date was between 01/04/2017-30/03/2018 the Period returned would be 2018.

So in my case (using the Nov to Oct dates) the calculation will see that for a Nov 2017 date a +1 should be added to the year if the month is Nov or Dec.

List of Field Names

Created = Created Date field (for your problem, just read 'Created Date' as 'Start Date'

Month = takes the month from the created date and returns it as text

Year = takes the year from the created date and returns it as text

Period = calculation which uses the data from the calculated Month and Year fields and returns data as text

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.