determine if either of two dates cover the last 12 months

[Dakotas]

Hi there,

I'm working on some client data and I'm trying to decide if a job was ever 'active' during the last 12 months.

I have start and end date fields for all of my jobs. For example, Job 2 started more than 12 months ago and will end in the future, but it falls into my target data set of jobs that have had active work in the last twelve months.

Any ideas on how I could get to determine this?

Thanks!

Dakota

Job	Start date	End date
1	7/1/2018	2/1/2019
2	10/1/2016	5/1/2020
3	5/1/2018	8/1/2019

[bpatel]

hi @Dakotas,

I mocked up a workflow from what i think you are looking for. Let me know if this helps or is on the right track.

[Dakotas]

Thanks for taking a look! I think this helps to determine how long a job is but I don't think this will work in this instance as it doesn't capture the fact that Job 2 was active during the past twelve months. (started in 2016 but goes until 2020)

[JosephSerpis · Accepted solution]

Hi @Dakotas I mocked up a workflow let me know what you think?

[estherb47]

Hi @Dakotas 

This approach generates all of the dates between the start and end date. If any fall within the last year (based on today's date), they will be assigned a value of 1, else 0. Add up all of those values for each row, and then filter where there are one or more date range matches.

Let me know if this helps. Instead of 2 DateTime parse tools, I used one MultiField formula. Either approach works very well.

Cheers!

Esther

[Dakotas]

Thanks! This worked well, I ended up switching to 365 day instead of 12 months to catch jobs that are less than a month long and the logic held there too 🙂

[Dakotas]

Thanks Esther, I went with an earlier solution to reduce the amount of data generated but I appreciate the insight, this would have worked well too 🙂