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. Date Fundamentals B6s2 and the date in B5 should make sense. Fractional Dates Cells B9:B11 should now show the date and partial date as time of day. Enter the values shown in cells B9:B11 exactly as they appear in the blanks below B9: B10: B11: Built in Excel Functions 1. They do not take arguments; just open and closed parenthesis (). 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 Date Parts E7: E8: Useful relative dates E11: to obtain the beginning of the month. You just need to add one more day to =EOMONTH(E6, 1). Since Excel sees dates and numbers, just +1. E12: E13: 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, E6, E8, and +1, in cell E14 find the first day of the year for the date shown in E6. E14: days that have occurred between the beginning of the year date (which you already found) and the date in E6. Enter the result of your 13 calculation below: I3: problem use a basis of 1. More help with YEARFRAC can be found at this link. To have the result show as a percentage, you may need to format the cell as a percent. In cell I4, find the faction of the year represented between the date in E6 and the beginning of the year (to 1 decimal). Enter the percentage below without the % sign. I4: In cell I5, find the fraction of the year represented between the date in E6 and the beginning of the month (to 1 decimal). Enter the percentage below without the % sign. I5: link. WEEKDAY function inside the TEXT function. formatting instruction to the TEXT function. Enter the day of the week from I7 below (capitalize the first letter). I7: = TEXT (date, " mmmm "). Enter the month name from I10 below (capitalize the first letter). I10: I12: of workdays you specify have happened? Help with WORKDAY function can be found at this link. the WORKDAY function to find the date that is 30 workdays from the date in E6. Ignore any holidays and leave the "Holidays" argument blank. I13: I12: of workdays you specify have happened? Help with WORKDAY function can be found at this link. the WORKDAY function to find the date that is 30 workdays from the date in E6. Ignore any holidays and leave the "Holidays" argument blank. I13: I14: 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 Revenue Pivot Table What is the lowest sum of revenue in the pivot table? (Note: Conditional formatting can help!) What is the highest sum of revenue in the pivot table? (Note: Conditional formatting can help!) Target Pivot Table second time in the Values field of the pivot table. Which month hit the target revenue most frequently? Month: 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. Date Fundamentals B6s2 and the date in B5 should make sense. Fractional Dates Cells B9:B11 should now show the date and partial date as time of day. Enter the values shown in cells B9:B11 exactly as they appear in the blanks below B9: B10: B11: Built in Excel Functions 1. They do not take arguments; just open and closed parenthesis (). 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 Date Parts E7: E8: Useful relative dates E11: to obtain the beginning of the month. You just need to add one more day to =EOMONTH(E6, 1). Since Excel sees dates and numbers, just +1. E12: E13: 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, E6, E8, and +1, in cell E14 find the first day of the year for the date shown in E6. E14: days that have occurred between the beginning of the year date (which you already found) and the date in E6. Enter the result of your 13 calculation below: I3: problem use a basis of 1. More help with YEARFRAC can be found at this link. To have the result show as a percentage, you may need to format the cell as a percent. In cell I4, find the faction of the year represented between the date in E6 and the beginning of the year (to 1 decimal). Enter the percentage below without the % sign. I4: In cell I5, find the fraction of the year represented between the date in E6 and the beginning of the month (to 1 decimal). Enter the percentage below without the % sign. I5: link. WEEKDAY function inside the TEXT function. formatting instruction to the TEXT function. Enter the day of the week from I7 below (capitalize the first letter). I7: = TEXT (date, " mmmm "). Enter the month name from I10 below (capitalize the first letter). I10: I12: of workdays you specify have happened? Help with WORKDAY function can be found at this link. the WORKDAY function to find the date that is 30 workdays from the date in E6. Ignore any holidays and leave the "Holidays" argument blank. I13: I12: of workdays you specify have happened? Help with WORKDAY function can be found at this link. the WORKDAY function to find the date that is 30 workdays from the date in E6. Ignore any holidays and leave the "Holidays" argument blank. I13: I14: 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 Revenue Pivot Table What is the lowest sum of revenue in the pivot table? (Note: Conditional formatting can help!) What is the highest sum of revenue in the pivot table? (Note: Conditional formatting can help!) Target Pivot Table second time in the Values field of the pivot table. Which month hit the target revenue most frequently? Month
