Can someone help me with my formula, or suggest an easier way? I'm trying to get all of the "Period End" dates to fall within a specific "Effective Date" using a formula (=IF(LEFT(H5,1)>=7,"08/17/2016","01/17/2017").
For Period End dates between July and December, the Effective Date should be 08/17/2016. For those that fall between January and June, the Effective Date should be 01/17/2017. I think there is something wrong with the formatting of the dates, because all of the formula results are "True" and giving me Effective Dates of 08/17/2017, even for those whose first digit is less than 7. The current formatting is General.
<colgroup><col><col><col></colgroup><tbody>
</tbody>
I thank you in advance for your help.
B.J.
For Period End dates between July and December, the Effective Date should be 08/17/2016. For those that fall between January and June, the Effective Date should be 01/17/2017. I think there is something wrong with the formatting of the dates, because all of the formula results are "True" and giving me Effective Dates of 08/17/2017, even for those whose first digit is less than 7. The current formatting is General.
Effective Date | Employment Status | Period End |
08/17/2016 | Active | 09/15/2016 |
08/17/2016 | Active | 09/30/2016 |
08/17/2016 | Active | 10/15/2016 |
08/17/2016 | Active | 10/31/2016 |
08/17/2016 | Active | 11/15/2016 |
08/17/2016 | Active | 11/30/2016 |
08/17/2016 | Active | 01/31/2017 |
08/17/2016 | Active | 02/15/2017 |
08/17/2016 | Active | 02/28/2017 |
08/17/2016 | Active | 03/15/2017 |
<colgroup><col><col><col></colgroup><tbody>
</tbody>
I thank you in advance for your help.
B.J.