Working with Date and Time in Excel

Get FREE Advanced Excel Exercises with Solutions!

We need to use date and time in Excel for various functions and formatting. Excel date and time functionality involves handling and manipulating dates and times within Excel.

Excel stores dates as serial numbers and times as fractional values of a day. Every date is assigned a unique serial number starting from January 1st, 1900 which is denoted by 1. January 2nd, 1900 holds a numeric value of 2.

In this article, we will discuss 6 ways to insert and perform calculations on date and time in Excel.


1. Inserting Date and Time

We can insert the date and time in Excel worksheets differently, such as manually, using keyboard shortcuts, and using Excel functions.

1.1. Manually Insert Date and Time

We can manually insert the date and time in two different ways. The manually inputted date and time do not update automatically.

To manually insert date and time:

Select cell C5 and manually insert the date and time.

manually insert the date and time

Alternatively, we can also insert the date and time using keyboard shortcuts:

  1. Ctrl + Shift + ;(semi-colon) inserts the current time.using keyboard shortcut to insert time
  2. Ctrl + ;(semi-colon) inserts the current date.using keyboard shortcut to insert date

1.2. Using TODAY Function to Insert Date

The main benefit of using functions to insert date and time is that the time and date update automatically when there are any changes made within the sheet. This is useful for creating dynamic reports and interactive dashboards.

The two functions to insert date and time are the TODAY and NOW functions.

The TODAY function in Excel returns today’s date. On the other hand, the NOW function returns both the current time and today’s date.

Suppose we have a dataset. Conference A starts today. We can use the TODAY function to display the date. For this:

  1. Go to cell C5.
  2. Insert the TODAY function: =TODAY()

select cell and insert TODAY function


2. Inserting Timestamp in Excel

Timestamps in Excel allow us to track the progress of different events and works after certain intervals.

We can insert timestamps manually, using keyboard shortcuts and Excel functions.

The NOW function that we have already used can insert the current time along with the date, which is quite useful for inserting timestamps.

To insert the timestamp, follow the steps below.

  1. Select cell C4.
  2. Insert the NOW function: =NOW()

insert NOW function for timestamp

Note: The date and time update automatically when there are any changes made within the sheet.

Alternatively, we can use the keyboard shortcuts that require manual intervention each time we want to insert a timestamp.

  1. Press Ctrl + Shift + ;(semi-colon) to insert the current time.
  2. Press Ctrl + ;(semi-colon) to insert the current date.

3. Adding Date and Time

Adding date and time is useful for calculating durations for projects and working hours.

3.1. Adding Days with Dates

We can add days to dates in different ways. The simplest way to add days to a date is by using a cell reference.

To add 5 days to the date in cell B5, we can use the formula: =C5+5

add days to date using cell reference

The DATE function can be used to add a specific number of days to a certain date. The DATE function can take an input and convert it to a date.

For this, use the following formula: =DATE(2024,2,17)+5

add days to date using DATE functionThis function adds 5 days to the date of 2/17/2024 and returns the result.

We can also use the TODAY function to add days to today’s date.

The following formula adds 5 days to today’s date: =TODAY()+5

add days to date using TODAY function

3.2. Adding Times

The TIME function can be used in Excel to add a certain time to a time specified in the TIME function.

To add a specific time to the time in cell B5, use the following formula: =C5+TIME(8,5,0)
This adds 8 hours and 5 minutes to the time in cell C5.

adding times


4. Subtracting Date and Time

Subtracting date and time is useful for calculating durations, managing deadlines, etc.

4.1. Subtracting Days from Dates

Subtracting days from dates is similar to adding days to dates. This returns a date as the result.

To subtract 5 days from the date in cell D5, we need to use the formula: =D5-5

subtract days from date using cell reference

We can use the DATE function to subtract days from a date.

The following formula subtracts 5 days from the date of 2/17/2024: =DATE(2024,2,17)-5

subtract days from date using DATE function

To subtract 5 days from today’s date: =TODAY()-5

subtract days from date using TODAY function

4.2. Subtracting Date from Another Date

Subtracting a date from another date returns the duration.

The following formula returns the result of the subtraction between the two dates: =DATE(2024,2,12)-DATE(2024,2,7)

subtract date from another date using DATE function

A similar result can be achieved using cell reference: =C5-B5

subtract date from date

4.3. Subtracting Time

The TIME function allows us to easily subtract time.

To subtract time from the time in cell D5, use the following formula: =D5-TIME(8,5,0)
This subtracts 8 hours and 5 minutes from the time in cell D5.

subtracting time


5. Changing the Date and Time Format

Changing the date and time format is essential for displaying the date and time in a more visually appealing way.

To change the date or time format:

  1. Select a cell or a range of cells and press Ctrl + 1.
    This brings out the Format Cells dialog box.
  2. Go to the Number tab on the Format Cells dialog box.
  3. Select Date or Time under Category.
  4. Select any format from the Type field, and click OK.

change date and time format


6. Converting Time Zones in Excel

The Earth is divided into many different time zones. It defines if a place is ahead or behind another place in terms of time. In Excel, there is no direct formula to convert time zones. For this, we can use the MOD function to convert time zones.

For this, we have a dataset with times of different areas and adjustments. The adjustment denotes how much time has to be added or subtracted from the GMT to get the time of that particular area.

Go to a cell and insert the following formula: =MOD(C4+(E4/24),1)
C5 is the time to convert
E5 is the adjustment time

convert time zones


Download Practice Workbook


Conclusion

In this article, we have discussed in detail the 6 ways to insert and perform calculations on date and time in Excel. The ways include: inserting date and time in Excel in two different ways, and learning how to insert timestamps in Excel. We have learned to perform various calculations on dates and times. Another useful learning point was changing time to different time zones.

We hope you’ll find this article useful and use the methods in your dataset.


<< Go Back to Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Maruf Niaz
Maruf Niaz

Md. Maruf Niaz, BSc, Textile Engineering, Bangladesh University of Textiles, has worked with the ExcelDemy project for 11 months. He works as an Excel and VBA Content Developer who provides easy solutions to Excel-related problems and regularly writes fantastic content articles. He has published almost 20 articles in ExcelDemy. As an Excel & VBA Content Developer for ExcelDemy, he offers solutions to complex issues. He demonstrates an enthusiastic mindset and adeptness in managing critical situations with finesse, showcasing... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo