Advanced Excel | Full Free Course | Lesson #4 – Date & Time Functions Comprehensive Guide

Welcome to Lesson 4 of our Ultimate Advanced Excel Free Course available exclusively on YouTube! In this comprehensive guide, we dive deep into the world of DATE and TIME functions in Excel, exploring 25 essential functions that will transform the way you manage and manipulate dates and times in your spreadsheets.

In this video, we cover:
1. Extract the Day, Month & Year from a date
2. Extract the Hour, Minute & Second from a time
3. Insert the Current Date & Time
4. Calculate percentage of Year Left & Year Passed
5. Combine Functions to insert the current date, current time and percentage of current year passed in a single text string which remains dynamic
6. Convert date or time stored as text to the proper format
7. Calculate maturity date for investments
8. Add/subtract months from a date
9. Calculate last day of present, next and previous month
10. Calculate percentage of current Month Passed keeping it dynamic
11. Calculate difference in days between Dates
12. Calculate total workdays between project start date and end date including/excluding holidays
13. Find week number and week day of a date
14. Calculate Project Completion Date
15. Calculate Total Hours Worked Excluding Break time
16. Calculate The Difference Between Two Dates in Various Units used the hidden DATEDIF function
17. Calculate Age of a Person in Years & Months
18. Calculate Days left for Birthday
19. Calculate Detailed Age using INT & MOD
20. Extract Date from Text String using a combination of functions

Purpose of the Video
The purpose of this video tutorial is to provide a comprehensive guide on using date and time functions in Excel. Whether you are a beginner or an advanced user, this tutorial will equip you with the skills to efficiently manage and manipulate date and time data in Excel. By the end of this video, you will have a strong understanding of how to extract, insert, calculate, and convert date and time information, as well as how to perform various calculations that are crucial for project management, personal planning, and data analysis.

Key Takeaways:
 Understand and use the DAY, MONTH, and YEAR functions to extract components from a date.
 Apply the HOUR, MINUTE, and SECOND functions to break down a time value.
 Use the TODAY and NOW functions for dynamic current date and time.
 Learn formulas to determine the percentage of the year passed and remaining.
 Combine various functions to create a dynamic text string with date, time, and year percentage.
 Add months or years to a date to calculate the maturity date for investments.
 Use the EDATE function to add or subtract months from a date.
 Apply the EOMONTH function to find the last day of the current, next, and previous months.
 Formulate dynamic calculations for the percentage of the current month passed.
 Calculate the difference in days between two dates using DATEDIF.
 Use the NETWORKDAYS function to find the number of workdays between two dates, including or excluding holidays.
 Utilize the WEEKNUM and WEEKDAY functions to determine the week number and weekday of a date.
 Calculate a project’s completion date by adding days to the start date.
 Use the DATEDIF function to find the difference between two dates in various units.
 Formulate the calculation of a person’s age in both years and months.
 Calculate the number of days remaining until the next birthday.
 Calculate detailed age in years, months, and days.

Timestamps:
00:00 Intro
00:51 Extract Day, Month & Year
01:44 Extract the Date
02:30 Extract Hour, Minute & Second
03:12 Extract the Time
03:47 Insert Current Date & Time
05:13 Calculate percentage of Year Left/ Passed
06:58 Complex Example # 1: Combine Functions Together
14:08 Conversion Functions: DATEVALUE & TIMEVALUE
17:15 Date Arithmetic Functions: EDATE & EOMONTH
22:15 Complex Example # 2: Calculate % Month Passed
28:08 Calculate Days Between Dates
31:28 Workday Functions: NETWORKDAYS & NETWORKDAYS.INTL
36:33 Week Functions: WEEKNUM, ISOWEEKNUM & WEEKDAY
40:16 Planning Functions: WORKDAY & WORKDAY.INTL
44:13 Calculate Total Hours Worked Excluding Break time
46:20 Calculate Project Completion Date
48:08 DATEDIF function
53:14 Complex Example # 3: Calculate Age of a Person & Days Left for Next Birthday
58:08 Complex Example # 4: Calculate Detailed Age using INT & MOD
01:08:40 Complex Example # 5: Extract Date from Text String

🔔 Subscribe for more Excel tutorials and hit the notification bell to stay updated with our latest videos!

#ExcelDateFunctions #ExcelTimeFunctions #ExcelTutorial #ExcelTips #DateExtraction #TimeExtraction #DynamicDateAndTime #CalculateYearPercentage #EDATEFunction #EOMONTHFunction #DATEDIFFunction #NETWORKDAYSFunction #WEEKNUMFunction #WEEKDAYFunction #ProjectManagementInExcel #AgeCalculation #BirthdayCountdown #DetailedAgeCalculation #TextToDateConversion #MaturityDateCalculation #ExcelEfficiency #ExcelForBusiness #ExcelForProjects

[goBeyondBasics]

Timestamps:
00:00 Intro
00:51 Extract Day, Month & Year
01:44 Extract the Date
02:30 Extract Hour, Minute & Second
03:12 Extract the Time
03:47 Insert Current Date & Time
05:13 Calculate percentage of Year Left/ Passed
06:58 Complex Example # 1: Combine Functions Together
14:08 Conversion Functions: DATEVALUE & TIMEVALUE
17:15 Date Arithmetic Functions: EDATE & EOMONTH
22:15 Complex Example # 2: Calculate % Month Passed
28:08 Calculate Days Between Dates
31:28 Workday Functions: NETWORKDAYS & NETWORKDAYS.INTL
36:33 Week Functions: WEEKNUM, ISOWEEKNUM & WEEKDAY
40:16 Planning Functions: WORKDAY & WORKDAY.INTL
44:13 Calculate Total Hours Worked Excluding Break time
46:20 Calculate Project Completion Date
48:08 DATEDIF function
53:14 Complex Example # 3: Calculate Age of a Person
58:08 Complex Example # 4: Calculate Detailed Age using INT & MOD
01:08:40 Complex Example # 5: Extract Date from Text String

[AmanSingh-llqh]

Thank you for creating such an informative and skill enhancing video on the use of date and time function in Excel. It was explained a great depth and has really helped me improve my Excel skills. Looking forward to more content like this in the future