Author avatar

Chhaya Wagmi

Working with Time and Date Functions in Excel - Part 1

Chhaya Wagmi

  • Sep 15, 2020
  • 11 Min read
  • 1,319 Views
  • Sep 15, 2020
  • 11 Min read
  • 1,319 Views
Business Professional
Microsoft Excel

Introduction

In this guide, you will learn how to work with time and date functions in Microsoft Excel 2019. There are various time and date functions which are present in the Excel 2019 version, as mentioned below:

  • DATE
  • DATEVALUE
  • DAY
  • DAYS
  • DAYS360
  • EDATE
  • EOMONTH
  • HOUR
  • ISOWEEKNUM
  • MINUTE
  • MONTH

The DATE Function

The DATE function gives "the sequential serial number that represents a particular date. Use it when you have to take three different values and merge them to form a date," according to Excel's documentation.

The DATE function has the following syntax:

1
=DATE(YEAR, MONTH, DAY)

To understand this function, consider the example given below.

ABCDE
SR. NO.YEARMONTHDAYRESULT
120190819?
22019115?
32019105?

Put the formula *=DATE(B1, C1, D1)in cellE1` and then apply it in subsequent rows. The result will be updated as shown below:

ABCDE
SR. NO.YEARMONTHDAYRESULT
12019081919-08-2019
2201911505-11-2019
3201910505-10-2019

The DATEVALUE Function

The DATEVALUE function is used "to convert a date that is available as text to a serial number that will be recognized as a date by Excel," according to Excel's documentation.

The DATEVALUE function has the following syntax:

1
=DATEVALUE(date_in_the_text_format)

To implement the DATEVALUE function , consider the example given below:

ABC
SR. NO.DATE_TEXTRESULT
1"05-11-2019"=DATEVALUE("05-11-2019")
2"18-08-2019"=DATEVALUE("18-08-2019")
3"19-08-2019"=DATEVALUE("19-08-2019")

The result will be updated as shown below:

ABC
SR. NO.DATE_TEXTRESULT
1"05-11-2019"43774
2"18-08-2019"43695
3"19-08-2019"43696

To avoid any possible error, right click on the cell containing DATE_TEXT and select Format Cells... -> text under Number tab -> OK.

The DAY Function

The DAY function returns "the day of the given date or serial number which ranges from 1-31," according to Excel's documentation.

The DAY function has the following syntax:

1
=DAY(date_serial_number_recognized_by_Excel)

To implement the DAY function, consider the example given in DATEVALUE function section and find the day of the serial number given as output in that example.

ABCD
SR. NO.DATE_TEXTRESULTDAY
1"05-11-2019"43774?
2"18-08-2019"43695?
3"19-08-2019"43696?

Put the formula =DAY(C1) in cell D1 and then apply it in subsequent rows. The result will be updated as shown below:

ABCD
SR. NO.DATE_TEXTRESULTDAY
1"05-11-2019"437745
2"18-08-2019"4369518
3"19-08-2019"4369619

The DAYS Function

The DAYS function is used "to calculate the days between two given dates," according to Excel's documentation.

The DAYS function has the following syntax:

1
=DAYS(new_date, old_date)

To implement the DAYS function in Excel, consider the example given below:

ABCD
SR. NO.END_DATESTART_DATEDAY
119-08-201918-08-2019?
205-11-201905-11-2018?
318-07-201918-06-2019?

Put the formula =DAYS(B1,C1) in cell D1 and then apply it in subsequent rows. It will calculate the days between the END_DATE and START_DATE and the result in DAY will be updated as shown below:

ABCD
SR. NO.END_DATESTART_DATEDAY
119-08-201918-08-20191
205-11-201905-11-2018365
318-07-201918-06-201930

The DAYS360 Function

The DAYS360 function is used "to calculate the number of days between two given dates which is based on a 360-day year (twelve 30-day months)," according to Excel's documentation.

The DAYS360 function has the following syntax:

1
=DAYS360(old_date,new_date,[expression])

To implement the DAYS360 function in Excel, consider the example given below:

ABCD
SR. NO.END_DATESTART_DATEDAY
119-08-201918-08-2019?
205-11-201905-11-2018?
318-07-201918-06-2019?

Put the formula =DAYS(C1,B1) in cell D1 and then apply it in subsequent rows. The third argument is optional; it is a logical value depending on the user’s choice to use the U.S. or European method for the calculation. It will calculate the days between the END_DATE and START_DATE. The updated result in the DAY column is given below:

ABCD
SR. NO.END_DATESTART_DATEDAY
119-08-201918-08-20191
205-11-201905-11-2018360
318-07-201918-06-201930

The EDATE Function

The EDATE function gives "the serial number/ date of the date before or after a specified date (the start_date)," according to Excel's documentation.

The EDATE function has the following syntax:

1
=EDATE(actual_date, time_span_in_months)

To implement the EDATE function in Excel, consider the example given below:

ABCD
SR. NO.DATEMONTHNEW_DATE
119-08-20191?
205-11-2019-1?
318-07-20192?

Put the formula =EDATE(B1,C1) in cell D1 and then apply it in subsequent rows. This function will return the date after adding the MONTH value to the date from the DATE column. The final result is shown below:

ABCD
SR. NO.DATEMONTHNEW_DATE
119-08-2019119-09-2019
205-11-2019-105-10-2019
318-07-2019218-09-2019

If the output is a serial number, right-click that cell value -> click on Format Cells... -> choose the format of the date you want.

The EOMONTH Function

The EOMONTH function gives "the last day of the month of the indicated number of months before or after the start_date as the serial number," according to Excel's documentation.

The EOMONTH function has the following syntax:

1
=EOMONTH(start_date, months)

To implement the EOMONTH function in Excel, consider the example given below.

ABCD
SR. NO.DATEMONTHNEW_DATE
119-08-20191?
205-11-2019-1?
318-07-20192?

Put the formula =EOMONTH(B1,C1) in cell D1 and then apply it in subsequent rows. The resulting value will be updated as below:

ABCD
SR. NO.DATEMONTHNEW_DATE
119-08-2019130-09-2019
205-11-2019-131-10-2019
318-07-2019230-09-2019

If the output is a serial number, right-click that cell value -> click on Format Cells... -> choose the format of date you want.

The HOUR Function

The HOUR function gives "the hour of a time value which ranges from 0 (12:00 A.M.) to 23 (11:00 P.M.)," according to Excel's documentation.

The HOUR function has the following syntax:

1
=HOUR(date)

To implement the HOUR function in Excel, consider the example given below:

ABC
SR. NO.VALUEHOUR
119-08-2019?
205-11-2019 7:45?
318-07-2019 1:15?

Put the formula =HOUR(B1) in cell C1 and then apply it in subsequent rows. The resulting value will be updated as below:

ABC
SR. NO.VALUEHOUR
119-08-20190
205-11-2019 7:457
318-07-2019 1:151

The ISOWEEKNUM Function

The ISOWEEKNUM function gives "the ISO (International Organization for Standards) week number of the year for a given date," according to Excel's documentation.

The ISOWEEKNUM function has the following syntax:

1
=ISOWEEKNUM(date)

To implement the ISOWEEKNUM function in Excel, consider the example given below.

ABC
SR. NO.DATEWEEK NUMBER
119-01-2019?
205-11-2019?
318-07-2019?

Put the formula =ISOWEEKNUM(B1) in cell C1 and then apply it in subsequent rows. The resulting value will be updated as below:

ABC
SR. NO.DATEWEEK NUMBER
119-01-20193
205-11-201945
318-07-201929

The MINUTE and MONTH Function

The MINUTE and MONTH function converts a time format into its corresponding minute and month time frame, respectively.

The MINUTE function has the following syntax:

1
=MINUTE(time_format)

The MONTH function has the following syntax:

1
=MONTH(time_format)

To implement the MINUTE and MONTH function in Excel, consider the example given below.

ABCDE
SR. NO.DATETIMEMINUTEMONTH
119-08-201919-01-2019 7:45:00??
205-11-201919-06-2019 9:15:00??
318-07-201918-07-2019 3:05:00??

Put the formula =MINUTE(C1) in cell D1 and =MONTH(B1) in cell E1 and then apply it in subsequent rows.

ABCDE
SR. NO.DATETIMEMINUTEMONTH
119-08-201919-01-2019 7:45:00458
205-11-201919-06-2019 9:15:001511
318-07-201918-07-2019 3:05:0057

Conclusion

In this guide, you've learned the first set of date and time operations in Excel like DATE, DATEVALUE, DAY, DAYS, etc. In the second part of this guide series, you'll learn about the next set of date and time operations.

1