In this lesson, we will explore the Excel functions that deal with time and dates. These functions are essential for working with deadlines, day calculations, hours, minutes, and much more. We will cover functions such as TODAY, NOW, DAYS, DATE, HOUR, MINUTE, SECOND, and others. Each function will be explained with practical examples to make understanding easier.The TODAY functionThe TODAY function returns the current date. It is useful for creating spreadsheets that always show the current date without needing manual updates.Syntax:=TODAY()Example:If today is 10/10/2023, the formula =TODAY() will return 10/10/2023.The NOW functionThe NOW function returns the current date and time. It is useful for recording the exact moment when an action was performed.Syntax:=NOW()Example:If today is 10/10/2023 and the time is 14:30, the formula =NOW() will return 10/10/2023 14:30.The DATE functionThe DATE function creates a date from year, month, and day values.Syntax:=DATE(year; month; day)Example:If you have the year in A1 (2023), the month in B1 (10), and the day in C1 (15), the formula =DATE(A1; B1; C1) will return 15/10/2023.The HOUR functionThe HOUR function extracts the hour from a time value.Syntax:=HOUR(time_value)Example:If you have the value 14:30:45 in cell A1, the formula =HOUR(A1) will return 14.The MINUTE functionThe MINUTE function extracts the minutes from a time value.Syntax:=MINUTE(time_value)Example:If you have the value 14:30:45 in cell A1, the formula =MINUTE(A1) will return 30.The SECOND functionThe SECOND function extracts the seconds from a time value.Syntax:=SECOND(time_value)Example:If you have the value 14:30:45 in cell A1, the formula =SECOND(A1) will return 45.The DAY functionThe DAY function extracts the day from a date.Syntax:=DAY(date)Example:If you have the date 15/10/2023 in cell A1, the formula =DAY(A1) will return 15.The MONTH functionThe MONTH function extracts the month from a date.Syntax:=MONTH(date)Example:If you have the date 15/10/2023 in cell A1, the formula =MONTH(A1) will return 10.The YEAR functionThe YEAR function extracts the year from a date.Syntax:=YEAR(date)Example:If you have the date 15/10/2023 in cell A1, the formula =YEAR(A1) will return 2023.The WEEKDAY functionThe WEEKDAY function returns the day of the week corresponding to a date, where 1 = Sunday and 7 = Saturday.Syntax:=WEEKDAY(date; [return_type])Example:If you have the date 15/10/2023 in cell A1, the formula =WEEKDAY(A1; 1) will return 1 (Sunday).The DAYS or DATEDIF functionThe DAYS function calculates the number of days between two dates.Syntax:=DAYS(end_date; start_date)Example:If you have the start date in A1 (01/01/2023) and the end date in B1 (10/10/2023), the formula =DAYS(B1; A1) will return 282.In Google Sheets, you will have to use DATEDIF:=DATEDIF(start_date; end_date; unit)For example, if you have the start date in A1 (01/01/2023) and the end date in B1 (10/10/2023) and want to know how many days there are between them, you will do the following:=DATEDIF("01/01/2023"; "10/10/2023"; "D")The NETWORKDAYS functionThe NETWORKDAYS function calculates the number of business days between two dates, excluding weekends and holidays.Syntax:=NETWORKDAYS(start_date; end_date; [holidays])Example:If you have the start date in A1 (01/10/2023) and the end date in B1 (15/10/2023), the formula =NETWORKDAYS(A1; B1) will return 11 business days (considering only Saturdays and Sundays as non-working days).The EDATE functionThe EDATE function returns a date that is a specific number of months before or after a start date.Syntax:=EDATE(start_date; months)start_date: The reference date.months: The number of months you want to add (positive) or subtract (negative) from the start date.Suppose you have the date 15/10/2023 in cell A1 and want to add 3 months to that date. =EDATE(A1; 3) would result in 15/01/2024Practical exercises on time and date functions1. Determine the day of the week for the date 15/10/2023 in cell A1, where 1 = Sunday and 7 = Saturday.2. Calculate the number of business days between 01/10/2023 (cell A1) and 15/10/2023 (cell B1), considering only Saturdays and Sundays as non-working days.3. Given the table below, calculate the number of business days between the start and end dates, excluding the listed holidays.StartEndHolidays01/10/202315/10/202312/10/202301/11/202315/11/202302/11/202301/12/202315/12/202325/12/20234. Given the table below, calculate the age of each person based on their date of birth and the current date (use the TODAY function).NameDate of BirthJoão15/10/1990Maria20/05/1985Pedro01/12/20005. Given the table below, calculate the expiration date of each contract by adding 12 months to the start date.ContractStart DateContract 115/10/2023Contract 220/05/2023Contract 301/12/20236. Given the table below, calculate the total hours worked by each employee, considering clock-in and clock-out times.NameClock-inClock-outJoão08:0017:00Maria09:0018:30Pedro07:3016:457. Given the table below, calculate each employee’s lateness, considering that the clock-in time should be 08:00.NameClock-inJoão08:15Maria07:45Pedro08:308. Given the table below, calculate each person's age in years, months, and days based on their date of birth and the current date.NameDate of BirthJoão15/10/1990Maria20/05/1985Pedro01/12/20009. Given the table below, calculate how many days are left until each person’s next birthday.NameDate of BirthJoão15/10/1990Maria20/05/1985Pedro01/12/2000
로그인하고 대화에 참여
— 코멘트 0
, 반응 1
첫 댓글을 남겨보세요