Quick to learn the time and dates functions

Uploaded time: August 17, 2022 Difficulty Beginner

Quick to learn the time and dates functions

Comment
Download exercise file
0
12.3K Views
Key Points Catalogue

The key points of this lesson

In this lesson, we‘ve learned the following key points:

1. How do we express the time and date correctly in the function formula?

Under normal circumstances, you just need to enter the standard date format in the formula, such as 01/01/2022. Don't forget that the date should appear inside quotation marks.

If you want to express a certain month in the formula, you need to satisfy two conditions at the same time. That's because the number of days each month is not fixed and can be 30 or 31 days.

Take March 2022 as an example:

Condition 1: Greater than or equal to March 1, i.e. >= 01/03/2022

Condition 2: Less than or equal to March 31,  i.e.

2. How do we separate the year, month, and day from the date?

The YEAR function can extract the year of the date; The MONTH function can extract the month of the date; The Day function can extract the day of the date.

3. How can we extract the hour, minute, and second of the time separately?

Similarly, the HOUR, MINUTE, and SECOND functions can extract hour, minute, and second of the time.

4. How do we use the DATEDIF function to calculate the employee's length of service?

- The DATEDIF function itself has very simple parameters, DATEDIF (Start_date, End_date, Comparison Unit)

can return the number of days, months, and years between two dates, which is one of the functions that HR must master.

- Besides, there are two functions else that are often used to calculate dates. NETWORKDAYS function can return the number of working days between two dates, and its function syntax is: NETWORKDAYS (Start_date, End_date, Holidays)

The WORKDAY function can return the end date based on the start date and the number of days required. Its function syntax is: WORKDAY(Start_date, Number of days, [Holidays])

PS:Time is by nature numerical and can be calculated as long as it is in a standardized format.

You'll Learn

The basics of WPS Spreadsheet.

The most commonly-used batch processing skills for increasing productivity.

Process large data in WPS Spreadsheet.

Master the popular PivotTables to create dynamic reports.

Nest dynamic formulas, including the IF, VLOOKUP, SUMIF, and MATCH functions to impress your leader.

Acquire skills in improving security, sharing, co-editing, and printing files.

Course Content

Start from beginners, from theory to practice, don't worry about learning

Basics of WPS Spreadsheet

6  lectures

3592  minutes

This chapter covers some basics about WPS Spreadsheet, including the interface and common format types. You will learn how to enter and process data, customize formats, and make a data table starting from 0.

How to make a table efficiently

6  lectures

3664  minutes

Filter, Search, locate and View are all the basics in a table. Do you really know how to use them? Knowing how they work can help you make tables quicker. This chapter covers the usage of "Text to Columns" and "Split and Merge." These two tools will boost your productivity by more than thrice.

Most commonly-used Formula Skills

5  lectures

3471  minutes

80% of beginners think that functions are too much for entry-level. They can't remember the syntaxes, make mistakes all the time, and make tables manually. A few key functions can solve most of the working issues. This chapter covers the statistical functions SUM and SUMIF; logical functions IF ,AND, OR; time function DATEIF and NETWORKDAYS; And the magical power of the VLOOKUP function.

PivotTable

2  lectures

1329  minutes

This chapter covers the basics of PivotTables and PivotCharts. You can immediately apply what you've learned to your work in real cases. You can also learn how to create and beautify the dynamic PivotTables, thus impressing your leader.

Data Visualization

6  lectures

3709  minutes

Charts have advantages in presenting data. This chapter covers the usages of conditional format, column charts, line charts, pie charts, combo charts, and sparklines. You can learn how to pick charts for various situations. There are also useful strategies to beautify your chart and make it more appealing.

Security and Painting

3  lectures

1896  minutes

This chapter focuses on three things: how to protect the security and privacy of sheets, how to share, collaborate and track changes in the table; how to solve typical printing issues at work.

You'll Get

  • 300+ Mins Videos
  • 100+ Real Cases
  • 40+ Practice Files

Comments

Seriously, it really helped me a lot. I used to be overwhelmed by the huge amount of data every day, but the spreadsheet has relieved me of the statistical burden!
WPS Office is completely beyond imagination and now I am highly dependent on it for my work, creating test papers, courseware, and slideshows ...... It covers almost everything.

Catalogue