{"id":1800,"date":"2012-10-04T08:27:09","date_gmt":"2012-10-04T08:27:09","guid":{"rendered":"http:\/\/www.microsofttraining.net\/b\/exceltraining\/?p=699"},"modified":"2023-12-30T23:12:17","modified_gmt":"2023-12-30T23:12:17","slug":"time-and-money-in-excel-formulas-aka-why-is-it-doing-that","status":"publish","type":"post","link":"https:\/\/www.stl-training.co.uk\/b\/time-and-money-in-excel-formulas-aka-why-is-it-doing-that\/","title":{"rendered":"Time and money in Excel formulas- aka “why is it doing that?”"},"content":{"rendered":"

I use timings in worksheets alongside other key data, for example, in a\u00a0project 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.<\/p>\n

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

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. \u00a0This result is shown in hours and minutes.<\/p>\n

The difficulty is, that I need to multiply time by cost. \u00a0In 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 \u00a3 in G2, I get 01.41 which isn’t what I want.<\/p>\n

\"excel-times-multiplied-by-cost-formula-not-working\"<\/a>
“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”<\/figcaption><\/figure>\n
<\/dl>\n

So how do I get Excel to show 2 hours and 58 minutes multiplied by the cost of \u00a38.66 per hour? I would expect the cost to be close to 3 x \u00a38.66 = \u00a325.98.<\/p>\n

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

My old formula was =F2*G2, but that made no sense to Excel and it gave me a figure that made no sense. \u00a0So now my revised \u00a0formula is =F2*G2*24<\/p>\n

\"excel-times-multiplied-by-cost-results\"<\/a>
Correct results achieved by amending the formula with a *24<\/figcaption><\/figure>\n

 <\/p>\n

I can then copy this formula down my column H and get the cost per hour for each project task.<\/p>\n

Mystery solved.<\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

I use timings in worksheets alongside other key data, for example, in a\u00a0project 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 […]<\/p>\n","protected":false},"author":5,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[4],"tags":[44,562],"class_list":["post-1800","post","type-post","status-publish","format-standard","hentry","category-excel-training","tag-advanced-excel-training","tag-using-formulas"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/1800","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/users\/5"}],"replies":[{"embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/comments?post=1800"}],"version-history":[{"count":1,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/1800\/revisions"}],"predecessor-version":[{"id":6724,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/posts\/1800\/revisions\/6724"}],"wp:attachment":[{"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/media?parent=1800"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/categories?post=1800"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.stl-training.co.uk\/b\/wp-json\/wp\/v2\/tags?post=1800"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}