# Working with Time and Date Functions in Excel - Part 1

Sep 15, 2020 • 11 Minute Read

## 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:

` ``=DATE(YEAR, MONTH, DAY)`

To understand this function, consider the example given below.

A | B | C | D | E |
---|---|---|---|---|

SR. NO. | YEAR | MONTH | DAY | RESULT |

1 | 2019 | 08 | 19 | ? |

2 | 2019 | 11 | 5 | ? |

3 | 2019 | 10 | 5 | ? |

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

A | B | C | D | E |
---|---|---|---|---|

SR. NO. | YEAR | MONTH | DAY | RESULT |

1 | 2019 | 08 | 19 | 19-08-2019 |

2 | 2019 | 11 | 5 | 05-11-2019 |

3 | 2019 | 10 | 5 | 05-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:

` ``=DATEVALUE(date_in_the_text_format)`

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

A | B | C |
---|---|---|

SR. NO. | DATE_TEXT | RESULT |

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:

A | B | C |
---|---|---|

SR. NO. | DATE_TEXT | RESULT |

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:

` ``=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.

A | B | C | D |
---|---|---|---|

SR. NO. | DATE_TEXT | RESULT | DAY |

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:

A | B | C | D |
---|---|---|---|

SR. NO. | DATE_TEXT | RESULT | DAY |

1 | "05-11-2019" | 43774 | 5 |

2 | "18-08-2019" | 43695 | 18 |

3 | "19-08-2019" | 43696 | 19 |

## 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:

` ``=DAYS(new_date, old_date)`

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

A | B | C | D |
---|---|---|---|

SR. NO. | END_DATE | START_DATE | DAY |

1 | 19-08-2019 | 18-08-2019 | ? |

2 | 05-11-2019 | 05-11-2018 | ? |

3 | 18-07-2019 | 18-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:

A | B | C | D |
---|---|---|---|

SR. NO. | END_DATE | START_DATE | DAY |

1 | 19-08-2019 | 18-08-2019 | 1 |

2 | 05-11-2019 | 05-11-2018 | 365 |

3 | 18-07-2019 | 18-06-2019 | 30 |

## 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:

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

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

A | B | C | D |
---|---|---|---|

SR. NO. | END_DATE | START_DATE | DAY |

1 | 19-08-2019 | 18-08-2019 | ? |

2 | 05-11-2019 | 05-11-2018 | ? |

3 | 18-07-2019 | 18-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:

A | B | C | D |
---|---|---|---|

SR. NO. | END_DATE | START_DATE | DAY |

1 | 19-08-2019 | 18-08-2019 | 1 |

2 | 05-11-2019 | 05-11-2018 | 360 |

3 | 18-07-2019 | 18-06-2019 | 30 |

## 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:

` ``=EDATE(actual_date, time_span_in_months)`

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

A | B | C | D |
---|---|---|---|

SR. NO. | DATE | MONTH | NEW_DATE |

1 | 19-08-2019 | 1 | ? |

2 | 05-11-2019 | -1 | ? |

3 | 18-07-2019 | 2 | ? |

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:

A | B | C | D |
---|---|---|---|

SR. NO. | DATE | MONTH | NEW_DATE |

1 | 19-08-2019 | 1 | 19-09-2019 |

2 | 05-11-2019 | -1 | 05-10-2019 |

3 | 18-07-2019 | 2 | 18-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:

` ``=EOMONTH(start_date, months)`

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

A | B | C | D |
---|---|---|---|

SR. NO. | DATE | MONTH | NEW_DATE |

1 | 19-08-2019 | 1 | ? |

2 | 05-11-2019 | -1 | ? |

3 | 18-07-2019 | 2 | ? |

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

A | B | C | D |
---|---|---|---|

SR. NO. | DATE | MONTH | NEW_DATE |

1 | 19-08-2019 | 1 | 30-09-2019 |

2 | 05-11-2019 | -1 | 31-10-2019 |

3 | 18-07-2019 | 2 | 30-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:

` ``=HOUR(date)`

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

A | B | C |
---|---|---|

SR. NO. | VALUE | HOUR |

1 | 19-08-2019 | ? |

2 | 05-11-2019 7:45 | ? |

3 | 18-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:

A | B | C |
---|---|---|

SR. NO. | VALUE | HOUR |

1 | 19-08-2019 | 0 |

2 | 05-11-2019 7:45 | 7 |

3 | 18-07-2019 1:15 | 1 |

## 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:

` ``=ISOWEEKNUM(date)`

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

A | B | C |
---|---|---|

SR. NO. | DATE | WEEK NUMBER |

1 | 19-01-2019 | ? |

2 | 05-11-2019 | ? |

3 | 18-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:

A | B | C |
---|---|---|

SR. NO. | DATE | WEEK NUMBER |

1 | 19-01-2019 | 3 |

2 | 05-11-2019 | 45 |

3 | 18-07-2019 | 29 |

### 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:

` ``=MINUTE(time_format)`

The **MONTH** function has the following syntax:

` ``=MONTH(time_format)`

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

A | B | C | D | E |
---|---|---|---|---|

SR. NO. | DATE | TIME | MINUTE | MONTH |

1 | 19-08-2019 | 19-01-2019 7:45:00 | ? | ? |

2 | 05-11-2019 | 19-06-2019 9:15:00 | ? | ? |

3 | 18-07-2019 | 18-07-2019 3:05:00 | ? | ? |

Put the formula ** =MINUTE(C1)** in cell

**D1**and

**in cell**

**=MONTH(B1)****E1**and then apply it in subsequent rows.

A | B | C | D | E |
---|---|---|---|---|

SR. NO. | DATE | TIME | MINUTE | MONTH |

1 | 19-08-2019 | 19-01-2019 7:45:00 | 45 | 8 |

2 | 05-11-2019 | 19-06-2019 9:15:00 | 15 | 11 |

3 | 18-07-2019 | 18-07-2019 3:05:00 | 5 | 7 |

## 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.