Question

The following is an Excel question:

You have been hired by Club 2010 to design and implement a worksheet for the "greeter"
at the door to use to determine whether people are old enough to enter. Create a model
that allows the user to enter his/her birth date. The model should compute the user’s age
and in another cell, it should appear either "Welcome" if the person is at least 21 years
old as of today, or "Sorry, you are underage" otherwise. Create at least 2 test cases to
obtain different results.

Show ALL FORMULAS used in getting the answer.

Answer 1

1. Name 3 rows and 1 column like the following

2. Select the cell under "DD-MM-YYYY" and write a random date in dd-mm-yyyy format only, (because excel only accept this format as a date value)

3. select the cell beside "Age" to calculate the age (for the entered date before ). put "=" and then write "IN", a drop-down menu will pop up .From that select "INT" function. Within its braces type "Days", again a drop-down menu will pop up and from that select the "Days360" function. Within this function, for 'start date' select the cell where you have entered the date (under DD-MM-YYYY). For 'end-date', start typing 'Today' ,a drop-down menu will pop up showing a "Today " function, select that. Today is an empty function that calculates the present date, so you do not need to enter any value within its braces.Just simply close the braces and go forward for the next step . for "method" write "TRUE" (in all capital) and close the braces of the function "Days360". Then put "/" and write 360 and close the braces of INT function. Then by pressing enter, it will give you the number of Years from the given date to the present date. As the given date is a birth date, so it shows the age of the person having that birth date.

Actually the function Days 360 is calculating the number of days from that given date to present date.By diving it with 360 (because the function considers a year is consisting of 360 days) we get the number of years between the given date and the present date ,i.e the age of a person if the given date is that person's birth date. But diving a number by 360 can give a decimal value too,but Age can not be like that at all. Usually we consider age as integers ,in years ,that is why we calculated that division (Days360(...)/360) within "INT" function because it will give us the approximated integer value.

4. Select the cell beside "statement" . Put "=" and write "IF", select the "IF" function from the drop-down menu.Within that,for 'logical test' select the cell having the age value,put '<', write 21.For 'value if true' write within double quotation "Sorry, you are under age" and for 'value if false' write welcome within double quotes. Press Enter.

the link of the excel file is given:

https://drive.google.com/file/d/1QH4dsumWUuedEd9WDAUAH7GWaNAGEPEF/view?usp=sharing