Welcome to Excel Jet Consult blog post. In this Excel video tutorial, we learn how to track Forbes Most Powerful Person Birthday Status for 2021 using the new IFS Function. Remember to hit the subscribe and bell icon buttons because there is a lot to come.
Video
Sample File
In the caption below, we have list of Forbes Most Power Person with their Occupation and Date of Birth in columns B and C respectively
Extract Birthday in 2021
In column D, we want to use combination of Date functions to return the Birthday for each of the folks based on their Date of Birth. Therefore, to do that:
- In cell D2, execute the formula: =DATE(YEAR(TODAY()),MONTH([@[Date of Birth]]),DAY([@[Date of Birth]]))
- Click on CTRL + Enter.
The result copied that to all the cells below which is peculiar to working with Tables. See below
Status using IFS Function
Next, in columns E, we want to use the relatively new IFS Function to return the Status for each of the folks such as Celebrated, This Month, Next month and Next Two Months celebrate. Therefore,
- In cell E2, execute the formula: =IFNA(IFS(TODAY()>[@[BirthDay in 2021]],”Celebrated”,MONTH([@[BirthDay in 2021]])=MONTH(TODAY()),”This Month”,MONTH([@[BirthDay in 2021]])=MONTH(TODAY())+1,”Next Month”,MONTH([@[BirthDay in 2021]])=MONTH(TODAY())+2,”Next Two Month”),””)
- Click on CTRL + Enter
See the result in the caption below
Note, the IFS Function is available in Office 2019 and newer versions.
See you in the next tutorial. Cheers