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 differently within Excel worksheets 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 insert the date and time manually,
- Select cell C5 and manually insert the date and time.
Alternatively, we can also insert the date and time using keyboard shortcuts:
- Ctrl + Shift + ;(semi-colon) inserts the current time.
- Ctrl + ;(semi-colon) inserts the current 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 dashboards.
The two functions to insert date and time are the TODAY function.
The TODAY function inserts the current date.
Suppose we have a dataset. Conference A starts today. We can use the TODAY function to display the date. For this:
- Go to cell C5.
- Insert TODAYÂ function:
=TODAY()
2. Inserting Timestamp in Excel
Inserting a timestamp in Excel allows us to track and manipulate data based on specific time intervals, such as minutes, hours, days, etc.
We can insert timestamps manually, using keyboard shortcuts and using Excel functions.
The NOW function that we have used already, can insert the current time along with the date which is quite useful for inserting timestamps.
To insert the timestamp follow the steps below.
- Select cell C4.
- Insert the NOWÂ function:
=NOW()
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.
- Press Ctrl + Shift + ;(semi-colon) to insert the current time.
- 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 date is by using cell reference.
- To add 5 days to the date in cell B5, we can use the formula:
=C5+5
We can use the DATE function to add days to a date. The DATEÂ function can take an input and convert it to date.
- For this, use the following formula:
=DATE(2024,2,17)+5
- This function adds 5 days to the date 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
- The following formula adds 5 days to today’s date:
3.2. Adding Times
We can use the TIME function to add time to a specific time.
- To add 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.
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 to date 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
- We can use the DATE function to subtract days from a date.
- The following formula subtracts 5 days from the date 2/17/2024:
=DATE(2024,2,17)-5
- To subtract 5 days from today’s date:
=TODAY()-5
4.2. Subtracting Date from Another Date
Subtracting a date from another date returns the duration.
- The following formula returns the result of subtraction between the two dates:
=DATE(2024,2,12)-DATE(2024,2,7)
- A similar result can be achieved using cell reference:
=C5-B5
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.
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:
- Select a cell or a range of cells and press Ctrl + 1.
- This brings out the Format Cells dialog box.
- Go to the Number tab on the Format Cells dialog box.
- Select Date or Time under Category.
- Select a type, and click on OK.
6. Converting Time Zones in Excel
Time zones are most important to know the actual time of different places to another location. 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
Frequently Asked Questions
What is the Excel formula for date and time?
The TODAY function shows the current date and the NOW function shows the current time along with the date. These are the default formulas in Excel for date and time.
How do I insert the date and time together in Excel?
The NOW function inserts the current date and time together in Excel.
Conclusion
This article describes everything about date and time in Excel. We discussed how to insert the date and time, and how we can change the formatting of the date and time. We have also discussed the method of inserting timestamps and the way to convert time zones.
Excel Date Time: Knowledge Hub
<< Go Back to Learn Excel
