Hi,
I have a collection at the moment with dates stored in text format example 2022-11-25.
I need an if statement to see if the date falls between Oct-1 – Mar-31. (I don't need the year)
Basically does the 11-25 fall between the dates Oct-1 – Mar-31. I can't seem to do it without the year.
Any help is greatly appreciated.
Thanks
No problem 🙂
hahah, omg yes it's as simple as that.
Sometimes you think you need something complex and it turns out to be simple.
Thank you
ok I see. If you range is fixed to Oct-1 – Mar-31, then the formula is simple.
The day is irrelevant because any day from 1st to 31st of any month would be acceptable so if years are also not important then really you're only checking the month.
If( (Month(RefDate) >= 10 || Month(RefDate) <= 3),
"Date is within Oct 1st to Mar 31st",
"It isn't"
)
I'm a little confused though, is the range always Oct 1st to Mar 31st? Is the date you're comparing a single date or another range?
Thank you, although I need it without the year as each date row in my collection I need to see if it falls between those date months and days. The year might be different on each date.
If it can not be done in a formula I might just replace the year to be the same across all dates just to see if it falls in the range. Would rather do it in the formula though as some dates can spread across 2 different years as I need to see if the current & previous dates fall in between those dates
I could use say date range as
(Oct-1-Current Year) & (Mar-31-Current Year + 1)
Then
if I have date period as DEC-1-2010 - FEB-20-2011 I will check if years are not equal then change to
DEC-1-Current Year & FEB-20-Current Year + 1 then see if its in the date range.
If years are the same then
FEB-20-Current Year & DEC-1-Current Year then see if its in the date range.
Unsure how to write this formula though.
Hi @christian12,
I would do this by using the year to create a range and then compare to your reference date:
// Use With() to set up some input parameters
With({
RangeStart: DatePicker1_4.SelectedDate,
RangeEnd: DatePicker1_5.SelectedDate,
RefDate: DatePicker1_6.SelectedDate
},
With({
// Make sure our dates are the right way around
PastDate: Min( RangeStart, RangeEnd),
FutureDate: Max( RangeStart, RangeEnd)
},
// Check if date is in range, including first and last days
$"date is {If( !(RefDate >= PastDate && RefDate <= FutureDate), "not ")}in range"
)
)
You can use DateValue() with the above code to convert your text dates into date objects:
DateValue("2022-11-25")
Hope this is helpful?