I need help with a fairly simple Excel assignment. If I am given an Excel file t

Question

I need help with a fairly simple Excel assignment. If I am given an Excel file that gives the arrival dates of customers at a hotel, I need to be able to convert the dates into their respective seasons and days of the week. For example, the arrival dates are given in the first column of the Excel sheet and would be in the form 26-Sep-14. I need to create a column that labels each date under an appropriate season (Winter, Fall, Spring, or Summer), and I also need to create a column that labels each date as a day of the week (Monday, Tuesday, etc.)

In the 26-Sep-14 example, I need to find a way to have the season column recognize it as Fall and the day of the week column to recognize it as Friday. The words must be spelled out completely. The assignment says that we can use "vlookup" in Excel to get that information, but I don't know how to do that. Would I also need to create some sort of table in Excel to link to the formula for seasons, since Fall begins on September 22, Winter begins on December 21, Spring begins on March 20, and Summer begins on June 21?

Please provide formulas that would successfully transform the dates and tell me how you came up with them (I want to understand how to do it). Thank you so much!

Explanation / Answer

Lets you entered the date in Column A starting from A1,

enter the formula 1 in B1, what it does is searches for particular months in corresponding column A cell and finds in which range does it fall. The range is given you can see, and what is the season for that range is specified after the range. The syntax of formula is =LOOKUP(lookup_value,lookup_array)

The formula is,

=LOOKUP(MONTH(A1),{1,3,7,10;"Winter","Spring","Summer","Falls"}) ----------(1)

enter the formula 2 in C1, this searches for the day of the date inputted in corresponding A cell.

=TEXT((A1),"ddd") -----------(2)

Now enter all your dates in column A. And drag down the B1 cell from its right bottom corner to bring up the results of the formula 1 in all the B cells for corresponding A cells.

Do the same for C1 cell, to bring up the results of formula 2.

Thank you.

SPR

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.