Looking for method to determine a date is between two dates

Hello!

I am looking for a way to determine which fiscal period a date falls in. So for example 2024-02-05 would fall in P1 (2024-01-29 to 2024-02-25). I am trying to use an IF formula but it's not giving me the correct results.

I'm looking for a way to:

1. Use formula tool like in the workflow attached to manually enter the date IF statement

2. Instead use the reference table attached to automate the process and more easily manage for future changes (preferred solution)

I'd like to avoid appending the reference table, the dataset I'm working with is already so large that this would slow down the workflow possibly too much so I'd keep this as last resort.

Thanks in advance !!

Sample - Dates Between.yxmd

Designer Cloud

Date Time

Help

Accepted answers

alexnajm

Use a Generate Rows tool to calculate all of the dates between [Starts] and [Ends]. Then you can Join on those individual dates to get the Period added on!

Sample - Dates Between.yxmd

All comments

OTrieger

@Rlopez21
Your formula is wrong.

Rlopez21

Thank you so much that worked! I was over complicating it.

Do you know any way to use the reference table instead of writing the dates into the formula? I'll leave this post uncomplete for now to see if I can get a solution for that part.

alexnajm

Use a Generate Rows tool to calculate all of the dates between [Starts] and [Ends]. Then you can Join on those individual dates to get the Period added on!

Sample - Dates Between.yxmd

Rlopez21

Thank you so much that worked perfectly!! Exactly what I was looking for.

Quick Links

This months top contributors

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335

JeffF 335