Multiple IF formula between two dates

Melly
Melly ✭✭
edited 02/28/23 in Formulas and Functions

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

  • Brent Wilson
    Brent Wilson ✭✭✭✭✭

    @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

  • Melly
    Melly ✭✭

    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

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!