nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

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

Date Time

Accepted answers

JosephSerpis

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

Dates190819.yxmd

All comments

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.

determining dates.yxmd

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

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

Dates190819.yxmd

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

DatesInRange.yxmd

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 🙂

Quick Links

Popular Tags

This months top contributors

atcodedog05 19598

Qiu 15906

binu_acs 15783

MarqueeCrew 13709

apathetichell 13703