Date & Time Formulas & Functions in Excel Worksheet and Power Query – 365 MECS 05

Posted on by Annie D

Download Excel file: https://excelisfun.net/files/05-M365ExcelClass.xlsx
Download pdf notes: https://excelisfun.net/files/05-M365ExcelClass.pdf
Course taught by Excel MVP and Highline College Professor, Mike Girvin.
Topics in video:
1. (00:00) Introduction
2. (01:12) Fundamentals of how Date Values work in Excel, Power Query and the Data Model
3. (04:08) How Date Number Formatting Works
4. (05:38) Date Formulas to calculate the difference between two dates
5. (08:08) Count Workdays. NETWORKDAY.INTL function
6. (10:15) Add or subtract days to get a future date. WORKDAY.INTL function
7. (12:48) Month Date calculations such as determining date by adding or subtracting months with EDATE function or determining end of month by using EOMONTH function
8. (15:10) Fundamentals of how Time Values and Time Number Formatting work in Excel, Power Query and the Data Model
9. (19:06) Enter Date-Time Values
10. (21:45) Time formulas to calculate the number of hours worked
11. (27:19) Formulas for Working Night Shift, including Logical Formula, IF function and MOD function
12. (31:28) Evaluate Formula Tool
13. (34:03) Round time values to nearest five minutes. MROUND function
14. (35:52) Create Date Attribute Fields to a table using Worksheet formulas and functions
15. (37:28) Using TEXT function to add Month Name and Day Name Attribute Fields
16. (37:58) Using ROUNDUP function to create Standard Quarter Attribute Field
17. (39:17) Using IF function to create Fiscal Quarter Attribute Field
18. (40:44) Using IF function to create Fiscal Year Attribute Field
19. (42:40) The Importance of Date and Time Attribute Fields
20. (43:09) Create Date Attribute Fields to a table using Power Query formulas and functions. Create Attribute Fields such as Month Name, Fiscal Quarter and Fiscal Year
21. (45:15) if function in Power Query. Learn how to use the Conditional Column feature in Power Query to create Fiscal Quarter and Year Fields
22. (47:18) Use Add Column Merge Columns feature to create Fiscal Period Field.
23. (48:50) Load fact table with Date Attribute Fields to PivotTable Cache.
24. (49:37) Conclusion
25. (50:15) Closing and Video Links

Song in video: Rock Intro 3 by Audionautix is licensed under a Creative Commons Attribution 4.0 license. https://creativecommons.org/licenses/by/4.0/ . Artist: http://audionautix.com/

ExcelIsFunMon, September 5, 2022 8:40pm