Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Help with IFS function and referencing dates

0

Hi All!  I'm hoping there is a simple fix and I'm just not seeing, but I've been banging my head against a wall all day to write this formula.

I'm putting the formula in cell B2.  What I want it to do is look at a date entered in cell C2, evaluate what date range it falls between, and display text in cell B2 based on the date range expression that evaluates true.  For example, if the date in cell C2 between 2/1/2023 and 4/30/23, I want the text "FY24 Q1" to show in cell B2.

I'm using the IFS function to try and keep the nesting simple.  And I'm using the AND function to evaluate the date range.  And I've also tried it 2 different ways - one by enclosing each AND statement in parens and one by not enclosing each AND statement (I saw it done successfully both ways in a couple of YouTube videos).  But I keep getting a Formula Parse Error.

Can anyone tell me what I'm doing wrong?  Here are my two different attempts:

=IFS(AND(C2>=DATEVALUE("2/1/2023"),C2<=DATEVALUE("4/30/2023")), "FY24 Q1", AND(C2>=DATEVALUE("5/1/2023"),C2<=DATEVALUE("7/31/2023")), "FY24 Q2", AND(C2>=DATEVALUE("8/1/2023"),C2<=DATEVALUE("10/31/2023")), "FY24 Q3", AND(C2>=DATEVALUE("11/1/2023"),C2<=DATEVALUE("1/31/2024")), "FY24 Q4", TRUE," "))))

=IFS(AND(C2>=DATEVALUE("2/1/2023"),C2<=DATEVALUE("4/30/2023")), "FY24 Q1", (AND(C2>=DATEVALUE("5/1/2023"),C2<=DATEVALUE("7/31/2023")), "FY24 Q2", (AND(C2>=DATEVALUE("8/1/2023"),C2<=DATEVALUE("10/31/2023")), "FY24 Q3", (AND(C2>=DATEVALUE("11/1/2023"),C2<=DATEVALUE("1/31/2024")), "FY24 Q4", TRUE," "))))

excel
MS Office Version: Google Sheets
posted by: tlsmith1103 on Thu Mar 2, 2023 at 6:48 pm
Answer
Discuss

Discussion

Why no response to my Answer? (See below)
John_Ru (rep: 6142) Apr 5, '23 at 10:28 am
Add to Discussion

Answers

0

Hi and welcome to the Forum. 

The IFS function in Excel (the subject of this Forum) seems to be the same as you're using in Google Sheets. This formula will work and the final agrument pair (in bold below, starting True) gives the default value, Q4:

=IFS(AND(C2>=DATEVALUE("2/1/2023"),C2<=DATEVALUE("4/30/2023")), "FY24 Q1", AND(C2>=DATEVALUE("5/1/2023"),C2<=DATEVALUE("7/31/2023")), "FY24 Q2", AND(C2>=DATEVALUE("8/1/2023"),C2<=DATEVALUE("10/31/2023")), "FY24 Q3", TRUE, "FY24 Q4") 

(Use the Select All bar to copy the text). 

For users not using US style dates (mm/dd/yyyy), the formula is:

=IFS(AND(C2>=DATEVALUE("01/02/2023"),C2<=DATEVALUE("30/04/2023")), "FY24 Q1", AND(C2>=DATEVALUE("01/05/2023"),C2<=DATEVALUE("31/07/2023")), "FY24 Q2", AND(C2>=DATEVALUE("01/08/2023"),C2<=DATEVALUE("31/10/2023")), "FY24 Q3", TRUE, "FY24 Q4") 

In fact a simple formula is possible. If Data Validation is applied to cell C2 to limit its input value between 01  Feb 2023 and 30 Jan 2024 (start and end of your FY24), then these shorter versions will work:

=IFS(C2<=DATEVALUE("4/30/2023"), "FY24 Q1", C2<=DATEVALUE("7/31/2023"), "FY24 Q2", C2<=DATEVALUE("10/31/2023"), "FY24 Q3", C2<=DATEVALUE("1/30/2024"), "FY24 Q4")

or using the final argument pair to give a default:

=IFS(C2<=DATEVALUE("4/30/2023"), "FY24 Q1", C2<=DATEVALUE("7/31/2023"), "FY24 Q2", C2<=DATEVALUE("10/31/2023"), "FY24 Q3", TRUE, "FY24 Q4")

Hope this fixes things for you. If so, please mark this Answer as Selected. 

posted by: John_Ru (rep: 6142) on Fri Mar 3, 2023 at 4:39 am
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login