Tips for Microsoft Excel Date and Time

Adding the date and time to Microsoft Excel spreadsheets is commonly needed however when doing it for the first time it can be daunting. We have put together 4 Tips to so you can do this easily and efficiently.

Tip #1 – Automatically Calculate Today’s Date

Ever wondered how to quickly show the current date? Getting today’s date is very simple and you can get Excel to automatically show the current date by doing the following:

1.     In the cell you would like the date, enter the function: =today() as shown below

Excel Tip Date formula

2.     Hit enter in any cell for the date to appear

Excel Formulas Date 2

Tip #2 – Entering Time into Excel

To get the times and duration calculated in Excel, follow the below steps.

1.    Add your list of times in your cells, for example:

Excel Formulas Time

NB: 1:30 is equal to 1 hour and 30 minutes.

2.    Select the AutoSum icon from the Home tab, which will select all the times you would like to calculate the duration for (A1:A3)

(AutoSum icon)

3.  Hit enter and the duration will be calculated.

Tip #3 – Formatting Time

Now you know how to calculate the duration of time, it’s time to learn how to format the time. This can be done in 6 steps as shown below.

1.    Referring back to tip #2, select the 7:30 in cell A4

2.    In the ribbon, go to the number section and select the drop down next to where it says General as shown below

Excel Formulas Formatting

3.   Select More Number Formats from the drop-down box

Excel Formulas Formatting 2

4.   The below pop up window will appear and select Custom

Excel Formulas Formatting 3

5.   Type the code [h] “hours” mm “minutes” in the type section as shown below;

Excel Formulas Formatting 4

6.  Click OK and the data will now be displayed in hours and minutes

Excel Formulas Formatting 5

Tip #4 – Calculating Time

You can work out the time spent on tasks or hours that someone has worked by doing a time calculation.

1.  Enter a start time in a cell and a finish time in another cell. For example:

2.  In the next cell do a formula starting with = and click into the cell with the finish time, then hit the minus key and then click into the cell with the start time in it. Once you have done this hit Enter

3.  Excel will return the value as 9:00 am

4.  To show the value as hours and minutes, apply the custom format from tip #3 and the total will display as the below:

Icon of a light bulb

Enhance your computer training skills with courses at Odyssey Training. 

Odyssey Training is dedicated to equipping the nation’s workforce with the skills to enhance their competitiveness in the workplace. Discover our range of Microsoft Excel training courses from beginner to expert levels.