Excel Training – Page 73 – STL

I use timings in worksheets alongside other key data, for example, in a project sheet, I have lists of individual tasks, with time started, time ended, total time taken, and then cost based on time taken multiplied by charge etc.

So when I want to calculate a mixture of times multiplied by costs, I get an “argh” moment, the figure doesn’t make sense. I want to know why is it doing that, and what can I do to fix it.

To tackle this problem, I created a simple example worksheet…I want to put a costing on the time taken to complete a task. I don’t want to include time taken for breaks for other work or a cuppa, so the project cost reflects only the work completed. So I use a start and finish time, calculating the difference between them for the total time. This result is shown in hours and minutes.

The difficulty is, that I need to multiply time by cost. In Excel, dates and times are shown as you expect them to look but they are stored and treated as numbers. So when I multiply the time taken in cell F2 by the cost in £ in G2, I get 01.41 which isn’t what I want.

excel-times-multiplied-by-cost-formula-not-working — “I want to work out the time taken multiplied by the cost per hour…but why is Excel doing that? I don’t get it”

So how do I get Excel to show 2 hours and 58 minutes multiplied by the cost of £8.66 per hour? I would expect the cost to be close to 3 x £8.66 = £25.98.

I need to convert the time taken into a number that makes sense for Excel. What I need to do is add *24 to the formula so Excel can “understand” what I mean.

My old formula was =F2*G2, but that made no sense to Excel and it gave me a figure that made no sense. So now my revised formula is =F2*G2*24

excel-times-multiplied-by-cost-results — Correct results achieved by amending the formula with a *24

I can then copy this formula down my column H and get the cost per hour for each project task.

Mystery solved.

Auto fill is a feature of Excel that automates the input of certain data such as days of the week, months of the year and quarters. It’s very simple to do and is a great time saver.

If for example you needed to have days of the week on a spreadsheet, you only have to type in Monday and auto fill will do the rest. Here’s how.

Type Monday (or Mon for short) into a cell and press tab/return.
Reselect the cell and move the mouse pointer to the bottom right hand corner. It will change from a white cross to a black cross
Hold down the left mouse key whilst the black cross is visible and move it across (or down) the page. As you do you will see the days of the week appear besides the cell it will eventually fill.
Release the mouse button when you have selected the relevant cells.

By default all seven days of the week will appear. If you want your spread sheet to list only weekdays, follow these extra steps.

Click on the auto fill options box which will appear beside the last cell in your selected range.
Select ‘fill weekdays’ from the menu.

You can auto fill months of the year and quarters using the first 4 steps.

Why not give it try.

Blog Categories