2 vl (Xv &] & _4 sa [mmc E : F | H I a J L M N 1,DATE VALUE VALUE MEASURE VALUE FORMULA MEASURE VALUE FORMULA 2| | Current Date (TODAY) Use E6 "Problem Date" for green cells in col. E & | 3| 9 j Current Time (NOW) Cumulative full days this year 4 548 Year to date fraction (YEARFRAC) 5 3835 Use E6 "Problem Date" for green cells in col. E & | Month to date fraction of year 6 40468 Problem Date 10/8/2019 7| Year (YEAR) Day of the week NAR 8 Date Value __Fractional Date Month (MONTH) TIP: Use custom date format to display 9 40468.25 Day (DAY) as the name of the day (dddd) 10 40468.375 Name of month NIA a 40468.625 End of month 12 Start of month Workdays in current month 13 Start of next month 30 workdays (excluding weekends) from today 14 First day of the year Days between today and 30 workdays from now 15 DATE DATA CLEANUP and MANIPULATION 16 Target 17 FORMULAS for TABLE COLUMNS C through E is Ps as) e)S MONTH DAY OF WEEK alae lse(-) DAY OF WEEK Hit Target? 19 10/1/2019 $3,642 20 10/2/2019 $3,427 2a 10/3/2019 $3,842 22 10/4/2019 $3,525 23 10/5/2019 $3,648 24 10/6/2019 $3,833 25 10/7/2019 $3,091 26 10/8/2019 $2,503 27 10/9/2019 $3,169 28 10/10/2019 $1,158 29 10/11/2019 $2,334 30 10/12/2019 $1,585 31 10/43/2019 $2,763 32 10/14/2019 $1,846 33 10/15/2019 $1,774 34 10/16/2019 $3,852 35 10/17/2019 $2,607 36 10/18/2019 $2,236 37 10/19/2019 $3,029 38 10/20/2019 $2,138 39 10/21/2019 $1,899 40 10/22/2019 $2,908 41 10/23/2019 $2,526 42 10/24/2019 $3,396 43 10/25/2019 $2,614 44 10/26/2019 $3,098 45 10/27/2019 $2,652 46 10/28/2019 $2,154 = @Data Pivot + Workbook Statistics . Give Feedback to Microsoft. = 100% + Targeted Date Functions IMPORTNT NOTE: For the remainder of shaded cells in columns E and I, you must use the date highlighted in cell E6. Whenever something is asked in cols E and I always reference E6. Date Parts Three very helpful functions are YEAR, MONTH, and DAY. As you might expect, each returns a specific part of a date. Using E6 as the reference, find the YEAR, MONTH, and DAY using cells E7:E9. Enter those values below: E7: E8: E9: Useful relative dates Many deadlines in business are at the end of the month. Therefore, Excel has a function called EOQMONTH 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 E6, in E11 insert =EOMONTH(E6,0). The zero tells Excel to stay in the month specified in the E6 date. Enter the value in E11 below exactly as it appears: Ell: 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(E6,0) provides the end of the month for the date of interest. What about end of the month for the prior month? In cell E12 enter =EOMONTH(E6,-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(E6,-1). Since Excel sees dates and numbers, just +1. E12: 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 E11. So, you just need one more day to get the beginning of next month; +1. E13: 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, E6, E8, and +1, in cell E14 find the first day of the year for the date shown in E6. E14: 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 E6, 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 13, find the number of full 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 I3 calculation below: 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 15 and February 15 to be approximately 1/12St of a year (one month), or about 0.8333 (8.33%) of the entire year. In fact, it is 8.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 14, 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. 14: % In cell 15, 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. 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