Help

Formula to show dates before a certain date in the past

Topic Labels: Formulas
Solved
Jump to Solution
2546 4
cancel
Showing results for 
Search instead for 
Did you mean: 
Maria
7 - App Architect
7 - App Architect

Hi Trying to create a formula to show all records before an actual date but not say today, last week etc.

Can see how to do if I want today etc but not an actual date

Originally, wanted to created formula to show records that fall in my accounts year end so within date range of 1 April 2020 and 31 March 2021 = 20/21. And to use for every year.

Going around in circles to find!

Can anyone help please?

1 Solution

Accepted Solutions
Ilan_Ben_Yaakov
6 - Interface Innovator
6 - Interface Innovator

IF(AND(IS_AFTER(Date,"3/1/2020"),IS_BEFORE(Date,"4/31/2021")),"20/21",IF(AND(IS_AFTER(Date,"3/1/2021"),IS_BEFORE(Date,"4/31/2022")),"21/22"))

See Solution in Thread

4 Replies 4
Ilan_Ben_Yaakov
6 - Interface Innovator
6 - Interface Innovator

just use the date like this
IF(Date>"1/2/2021","y","n")

Thanks Ilan really thought I tried that one first but must have not quite got it correct!

So that is step one. My ultimate aim is to create formula that will update all future records

ie
After 1/4/2020 and Before 31//3/2021 = 20/21
After 1/4/2021 and Before 31//3/2022 = 21/22

Do you have any ideas how to create a formula to do this without any more manual input.

Any help much appreciated. Thank you

Ilan_Ben_Yaakov
6 - Interface Innovator
6 - Interface Innovator

IF(AND(IS_AFTER(Date,"3/1/2020"),IS_BEFORE(Date,"4/31/2021")),"20/21",IF(AND(IS_AFTER(Date,"3/1/2021"),IS_BEFORE(Date,"4/31/2022")),"21/22"))

Thank you so much! You are so kind :grinning: