ivity $3-$ Data Cleaning and Manipulation using DATE functions $($Tutorial$)$

asked in cols $E$ and $x$ 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 E$6$ as the reference, find the YEAR, MONTH, and DAY using cells E$7$:E$9,$ Enter those values below:

E$7$:

E$8$:

E$9$:

Usefut 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$(E6,0).$ The zero tells Excel to stay in the month specified in the E$6$ date. Enter the value in Eii below exactly as it appears:

E$11$:

To find the start of the month it requires a littie 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 what to do to obtain the beginning of the month. You juster $=$ EOMONTH $(E6,-1).$ What did you obtain by using $-1$ instead of $0?$ From there it should be obvious E$12$:

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$:

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 $\frac{4}{19}\frac{?}{19}$ we would go to $\frac{4}{30}\frac{?}{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$:

Oftentimes for caiculations 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

Autosaved at $11$:$17$ AM