4 out of 5
4
2 reviews on Udemy

Advanced Excel: Date Cleaning, Formulas, Tricks and Calcs

Learn time saving Advanced Excel concepts & tricks on Date Cleaning, Formulas, Tricks and Calcs
Instructor:
Yoda Learning
397 students enrolled
English [Auto-generated]
Learn Advanced tricks on Excel Concept: How Excel reads date, Date formulas & tricks, Pivot Table - Grouping based on Dates Formatting tricks & Paste Special & Auto-fill tricks
Get Downloadable Case Studies + Picture based eBook [mapped to the Lectures]
Get Course Completion Certificate
Learn from the Pro - a Professional Corporate Trainer (PwC, EY, RBS, Tata Group, XLRI etc.)

Before you read what I have to say, see what my students are saying about my courses on Excel:

“I would like to tell you that I am very impressed with your courses so far – I particularly like the short videos that allow me to get to a topic quickly and then move on (your competitors mostly offer 20-30 min videos which is a bit much to keep one’s attention span). The other gem in your training are the shortcuts, I found no other course with the same insights.” by Peter Klugsberger, Australia, ex-McKinsey & Co., served various senior roles (CEO, COO) in various companies

“I thought you knew me – You solved  the exact problem which I face at my office in your course” by Rajesh Dhoot, India, ex-Sr. Manager, Finance and Accounts, Ultratech Cements – An Aditya Birla Group Co.

——————————————————————————————————————-

“Date” data type is crucial for every report as lot of important decision making exercises are based on it. Similarly, The aesthetics and design of every report helps read the report better and faster.

  • Concept: How Excel reads date
  • Date formulas & tricks
  • Pivot Table – Grouping based on Dates
  • Formatting tricks
  • Paste Special & Auto-fill tricks

We have 14 videos of duration 42 mins (in one sitting). Through practical examples, You’ll learn Ninja level application on Advanced Excel: Date Cleaning, Formulas, Tricks and Calcs

——————————————————————————————————————-

Your Question – Why should I take this mini-course on Excel?

Our Response – 

E.g. 

——————————————————————————————————————

Your Question – So will I learn Pivot table, Vlookup, Sort & Filter? 

Our Response – No. This course covers Advanced Excel: Date Cleaning, Formulas, Tricks and Calcs

——————————————————————————————————————

“The courses are really helpful whether in terms of saving time, improving your presentation skills or getting to learn new features. The videos give you the feeling of one to one attention. Moreover, their constant attachment by emailing new tricks is highly appreciating. #thankyouteamyoda” by Arundhati Raychaudhuri, Finance Professional, India

——————————————————————————————————————-

Important information before you join:

  • Once enrolled, you have unlimited, lifetime access to the course!
  • You will have instant and free access to any updates I’ll add to the course.
  • I will give you my full support regarding any issues or suggestions related to the course.
  • Guided practice worksheets included for immediate practice
  • Access to all videos 24 x 7 – learn online from anywhere
  • A 30-day money back guarantee (we want you to be 100% satisfied)

—————————————————————————————————————–

If you have read this far, next action is JOINING this course. Invest 42 mins (in one sitting) for a benefit of lifetime Ninja status on Advanced Excel: Date Cleaning, Formulas, Tricks and Calcs!

Introduction to Course

1
Intro to the course

If you like the course, PLEASE leave a review so other new students can hear about it.

If you are NOT at the 5 star level of happiness with our course, MESSAGE me with any feedback or questions!

I am HAPPY to create new lectures to help you better understand a topic and get you to that 5 STAR HAPPY LEVEL with this course!

Thanks again for joining YodaLearning!

Understanding the Dates in Excel

1
Dates - Important Concepts 1 of 2

Every valid date (i.e. date that can be understood by Excel) is a number

  • 2-Jan-1900 is 2 days away from 31-Dec-1899 and hence, read by Excel as 2.0
  • Use =ISNUMBER() to detect validity of Dates entered i.e. whether the displayed date is a number
2
Dates - Important Concepts 2 of 2
  • Use “Format Cells” or Ctrl + 1 to change the “skin” or the display value of the date
     ○ Use “Comma Style” or “General” to display the number
     ○ Ctrl + Shift + 3 will convert a correct date’s display value to dd-mmm-yy format or 22-Jul-2015.
  • Microsoft OS: Control Panel > Region & Language > Settings – to change the format of the date input accepted by Excel

Date Formulas

1
DAY(), MONTH(), YEAR(), DATE()

Extracting date information through formulas - DAY(), MONTH(), YEAR(), DATE()

  • Compiles the three components – Year, Month, Day in a date value
2
TEXT()

Extracting date information

  • Converts the date into Custom format. E.g. “mmmm-yyyy” will display June-2011
  • Important: Resultant answer value is not a date value but a text value. Used for display purposes and not for subsequent formula computations
3
WEEKDAY(), WORKDAY(), NETWORKDAYS()

Date Formula - WEEKDAY():

  • Returns a value from 1 to 7, representing day of the week
  • E.g. 1=Sunday, 2=Monday, 7= Saturday
  • Used with IF() to write day based logical formula. E.g. =IF(WEEKDAY(A1)=1,”Holiday”,”Office Day”)
  • Scheduled public holidays can also be excluded

Date Formula - WORKDAY():

  • Returns the date before or after a specified number of weekdays (weekends excluded). It excludes start date in computing final answer.
  • E.g. If Cell A1 is 30-Dec-2011, then =WORKDAY(A7,5)-1 will return 5-Jan-2012. 1-Jan-2012 is a Sunday and hence, excluded.
  • Scheduled public holidays can also be excluded
  • Used to calculate deadline/due date calculations

Date Formula - NETWORKDAYS():

  • Returns the number of weekdays (weekends excluded) between two dates.
  • It includes start date in computing final answer.
  • Scheduled public holidays can also be excluded
  • Used to calculate no. of business days between two dates
4
WORKDAY.INTL() for deadline/due date calculations with custom weekends/holidays

WORKDAY.INTL() for deadline/due date calculations w. custom weekends/holidays

  • Returns the date before or after a specified number of weekdays (weekends excluded). It excludes start date in computing final answer
  • Scheduled public holidays can also be excluded
  • Used to calculate deadline/due date calculations and in Project Management

How is it different from =WORKDAY()

  • Allows the user to specify which days are counted as weekends.
  • E.g. 7 = Fri/Sat are weekends as followed by Saudi Arabia
5
NETWORKDAYS.INTL() for no. of business days calculations w. custom weekends/Offs

NETWORKDAYS.INTL() for no. of business days calculations w. custom weekends/holidays

  • Returns the number of weekdays (weekends excluded) between two dates.
  • It includes start date in computing final answer
  • Scheduled public holidays can also be excluded
  • Used to calculate no. of business days between two dates and in Project Management

How is it different from =NETWORKDAYS()

  • Allows the user to specify which days are counted as weekends
  • E.g. 7 = Fri/Sat are weekends as followed by Saudi Arabi
6
TODAY() and NOW() w. Shortcut

Date Formula - TODAY() w. Shortcut

  • Returns the current date as per PC’s system clock
  • Updates every time the file is opened (dynamic)
  • Ctrl + ; and press Enter - for inserting current date (static)

Date Formula - NOW() w. Shortcut

  • Returns the current date and time as per PC’s system clock
  • Updates every time the file is opened (dynamic)
  • Ctrl + Shift + ; and press Enter - for inserting current time (static)
7
EOMONTH() for Financial Modeling, Budgets, Due Dates

Date Formulas - EOMONTH() for Financial Modeling, Budgets, Due Dates

  • Returns the last day of the month before or after a specified number of months.
  • Used for due dates computations such as 5th of next month, end of current month
  • Used for creating timelines in Budget & Forecast models – MoM, QoQ, YoY
8
EDATE() for Financial Modeling, Budgets, Due Dates

Date Formulas - EDATE() for Financial Modeling, Budgets, Due Dates

  • Returns the date that represents the indicated number of months before or after the start date. E.g. 60 days vs. 2 months
  • Used for computing 3 months’ notice period end date, retirement age, probation period, contract deadline, EMI installment due date
9
Pivot Table - Grouping - Dates [Years, Months etc.]
10
Cell drag-n-drop Auto Fill Options - Yr-end, Mth-end, AlphaNumeric, Fill-Justify

Cell drag–n–drop Auto Fill Options

  • Also, refer =EOMONTH() for formula based Fill Months (1), Fill Quarters (3) and Fill Years (12)
  • If the fill–handle doesn’t appear or the mouse cursor isn’t allowing you to draw the contents of a cell, please check if the “Enable fill handle and cell drag–and–drop” setting is turned ON
11
Cell drag-n-drop Auto Fill Options vs. EOMONTH() - End of Year/Quarter/Month

Level Up

1
Level up your skill on MS Excel with advanced learning program
You can view and review the lecture materials indefinitely, like an on-demand channel.
Definitely! If you have an internet connection, courses on Udemy are available on any device at any time. If you don't have an internet connection, some instructors also let their students download course lectures. That's up to the instructor though, so make sure you get on their good side!
4
4 out of 5
2 Ratings

Detailed Rating

Stars 5
1
Stars 4
0
Stars 3
1
Stars 2
0
Stars 1
0
a8e005931e1a965fbd0ef1b4126ad3b1
30-Day Money-Back Guarantee

Includes

1 hours on-demand video
1 article
Full lifetime access
Access on mobile and TV
Certificate of Completion