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.
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
I can then copy this formula down my column H and get the cost per hour for each project task.
Mystery solved.
