Multiple IF formula between two dates

Melly

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"; ""))))))))))))))

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

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.