Scribd
Upload
4 views

ExcelDate&TimeFunctions

Uploaded by

muraligrishnan
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
4 views

ExcelDate&TimeFunctions

Uploaded by

muraligrishnan
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 3

Date Functions

1. `DATE(year, month, day)`


- Description: Creates a date value from year, month, and day.
- Example: `=DATE(2024, 8, 13)` returns August 13, 2024.

2. `TODAY()`
- Description: Returns the current date.
- Example: `=TODAY()` returns today’s date.

3. `NOW()`
- Description: Returns the current date and time.
- Example: `=NOW()` returns the current date and time.

4. `YEAR(date)`
- Description: Extracts the year from a date.
- Example: `=YEAR(DATE(2024, 8, 13))` returns 2024.

5. `MONTH(date)`
- Description: Extracts the month from a date.
- Example: `=MONTH(DATE(2024, 8, 13))` returns 8.

6. `DAY(date)`
- Description: Extracts the day of the month from a date.
- Example: `=DAY(DATE(2024, 8, 13))` returns 13.

7. `WEEKDAY(date, [return_type])`
- Description: Returns the day of the week as a number (1-7).
- Example: `=WEEKDAY(DATE(2024, 8, 13), 1)` returns 2 (Tuesday).
- Return Types:
- 1: Numbers 1 (Sunday) to 7 (Saturday).
- 2: Numbers 1 (Monday) to 7 (Sunday).
- 3: Numbers 0 (Monday) to 6 (Sunday).

8. `WEEKNUM(date, [return_type])`
- Description: Returns the week number of a date.
- Example: `=WEEKNUM(DATE(2024, 8, 13), 1)` returns 33.

9. `DATEDIF(start_date, end_date, unit)`


- Description: Calculates the difference between two dates in specified units.
- Example: `=DATEDIF(DATE(2020, 1, 1), DATE(2024, 8, 13), "Y")` returns 4
(years).

10. `EOMONTH(start_date, months)`


- Description: Returns the last day of the month that is a specified number of
months before or after a specified date.
- Example: `=EOMONTH(DATE(2024, 8, 13), 1)` returns September 30, 2024.

11. `EDATE(start_date, months)`


- Description: Returns the date that is a specified number of months before
or after a specified date.
- Example: `=EDATE(DATE(2024, 8, 13), 6)` returns February 13, 2025.

12. `DATEVALUE(date_text)`
- Description: Converts a date in text format to a serial number.
- Example: `=DATEVALUE("2024-08-13")` returns the serial number
corresponding to August 13, 2024.

Time Functions

1. `TIME(hour, minute, second)`


- Description: Creates a time value from hours, minutes, and seconds.
- Example: `=TIME(14, 30, 0)` returns 2:30 PM.

2. `HOUR(time)`
- Description: Extracts the hour from a time value.
- Example: `=HOUR(TIME(14, 30, 0))` returns 14.

3. `MINUTE(time)`
- Description: Extracts the minute from a time value.
- Example: `=MINUTE(TIME(14, 30, 0))` returns 30.

4. `SECOND(time)`
- Description: Extracts the second from a time value.
- Example: `=SECOND(TIME(14, 30, 15))` returns 15.

5. `TIMEVALUE(time_text)`
- Description: Converts a time in text format to a serial number.
- Example: `=TIMEVALUE("14:30:00")` returns the serial number
corresponding to 2:30 PM.
Date and Time Calculations

1. `YEARFRAC(start_date, end_date, [basis])`


- Description: Returns the year fraction between two dates.
- Example: `=YEARFRAC(DATE(2020, 1, 1), DATE(2024, 8, 13))` returns 4.64.

2. `NETWORKDAYS(start_date, end_date, [weekend], [holidays])`


- Description: Returns the number of working days between two dates.
- Example: `=NETWORKDAYS(DATE(2024, 8, 1), DATE(2024, 8, 13))` returns 9
(excluding weekends).

3. `WORKDAY(start_date, days, [weekend], [holidays])`


- Description: Returns the date after a specified number of working days.
- Example: `=WORKDAY(DATE(2024, 8, 1), 10)` returns August 15, 2024.

4. `WORKDAY.INTL(start_date, days, [weekend], [holidays])`


- Description: Returns the date after a specified number of working days, with
custom weekend days.
- Example: `=WORKDAY.INTL(DATE(2024, 8, 1), 10, "0000011")` returns August
15, 2024, assuming weekends are Saturday and Sunday.

Additional Functions

1. `TEXT(value, format_text)`
- Description: Converts a value to text in a specified number format.
- Example: `=TEXT(DATE(2024, 8, 13), "dddd, mmmm d, yyyy")` returns
"Tuesday, August 13, 2024".

2. `CHOOSE(index_num, value1, [value2], ...)`


- Description: Selects a value from a list of values based on an index number.
- Example: `=CHOOSE(WEEKDAY(DATE(2024, 8, 13)), "Sun", "Mon", "Tue",
"Wed", "Thu", "Fri", "Sat")` returns "Tue".

You might also like