top of page
Writer's pictureThe Excellerator

Let's Make a Date!

Updated: Apr 20, 2023

Meet Microsoft Excel's 30+Date and Time functions.

Excel calendar

DATE(year, month, day)

Date based on the provided year, month, and day.

Example: =DATE(2023, 1, 15) returns 15/01/2023


DATE(year, month, day)

Date specified by a particular year, month, and day.

Example: =DATE(2023, 01, 15) returns 15/01/2023


Tip: Use four digits for the year argument to prevent unwanted results. For example, "07" could mean "1907" or "2007." Four digit years prevent confusion.
 

Our examples are using the format DD/MM/YYYY

 

DATEDIF(start_date, end_date, unit)

Number of full units of a given type between two dates, with a fraction representing the part of the unit at the end date.

Example: =DATEDIF("01/01/2023","15/01/2023","D") returns 14


DATEDIF(start_date, end_date, unit)

Number of units of time between two dates.

Example: =DATEDIF("01/01/2023","15/01/2023","D") returns 14


DATEIF(start_date, end_date, unit)

Number of full units of a given type between two dates, with a fraction representing the part of the unit at the end date.

Example: =DATEDIF("01/01/2023","15/01/2023","D") returns 14


DATEVALUE(date_text)

Date represented by a text string.

Example: =DATEVALUE("15/01/2023") returns the date value of 15/01/2023


DAY(date)

Day of the month for a given date.

Example: =DAY("15/01/2023") returns 15

Excel Days function

DAYS(end_date, start_date)

Number of days between two dates.

Example: =DAYS("15/01/2023","01/01/2023") returns 14



DAYS360(start_date, end_date, method)

Number of days between two dates based on the 30/360 method.

Example: =DAYS360("1/01/2023", "15/01/2023", TRUE) returns 14


EDATE(start_date, months)

Date that is a specified number of months before or after a date.

Example: =EDATE("15/01/2023", 3) returns 15/04/2023


EOMONTH(start_date, months)

Last day of the month, a specified number of months before or after a date.

Example: =EOMONTH("15/01/2023", 1) returns 28/02/2023


EOMONTH(start_date, months)

Last day of the month, a specified number of months before or after a date.

Example: =EOMONTH("15/01/2023", 1) returns 28/02/2023


HOUR(time)

Returns the hour as a number from 0 (12:00 AM) to 23 (11:00 PM) of a time value.

Example: =HOUR("10:30:15 AM") returns 10


ISOWEEKNUM(date)

Returns the ISO week number of a date, as a number from 1 to 53.

Example: =ISOWEEKNUM("15/01/2023") returns 3


MINUTE(time)

The minute as a number from 0 to 59 of a time value.

Example: =MINUTE("10:30:15 AM") returns 30


MONTH(date)

Month of the year for a given date.

Example: =MONTH("15/01/2023") returns 1


MONTHS(end_date, start_date)

Number of months between two dates,

Example: =MONTHS("15/01/2023","01/01/2023") returns 0


NETWORKDAYS(start_date, end_date, holidays)

Number of whole working days between two dates, excluding weekends and holidays.

Example: =NETWORKDAYS("1/1/2023", "15/1/2023", {17/1/2023, 18/1/2023}) returns 11


NOW()

Current date and time.

Example: =NOW() returns the current date and time


SECOND(time)

Returns the second as a number from 0 to 59 of a time value.

Example: =SECOND("10:30:15 AM") returns 15


Excel Time function

TIME(hour, minute, second)

Time based on the provided hour, minute, and second.

Example: =TIME(10, 30, 15) returns 10:30:15 AM




TIMEVALUE(time_text)

Time represented by a text string.

Example: =TIMEVALUE("10:30:15 AM") returns the time value of 10:30:15 AM


TODAY()

The current date.

Example: =TODAY() returns the current date


WEEKNUM(date)

Week number of a date, as a number from 1 to 54.

Example: =WEEKNUM("15/01/2023") returns 3


WEEKNUM(date, [type])

Week number of a date, as a number from 1 to 54.

Example: =WEEKNUM("15/01/2023",2) returns 3


WORKDAY(start_date, days, holidays)

Represents a date that is the indicated number of working days before or after a date, excluding weekends and holidays.

Example: =WORKDAY("15/01/2023", 3, {17/01/2023, 18/01/2023}) returns 20/01/2023


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

A date that is the indicated number of working days before or after a date, based on the weekend and holidays arguments.

Example: =WORKDAY.INTL("15/01/2023", 3, "1111100", {17/17/2023, 18/01/2023}) returns 20/01/2023


YEAR(date)

Year for a given date.

Example: =YEAR("15/01/2023") returns 2023


YEARFRAC(start_date, end_date, basis)

Fraction of the year represented by the number of whole days between two dates.

Example: =YEARFRAC("01/01/2023", "15/01/2023", 1) returns 0.1041


YEARS(end_date, start_date)

Number of years between two dates.

Example: =YEARS("15/01/2023","01/01/2023") returns 0


 

Comdex Training News & Blogs 2023

76 views0 comments

Comments


bottom of page