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

This video is a comprehensive lesson in data and time formulas and functions in the Excel Worksheet and Power Query.

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

#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

[johnadair]

Just placed my order on Amazon for "Microsoft 365 Excel: The Only App That Matters: Calculations, Analytics, Modeling, Data Analysis and Dashboard Reporting for the New Era of Dynamic Data Driven Decision Making & Insight." Woo Hoo!

[d-lirious]

Thank you so much! I just wanted to learn some basics so I could do a project at home, and you've been so helpful with these videos.

[kiya]

Now this is fast, considering what happened to you last week
Thank you, and Stay strong!

[teoxengineer]

Mike,
Thank you for sharing this beautiful tutorial that no one can obtain such knowledge
Everyone should have this carefully prepared study in their archives.
What I want to learn is, when given a month name, how can we calculate the number of months corresponding to this month name?
For example, in a data with the month "February", how can we convert the name of the month to the number "2"?
Excel can calculate the opposite solution like, =TEXT("2/28/2022", "mmmm") = February
Thank you

[darrylmorgan]

Boom!This Was One Awesome Super Fun Refresher Class Happy Days...Thank You Mike :)

[rajonemanshow]

Thanks, Mike, It's a great one, But I want to check how to get the second Sunday of Every Month on the sequence.

[Excelambda]

Great video!!
Regarding quarters, fiscal quarter/years, seems the perfect scenario for a simple lambda function alternative to PQ, all in one that adds also the versatility, for other countries, to choose a different starting month of fiscal year, like 7 or 10 instead of 4

QTR(ar, [qt], [sm]) where:

ar: dates array, (could be single value, cell reference, column/row vectors or even 2D arrays)
[qt]: quarter type argument, (one of these values "q", "qy", fq", "fy", "fqy" )
[sm]: start month of fiscal year
- if sm is omitted, default value is 4, if we need 7 we input 7

qt quarter type argument values:
- if qt is omitted, default value = "qy" => this format ex: 2022 Q4 (always year first, for sorting versatility)
"q", calendar quarter => format ex: Q4
"qy", calendar quarter and year => format ex: 2022 Q4
"fq", fiscal quarter => format ex: FQ4
"fy", fiscal year => format ex: FY 2023 (fiscal year 2023-24)
"fqy", fiscal quarter and year => format ex: FY 2023 Q4
If none of the above function will return the value for "fqy"
Could have use numeric values instead of text, but with letters is easier to remember

=LAMBDA(ar, [qt], [sm], LET(
s, IF(sm, sm, 4), a, IF(ar="", "", ar),
m, MONTH(a), y, YEAR(a),
q, "Q"&MONTH(m&0), qy, y&" "&q,
fq, "Q"&MONTH(MOD(m-s, 12)+1&0),
fy, "FY "&y+(m>=s)-1,
fqy, fy&" "&fq,
IFERROR(SWITCH(qt, 0, qy, "q", q, "qy", qy, "fq", "F"&fq, "fy", fy, "fqy", fqy), "")
)
)

If dates array has blanks, empty strings, text or errors will be ignored and function will return empty strings instead.

[barttitulaerexcelbart]

Mike, that was comprehensive again! Thumbs up!

[richardhay]

Badness can delay but cannot DEFEAT!!! : ) : )

[kiwikiow]

It's a great video. I am not familiar with time formulas. I'm glad you did this video. Thank you Mike 🙂

[qinyu]

Love this refresh series. Such a systemtical learning path. Have to change my plan for memorial day shopping 🤣🤣🤣

[billrohlfing]

Hands down, you have the best Excel videos out there! Thank you! If you happen upon my comment and feel like responding, I've been experimenting with creating a three column schedule that cascades based on the time entered into the first column and it's duration. First column is time, second is activity, and third column is estimated duration of activity (hh:mm). What's the best way to populate each new activity's start time based on the previous activity's start time plus the previous activity's duration. So if I started at 7:00 AM with a 45 minute task, the next row's time would automatically populate with 7:45 AM and so forth. I don't expect an answer, but thought it couldn't hurt to post. :-) Thanks again for the inspiring videos, Mike!

[HusseinKorish]

Amazing Tutorial as usual ... date and time functions were tricky for me ...Thanks Mike

[aijazali]

Thanks for sharing such a beautiful and comprehensive Tutorial!

[pollora]

thanks Mike, i just got your book. It is amazing .

[chrism]

This was fantastic Mike!! Date and Time can be a little tricky so this was super helpful!

[RogerStocker]

Really an epic Video, could learn a lot again. Go Mike, go! 🏆
Hope you are back to normal again. Stay safe and be strong!

[jerrydellasala]

Amazing series! At 25:17 when I use [Alt]= to Auto Sum, I get "=SUM(H22:H25)" instead of "=SUM(H21:H25)". It ended up that cell H21 was formatted as time AM/PM, but the rest of the spilled cells were formatted as General. Once I changed H21 to general, Auto Sum worked fine.

[elgennacorda]

Thanks for sharing your knowledge, you help a lot of people. Continue sharing your blessings ❤️

[Chamchijjige]

Thank you very much for another good teaching.