Multiple IF formula between two dates
Hello there.
I am desperately trying to complete this formula by I dont know what I am doing wrong. Can anyone please assist me. I need to bring back the "Approved Month" based on the HR Managers Approval Date, but it only brings back FEB 2023 and does not bring back the other months.
Any assistance would be greatly appreciated.
my formula is as follws:
=IF(AND([HR Mamger Approval Date]@row >= DATE(2023; 1; 26); [HR Mamger Approval Date]@row <= DATE(2023; 2; 25)); "FEB 2023"; IF(AND([HR Mamger Approval Date]@row >= DATE(2023; 2; 26); [HR Mamger Approval Date]@row <= DATE(2023; 3; 25)); "MAR 2023"; IF(AND([HR Mamger Approval Date]@row >= DATE(2023; 3; 26); [HR Mamger Approval Date]@row <= DATE(2023; 4; 25)); "APRIL 2023"; IF(AND([HR Mamger Approval Date]@row >= DATE(2023; 4; 26); [HR Mamger Approval Date]@row <= DATE(2023; 5; 25)); "MAY 2023"; IF(AND([HR Mamger Approval Date]@row >= DATE(2023; 5; 26); [HR Mamger Approval Date]@row <= DATE(2023; 6; 25)); "JUN 2023"; IF(AND([HR Mamger Approval Date]@row >= DATE(2023; 6; 26); [HR Mamger Approval Date]@row <= DATE(2023; 7; 25)); "JUL 2023"; IF(AND([HR Mamger Approval Date]@row >= DATE(2023; 7; 26); [HR Mamger Approval Date]@row <= DATE(2023; 8; 25)); "AUG 2023"; IF(AND([HR Mamger Approval Date]@row >= DATE(2023; 8; 26); [HR Mamger Approval Date]@row <= DATE(2023; 9; 25)); "SEP 2023"; IF(AND([HR Mamger Approval Date]@row >= DATE(2023; 9; 26); [HR Mamger Approval Date]@row <= DATE(2023; 10; 25)); "OCT 2023"; IF(AND([HR Mamger Approval Date]@row >= DATE(2023; 10; 26); [HR Mamger Approval Date]@row <= DATE(2023; 11; 25)); "NOV 2023"; IF(AND([HR Mamger Approval Date]@row >= DATE(2023; 11; 26); [HR Mamger Approval Date]@row <= DATE(2023; 12; 25)); "DEC 2023"; IF(AND([HR Mamger Approval Date]@row >= DATE(2023; 12; 26); [HR Mamger Approval Date]@row <= DATE(2024; 1; 25)); "JAN 2024"; IF(AND([HR Mamger Approval Date]@row >= DATE(2024; 1; 26); [HR Mamger Approval Date]@row <= DATE(2024; 2; 25)); "FEB 2024"; IF(AND([HR Mamger Approval Date]@row >= DATE(2022; 12; 26); [HR Mamger Approval Date]@row <= DATE(2023; 1; 25)); "JAN 2023"; ""))))))))))))))
Answers
-
@Melly Complete change of logic will make this so much easier
First, you can find the month and year by the MONTH and YEAR Functions.
=MONTH([HR Mamger Approval Date]@row)+ " " + YEAR([HR Mamger Approval Date]@row)
That would return something like "2 2023".
No need for a massive IF statement that will constantly need to be rewritten
Next
The trick to getting "2" to be "Feb" is to create another table
Let's call it MonthTable
Now you can do a VLOOKUP on the Month "2" and return Feb or 8 and return Aug
The new formula would look like this
=VLOOKUP(MONTH([HR Mamger Approval Date]@row), {MonthTable Lookup}, 2, false) + " " + YEAR([HR Mamger Approval Date]@row)
Note: You must define the Lookup table {MonthTable Lookup} range in the formula as you type it.. You need to choose the first 2 columns.
Feel free to reach out if you have problems
Brent C. Wilson, P.Eng, PMP, Prince2
Facilityy Professional Services Inc.
http://www.facilityy.com
-
Hi Brent
Thank you, but the I am working with a timehseet and payment cycle, so for example: the "FEB" cycle starts on the 26 Jan 2023 and ends on 25 Feb 2023 (the next month) call it the "FEB" payment run so I dont think the normal dates and years will work. it needs to pick up the timsheets that were created ("created date") the dates between 26 Jan and 25 Feb and call them "Feb 2023" so that I can pull a pivot table for all timesheets that need to be paid in FEB.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.2K Get Help
- 360 Global Discussions
- 199 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 136 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!