Working with DATE and TIME in Excel

Working with DATE and TIME in Excel

This lesson should help you master the usage of Date and Time in Excel and the various functions and formats around the same.

10 Functions covered in detail in this lesson:
TODAY, NOW, DATE, TIME, YEAR, MONTH, DAY, HOUR, MINUTE, SECOND

Download DATE and TIME - Practice File

 

To enter a date in Excel, use the “/” or “-” characters. I am using front slash to enter todays date.

To enter a time, you can use the “:” (colon).

You can also enter date and time in a single cell.

Date and Time Manual Entry in Excel

Your windows regional settings determine the default date and time format. Various Date and Time formats are covered in this lesson.

Current Date & Time

You may need to quickly enter the current date and time while making an activity log. Or maybe you want that the current date and time in a cell automatically refresh every time the sheet is updated or formulas are recalculated. There are several ways to insert the current date and time in a cell.

In the spreadsheet, select the cell in which you want to insert the current date or time.

Now, to insert the current date, press Ctrl+; (semi-colon).

To insert the current time, press Ctrl+Shift+; (semi-colon).

To insert current date and time in a single cell press Ctrl+; (semi-colon) give a space and then press Ctrl+Shift+; (semi-colon).

Current Date in Excel  Current Time in Excel

These values won't change when the worksheet is recalculated or opened next time and hence its called 'static' value.

Now, a date or time that updates when the worksheet is recalculated or the workbook is opened is considered 'dynamic' instead of static. The most common way to return a dynamic date or time in a cell is by using worksheet functions.

Static Date and Time in Excel

 

To get the current date use the 'TODAY' function:

On cell D3, type =TODAY()

 

 

To get the current 'date' and 'time', use the 'NOW' function.

On cell D5, type =NOW()