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. <=31/03/2022
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.