excelisfun | Date & Time Formulas & Functions in Excel Worksheet and Power Query - 365 MECS 05 @excelisfun | Uploaded 2 years ago | Updated 1 day ago
Download Excel file: https://excelisfun.net/files/05-M365ExcelClass.xlsx
Download pdf notes: https://excelisfun.net/files/05-M365ExcelClass.pdf
This video is a comprehensive lesson in data and time formulas and functions in the Excel Worksheet and Power Query.
This full free Microsoft 365 Excel & Power BI class is taught by Excel MVP and Highline College Professor and can be found here: https://www.youtube.com/playlist?list=PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW
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/
#MikeGirvin
#excelisfun
#MikeexcelisfunGirvin
#Microsoft365Excel
#SerialNumberDates
#SerialNumberTime
#ExcelDateValues
#ExcelTimeValues
#NETWORKDAY.INTLfunction
#WORKDAY.INTLfunction
#EOMONTHfunction
#EDATEfunction
#TEXTfunction
#ROUNDUPfunction
#IFfunction
#FiscalQuarter
#FiscalYear
#PowerQueryformulasandfunctions
#ConditionalColumnfeature
#AddColumnMergeColumns
#PivotTableCache
#LoadPowerQuerytoPivotTable
#AvoidextracolumnsinFactTable
#date
#FreeClass
#FreePowerBIClass
#time
#excel
#timeformulas
#dateformula
#excelformulas
#excelfunctions
#powerbi
#powerquery
Download Excel file: https://excelisfun.net/files/05-M365ExcelClass.xlsx
Download pdf notes: https://excelisfun.net/files/05-M365ExcelClass.pdf
This video is a comprehensive lesson in data and time formulas and functions in the Excel Worksheet and Power Query.
This full free Microsoft 365 Excel & Power BI class is taught by Excel MVP and Highline College Professor and can be found here: https://www.youtube.com/playlist?list=PLrRPvpgDmw0nre_bTeBfJWjrnixKoyNtW
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/
#MikeGirvin
#excelisfun
#MikeexcelisfunGirvin
#Microsoft365Excel
#SerialNumberDates
#SerialNumberTime
#ExcelDateValues
#ExcelTimeValues
#NETWORKDAY.INTLfunction
#WORKDAY.INTLfunction
#EOMONTHfunction
#EDATEfunction
#TEXTfunction
#ROUNDUPfunction
#IFfunction
#FiscalQuarter
#FiscalYear
#PowerQueryformulasandfunctions
#ConditionalColumnfeature
#AddColumnMergeColumns
#PivotTableCache
#LoadPowerQuerytoPivotTable
#AvoidextracolumnsinFactTable
#date
#FreeClass
#FreePowerBIClass
#time
#excel
#timeformulas
#dateformula
#excelformulas
#excelfunctions
#powerbi
#powerquery
