Question $10/10$ Submit Video Question Content Area Introduction When conducting analysis in Excel for business related problems, you will likely encounter the data type of "date" quite often. Many elements of business are tied to dates. As such, Excel has numerous functions and techniques that allow you to manipulate dates to gain the insights you need. This tutorial will help you learn more about date functions in Excel and the spreadsheet below will be your working document. Go ahead and click on the link to navigate to the spreadsheet. The first thing to understand about dates in Excel is that Excel sees every date as a number. Fundamentally, Excel assigns the number $"1"$ to the date January $1,1900(1/1/1900).$ Oftentimes you will need to change the formatting of a cell to the date format so that it displays correctly, but underneath every date is a number. Let's practice and see how this works. The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the questions below. NOTE: Do not copy $/$ paste dates or times directly from Excel into the fields below as errors may occur. Please type in date and time answers as text strings as they appear in Excel using your keyboard. Open spreadsheet Date Fundamentals In cells A$2$:A$6$ you will find numbers. We would like to change those numbers into actual dates using cells B$2$:B$6.$ To accomplish this, enter $=$A$2$ into cell B$2.$ That will essentially copy A$2$ into B$2$ using a formula. Next drag down B$2$ to B$6$ to fill down. Finally, select cells B$2$:B$6,$ navigate to the Home tab, locate number formatting, and then use the down arrow to select Short Date. Now, cells B$2$:B$6$ should be proper dates. Enter the date for B$6$ exactly as it appears in the blank below: B$6$: fill in the blank $2$ Notice the pattern. Look at the dates in B$2$ and B$3$ and the corresponding numbers in A$2$ and A$3.$ The date in B$3$ is the $10$th of the month. Notice the date in B$5$ and the number in A$5.$ Knowing that a year is approximately $365$ days, the number in A$5$ and the date in B$5$ should make sense. Fractional Dates Excel can also accept fractional dates. In cells A$9$:A$11$ you will see numbers that have a decimal component. The decimal represents part of a day. For example, a number ending in $0\mathrm{.}5$ will be halfway through the day or $12$:$00$ PM $/$ noon. Repeat the same process as above to populate cells B$9$:B$11$ with the same numbers as A$9$:A$11.$ Formatting B$9$:B$11$ will be a little different. To get Excel to show the partial date in cells B$9$:B$11$ highlight those cells, navigate to the Home tab, locate number formatting, and then use the down arrow to select More Number Formats. This opens a dialog box where you can apply custom formats. Under Date, find a format that also shows the time, e$.$g$.,$ if A$9$ contains $40368\mathrm{.}25$: Cells B$9$:B$11$ should now show the date and partial date as time of day. Enter the values shown in cells B$9$:B$11$ exactly as they appear in the blanks below: B$9$: fill in the blank $3$ B$10$: fill in the blank $4$ B$11$: fill in the blank $5$ Built in Excel Functions Two Excel date functions that are good to know are TODAY$()$ and NOW$().$ In cells E$2$ and E$3$ practice those two functions. Three important points about TODAY$()$ and NOW$().$ They do not take arguments; just open and closed parenthesis $().$ They are both "live" meaning that any time you do something in your spreadsheet they will update and change in real time. There are ways to stop this behavior but this is the default. They are both dependent on the machine you are working on to obtain their value. IMPORTANT: If you are working on a server located elsewhere or in the cloud, they may return a value you do not expect. Remember, it is based on the machine running Excel. That could be on your laptop or in the case of the cloud, halfway around the world. So just be mindful. Targeted Date Functions IMPORTNT NOTE: For the remainder of shaded cells in columns E and I, you must use the date highlighted in cell E$6.$ Whenever something is asked in cols E and I always reference E$6.$ Date Parts Three very helpful functions are YEAR, MONTH, and DAY. As you might expect, each returns a specific part of a date. Using E$6$ as the reference, find the YEAR, MONTH, and DAY using cells E$7$:E$9.$ Enter those values below: E$7$: fill in the blank $6$ E$8$: fill in the blank $7$ E$9$: fill in the blank $8$ Useful relative dates Many deadlines in business are at the end of the month. Therefore, Excel has a function called EOMONTH that can help you find that automatically. Help with EOMONTH can be found at this link. To find the end of the month for the date in E$6,$ in E$11$ insert $=$EOMONTH$($E$6,0).$ The zero tells Excel to stay in the month specified in the E$6$ date. Enter the value in E$11$ below exactly as it appears: E$11$: fill in the blank $9$ To find the start of the month it requires a little more work. No function for "beginning of the month" exists in Excel. So$,$ to find the beginning of the month, we must go back to end of the previous month and add one day. Remember that $=$EOMONTH$($E$6,0)$ provides the end of the month for the date of interest. What about end of the month for the prior month? In cell E$12$ enter $=$EOMONTH$($E$6,-1).$ What did you obtain by using $-1$ instead of $0?$ From there it should be obvious what to do to obtain the beginning of the month. You just need to add one more day to $=$EOMONTH$($E$6,-1).$ Since Excel sees dates and numbers, just $+1.$ E$12$: fill in the blank $10$ What about the start of the next month? The start of the next month is just the end of the month plus one day. You already learned how the find the end of the month in E$11.$ So$,$ you just need one more day to get the beginning of next month; $+1.$ E$13$: fill in the blank $11$ Next, what about the first day of the year? To obtain that, we go to the end of the current month, subtract $/$ go backwards the number of months of the current month. For example, if it was $4/19/19$ we would go to $4/30/19($EOMONTH$)$ then back up four months. That would take us to the end of the previous year. Then add one day to get to the beginning of the current year. Using EOMONTH, E$6,$ E$8,$ and $+1,$ in cell E$14$ find the first day of the year for the date shown in E$6.$ E$14$: fill in the blank $12$ Oftentimes for calculations such as average daily revenue, we need to keep track of how many days have occurred so far in a given year. Lucky, since Excel treats dates fundamentally as numbers, dates can be added and subtracted. So$,$ to find the number of days between the beginning of the year and the date in E$6,$ you can use simple subtraction. Reminder: Excel sees a date as a number beginning with $1$ on $1/1/1900$ so it's just later date minus earlier date. In cell I$3,$ find the number of full days that have occurred between the beginning of the year date $($which you already found$)$ and the date in E$6.$ Enter the result of your I$3$ calculation below: I$3$: fill in the blank $13$ While the cumulative number of days between two dates can be very useful, sometimes we would like to convert the number of days into a fraction of a year. The function to do that is YEARFRAC. It asks for a start date, end date, and a basis. For this problem use a basis of $1.$ More help with YEARFRAC can be found at this link. For example, you would expect the fraction of a year between January $1$st and February $1$st to be approximately $1/12$st of a year $($one month$),$ or about $0\mathrm{.}8333(8\mathrm{.}33\%)$ of the entire year. In fact, it is $8\mathrm{.}49\%$; $=$YEARFRAC$(1/1/2019,2/1/2019,1).$ Note: To have the result show as a percentage, you may need to format the cell as a percent. In cell I$4,$ find the faction of the year represented between the date in E$6$ and the beginning of the year $($to $1$ decimal$).$ Enter the percentage below without the $\%$ sign. I$4$: fill in the blank $14\%$ In cell I$5,$ find the fraction of the year represented between the date in E$6$ and the beginning of the month $($to $1$ decimal$).$ Enter the percentage below without the $\%$ sign. I$5$: fill in the blank $15\%$ While we have been learning about dates up to this point, nothing has shown us what day of the week a given date represents. Luckily, Excel has the WEEKDAY function to help find the name of the day. More help with WEEKDAY can be found at this link. WEEKDAY requires a date and a return type which will almost always be $1.$ So $=$WEEKDAY$($date$,1).$ The function returns a number such as $1$ for Sunday, $2$ for Monday, etc. but not the actual day name. To obtain the day name, you will need to use the WEEKDAY function inside the TEXT function. In cell I$7,$ use the function $=$TEXT$($WEEKDAY$($date$,1),"$dddd$")$ replacing date with E$6.$ What this does is find the weekday number and then uses the TEXT function to convert it to day of week name, which is what $"$dddd$"$ represents. The $"$dddd$"$ is a formatting instruction to the TEXT function. Enter the day of the week from I$7$ below $($capitalize the first letter$).$ I$7$: fill in the blank $16$ For the name of the month in cell I$10,$ perform a similar process as you did for the day of the week, but instead of $"$dddd$"$ use $"$mmmm$"$ which as you might guess, tells the text function to format as month. In this case you do not need WEEKDAY, just $=$TEXT$($date$,"$mmmm$").$ Enter the month name from I$10$ below $($capitalize the first letter$).$ I$10$: fill in the blank $17$ For various reasons, it is often helpful to know the number of actual workdays over a given period. Excel has the NETWORKDAYS function to calculate workdays. The function just takes a beginning date and an end date. For this question, use the beginning of the month and end of month in column E and place the result in I$12.$ How many workdays are in the current month? Ignore any holidays and leave the "Holidays" argument blank. I$12$: fill in the blank $18$ The WORKDAY function is similar to NETWORKDAYS, but it takes a date, the number of days ahead you would like to look, and then returns the date that falls on that many workdays in the future. Put another way, at what future date will the number of workdays you specify have happened? Help with WORKDAY function can be found at this link. Remember that workdays are different than calendar days in most cases. It's the number of nonweekend and nonholiday days before or after start$\_$date. A positive value for days yields a future date; a negative value yields a past date. In cell I$13,$ use the WORKDAY function to find the date that is $30$ workdays from the date in E$6.$ Ignore any holidays and leave the "Holidays" argument blank. I$13$: fill in the blank $19$ How many calendar days will pass before achieving a certain number of workdays? Using WORKDAY and a new function called DATEDIF we can easily find the answer; $=$DATEDIF$($start$\_$date, end$\_$date, $"$D$").$ In this case the $"$D$"$ stands for days. Find the number of days it will take to get in $30$ workdays from the date in E$6.$ You have everything you need already for the DATEDIF function. In cell I$14,$ find the number of days it will take to get in $30$ workdays from the date E$6.$ Place your result below. I$14$: fill in the blank $20$ Data Table Oftentimes you will receive data that is overly general. Notice in the data table that begins in row $18,$ daily revenue is provided. However, your analysis calls for monthly and daily aggregation of the revenue data. In columns C and D of the data table, use the techniques you learned above to populate each row with the month and day of the week based on the date in column A$.$ In column E of the data table, note the target dollar value in cell E$16.$ In column E of the data table, create an IF formula that will display TARGET if the revenue in column B is greater than or equal to the target value in E$16,$ or MISS otherwise. Revenue Pivot Table In cell A$2$ in Pivot worksheet, create a pivot table based on the data table, that aggregates revenue by placing month in the columns and weekdays in the rows. Make sure Vales in the Pivot Table task pane is set to Sum of Revenue. What is the lowest sum of revenue in the pivot table? $($Note: Conditional formatting can help!$)$ Lowest revenue Month Day $fill in the blank $21$ What is the highest sum of revenue in the pivot table? $($Note: Conditional formatting can help!$)$ Highest revenue Month Day $fill in the blank $24$ Target Pivot Table Finally, it is important to know which months hit the target most often. In cell A$13$ in Pivot worksheet, create a second pivot table where months are the columns and hit target are the rows. Next to get the counts of TARGET and MISS, use hit target a second time in the Values field of the pivot table. Which month hit the target revenue most frequently? Month: