Free computer Tutorials

HOME Stay at Home and Learn

How to use the Time Function in Excel

 

Getting the time into a spreadsheet cell is fairly straightforward. First, use the Now() Function. Then format the cell to the Time format you want. We'll go through it now. To enter the Time function, do the following:

  • Click on any empty cell
  • Click inside the formula bar
  • Type in this formula:

=Now( )

  • Press the Return key on your keyboard

When you press the Return key, Excel will give the current date and time. To get only the time, do this:

  • Click inside the cell where your Now( ) function is.
  • From the Excel menu bar, click on Format
  • From the drop down menu, click on Cells
  • The Format Cells dialogue box appears. Click the Number tab strip
  • Under Category, click on Time
  • Under Type, select the Time type you want.
  • Click OK when you have finished

The thing to bear in mind about the Time function is that Excel doesn't update it every second. So you can't use it like a normal clock. But Excel will update the Time when you enter a calculation. As an example, try this:

  • Note the cell where your Time function is, and what time it reads
  • Click inside an empty cell
  • Enter any calculation (= 2 + 2 will do)
  • Press the Return key on your keyboard
  • Look at your Time function cell again. Excel will have updated the Time

You don't have to use the Now( ) function to get the Time. We'll see how to get the Time another way, and we'll construct a timetable.

 

A Timetable

Start on a new spreadsheet, and create the same labels as the ones in the image below:

Create this in Excel

What we're going to do is enter a start time of 9 in the morning. That's when we start washing the pots. We'll estimate how long it takes to wash the pots in the C column, under "Time it Takes". We'll add the "Time it Takes" to the "Start Time" to get a new start time for the Hoovering . But you'll see how it works as we go along.

The first task is to format the Start Time column. So:

  • Highlight the Start Time column, from B3 to B8.
  • From the Excel menu bar, click on Format
  • From the drop down list, click on Cells
  • The Format Cells dialogue box appears
  • Click the Number tab strip, and select Time
  • Under Type, select 13.30. Then ckick OK

The next thing to do is enter a time for cell B3, the Start time. Now that you have formatted the B column as a Time, you can go ahead and just enter a time. Do this:

  • Click on cell B3
  • Click inside the formula bar
  • Type in 09:00 (the colon in between the numbers is important)
  • Press the return key on your keyboard
  • Excel will now treat cell B3 as a time - 9 AM

We'll assume that we're very fast at washing pots (it's all that practice!). So we'll spend just 15 minutes on this job.

  • Click inside cell C3
  • Click inside the formula bar
  • Type in 0:15
  • Press the return key on your keyboard

Excel will probably enter 00:15 for you. (The first two zeros are for the hour.) Your spreadsheet will now look like this:

Your spreadsheet should look like this

We now need to add 15 minutes to the starting time of 9 AM. So do the following::

  • Click inside cell B4
  • Then click inside the formula bar
  • Enter this formula:

= B3 + C3

  • Press the return key on your keyboard
  • Excel will enter 09:15 in cell B4

OK, we have a start time for the hovering - 9:15 in the morning. We can get Excel to Auto Fill the rest of the formulas, because they'll be same. So:

  • Click inside cell B4
  • Move your cursor to the bottom right of the cell
  • When your cursor changes to the shape of a black cross, hold down your left mouse button.
  • Keep it held down and drag the mouse pointer to cell B9
  • Excel will enter 09:15 for all the cells

The only thing left for us to do now is enter some times for the "Time Taken" column.

  • Click on cell C4
  • Click inside the formula bar and type in 01:00 (meaning one hour for the hovering)
  • Press the return key on your keyboard
  • Excel will change all the cells from Rest downwards to 10:15
  • Your spreadsheet will now look like this one:

Your spreadsheet now

OK, you can enter all the other figures yourself. Enter these figures in the remaining "Time it Takes" column:

Rest: 30 minutes
Dust:
30 minutes
Windows:
One hour
Rest
30 minutes

When you are finished, you should have a timetable like the one below:

The finished spreadsheet

 

And that's how to construct a simple timetable. Working with times can be quite tricky, and a good knowledge of Excel functions will stand you in good stead. But we'll leave the Time function, and move on to something else

 

We'll now move on to some financial formulas.

Explore Financial Functions in Excel -->

<--Back to the Excel Contents Page

 

 
Computer Tutorials List