EXCEL- Date and Time Functions

1. EXCEL stores DATE as numbers. This allows dates in two columns to be subtracted.
2. The Excel DATE function creates date from individual year, month and day.

=DATE (year, month, day)

3. The Excel YEAR function returns the year component of date in 4 digit year.

=YEAR(date)

4. The Excel MONTH function returns the month component of date in number, 1 (JANUARY)…12 (DECEMBER)

=MONTH(date)

5. The Excel DAY function returns the day component of date in number,

1 to 31.

=DAY(date)

6. The TIME function allows you to create time with individual hour, minutes, and seconds components.

=TIME(hour, minutes, seconds)

7. The Excel HOUR function returns the hour component of the time as a number between 0 and 23.

=HOUR(serial_number)

8. The Excel MINUTE function returns the minute component of the time between 0–59.

=MINUTE(serial_number)

9. The Excel SECOND function returns the second component of the time between 0–59.

=SECOND(serial_number)

serial_number — A valid time format or date format that Excel recognizes.

10. The Excel TODAY function returns today’s date and is updated when you open or change the worksheet.

=Today()

11. The Excel NOW function returns the current date and time and is updated when you change or open the worksheet.

=NOW()

12. The DATEDIF function returns the difference between two date values in years, months or days.

=DATEDIF(start_date, end_date, unit)

unit — years (“y”) , months(“m”) or days (“d”)

13. The WEEKDAY function takes a date and returns a number between 1 to 7 representing the day of the week.

1-Sunday ….7-Saturday

=WEEKDAY(serial_number, [return_type])

By default, weekday returns 1 for Sunday and 7 for Saturday.

15. The NETWORKDAYS calculates the number of working days between the two dates. It excludes weekends and optionally can exclude holidays.

=NETWORKDAYS(start_date, end_date, [holidays])

[holidays ] — optional, a list of non-work days as dates.

16. The WORKDAY functions figures out a date that represents the nearest working days in the past or future.

By default, WORKDAY will exclude the weekends.

WORKDAY can be used to calculate ship dates, due dates, delivery dates, etc that should exclude the non-working days.

=WORKDAY(start_date, days, [holidays])

17. The EDATE function shift date n months in future or past.

=EDATE(start_date, months)

=EDATE(1-Feb-2019, 2) — 1-Apr-2019 ie 2 month later.

=EDATE(1-Feb-2019, -1) — 1-Jan-2019 ie 1 month earlier.

18. The EOMONTH function returns the last day of the month, n months in the past or future.

=EOMONTH(1-Feb-2019, 2) — 30-Apr-2019 ie Last day 2 months later.

=EOMONTH(1-Feb-2019, -1) — 31-Jan-2019 ie last day 1 month earlier.

19. The EOMONTH returns the last day of the month, so to get the first day of the current month,

=EOMONTH(TODAY(), -1) + 1 — This will give first day of the current month.

20. Note that for months, use a positive number for future dates and a negative number for dates in the past.